Export Configuration

The Export Configuration dialog allows you to configure KeyServer to export its internal data to an external database, such as Oracle, MySQL, SQL Server, etc.

What exporting is NOT:

There are various reasons you might want to use exporting:

Both external and internal reports in KeyConfigure can be used to access either exported or internal data. External reports (Crystal, MSAccess, FileMaker, etc.) always use ODBC with a correctly configured DSN to specify the target database. Internal reports target the KeyServer's internal database by default unless the Options button in the Configure Reports dialog is used to specify a DSN targeting an exported database.

If there are firewall rules controlling traffic between the KeyServer host computer and the external database server, you may need to set up appropriated firewall rules. Consult the database server documentation.

Depending on the platform hosting the KeyServer process, specific export modules are available:

Three additional export modules listed below are included for use in transforming KeyServer data to XML, comma delimited, or tab delimited text. These methods do not use the network to pass data directly to a remote database server and cannot be performed incrementally (e.g. complete database files are exported every time). They can be used for static export/import of K2 data into another database or analysis tool. Usually, exporting to a database of some sort is a better choice than exporting to any of the text formats.

Note: every time one of the static export methods (XML, CSV, or Text) is run, the complete database files are exported into the "Export" folder within the KeyServer Data Folder replacing any previously exported file of the same name. For these three static methods, scheduled exports are allowed at most once per day. Also, since a complete export of large databases can be slow, you might want to disable export of Audits and Usage when using a text based method.

Configuration Details

OPEN Export Configuration dialog

Disabled Module

By default, the export module is Disabled, meaning that the internal databases will not be exported.

Export Audits and Export Usage

All tables except for Audits and Usage are always exported. These two check boxes allow you to configure whether the Audits and Usage tables are exported or not, since these two tables are by far the largest. By default, they are both exported, so that the external database will contain all the data that the internal database does.

ODBC Module

Configuring this module allows KeyServer to use ODBC to export the internal databases to an SQL database through a DSN (defined with system access permissions on the computer which is running KeyServer). Before configuring the ODBC module, you must first set up the ODBC Data Source that KeyServer will use. Instructions on setting up an ODBC Data Source should be included with your database software, as the details are specific to each database system.

Note that if you are using a 64-bit version of KeyServer, you will need to use a 64-bit ODBC driver. Likewise, 32-bit KeyServer requires a 32-bit ODBC driver. This is one of the few reasons to prefer one bitness over the other when given a choice on a 64-bit OS. In addition, you must use the correct ODBC Administrator which matches the bitness of the KeyServer executable. Specifically, if you are using 32-bit KeyServer on a 64-bit OS, you will need to manually locate and launch the 32-bit ODBC Administrator, which is at C:\Windows\SysWow64\odbcad32.exe, and use that version of ODBC Administrator when defining the DSN which will be used in the KeyServer export configuration.

You will also need to create a database into which KeyServer will insert data. You can use an existing database if you choose. There must also be an account and password that is granted read and write (SELECT, INSERT, UPDATE, CREATE TABLE) access to the database.

Once you have set up an ODBC Data Source (DSN) and the corresponding database (and access account), you can configure KeyServer to export its data to this Data Source. Choose the Exporting command from the Config Menu in KeyConfigure, and then select ODBC from the pop-up menu. In the Configuration section of the dialog box, enter the name of the Data Source you just set up, along with the account name and password (don't leave the password field blank).

There are various options that you can set to control the SQL commands used for creating new tables, records, and fields.

Use Microsoft-compatible CREATE TABLE
KeyServer will create all tables if they are not already present in your database. This option changes what type of syntax is used in the CREATE queries. If you are using MS Access or SQL Server to host your database, you should check this option. Other database programs might also support or even require this method of table creation. See KeyServer Tables Description for more information on what tables will be created. If you don't know whether to use this option, try using it first, before trying not using it.
Use SQLBindParameter optimization
Some databases support “Parameter binding,” which is an efficient way of executing multiple SQL queries that use the same basic form. While exporting, the ODBC module issues many commands that look virtually identical except for the actual data. Checking this option may allow your database driver to parse the actual command a single time, greatly speeding up the time required to export. If your database supports Parameter Binding, check this option.
Use SQLBindParameter with multiple rows
When doing “Parameter binding,” you can give data for more than query in a single call to the ODBC driver. If your database/driver support multi row binding, you should consider using this option. It could make the export more efficient overall.
Record Greenwich Mean Time
By default, KeyServer writes the “local” time of each event. If you have different KeyServers in different time zones, or if you want your data to be independent of time zone, check this option. The KSServers table contains the GMT offset of each server listed, which aids in computing the local time from GMT, or GMT from local time. If you do check this option, you should check it on all servers that stream data to the same database.
Escape Backslashes in text fields
This option should almost never be turned on. Some old versions of postgresql treated a backslash within a string as an escape symbol, which causes problems if a single backslash is followed by three numerical characters. These versions of postgresql require this option to be turned on. If you discover that another ODBC driver requires this option, please let us know.
 

The basic queries for adding new rows and modifying existing rows can be done in one of four ways:

Use INSERT/UPDATE semantics
This is the most inefficient way of forming queries. Since KeyServer does not know if each row will exist in exported data, it will first try to INSERT a row. If this fails because the row already exists, KeyServer will then UPDATE the row.
Use REPLACE semantics
Some databases (such as MySQL) support a “REPLACE” SQL command, which is an efficient way to insert an item into a table that might already contain that item. If your database supports REPLACE, select this option. For most databases, this option should NOT be selected.
Use MERGE semantics
Some databases (such as MS SQL) support a “MERGE” SQL command, which is an efficient way to insert an item into a table that might already contain that item. If your database supports MERGE, select this option.
Use PL/pgSQL stored procedure
postgresql does not support REPLACE or MERGE, but it does allow a stored procedure to be created which will conditionally INSERT or UPDATE a row. If you are using postgresql this is likely more efficient than using INSERT/UPDATE
 

There are three options for text encoding - the correct choice might depend on what type of RDBMS you are using but also how the specific database is configured:

Use ANSI Encoding for text fields
This is the default text encoding. With this encoding, you will not be able to see high ascii or non-ascii characters in your exported data. Such characters would most likely appear in Program Names, but could also appear in other fields.
Use UTF-8 Encoding for text fields
You can also choose UTF-8 if your target database supports it. This will allow all characters to be represented. Note: some versions of postgresql require UTF-8 text encoding.
Use Unicode Encoding for text fields
Some databases might be configured to use Unicode (UTF-16) encoding, in which case you should use this option.
 

Time fields can be exported in one of four different formats:

Use Standard DATETIME
The "standard" DATETIME data type and time format is accepted by most databases. This is the default.
Use Oracle-compatible DATETIME
Oracle accepts the DATETIME data type but the exact syntax for the time format is Oracle specific, so use this format when exporting to Oracle. Note: Oracle also uses a proprietary join syntax for queries. If you make use of the ODBC option to point internal reports to an exported Oracle database, then be sure to select "Use Oracle Join Syntax" under Options in the Report Selection dialog. See Report Options Dialog for more information.
Use TIMESTAMP
The TIMESTAMP data type and time format is required by some databases (instead of the more common DATETIME datatype).
Use text times
If your database does not support a datatype for date-time data, you can resort to a text export.

In the lower left, in the Schedule area, you can configure how often KeyConfigure will perform an export. Note that the export will be incremental, so there will not be a huge performance penalty if you choose a short interval. For a small KeyServer you might export as often as every hour. For a larger KeyServer you should probably export no more frequently than once a day.

Once these settings have been made, KeyServer begins to export its databases according to the schedule you have configured. You can force an initial export by clicking Export Now. The first thing that KeyServer does during an export is check if the tables are already present in the database. If not, it attempts to create these databases using the appropriate “CREATE TABLE” command. If for some reason the tables cannot be created, you can create these tables yourself (FileMaker Pro for example does not accept this command). See KeyServer Tables Description for the details of what is in each table.

Example configurations

The following list gives examples of how to configure the ODBC export module to work with your particular ODBC driver. If you are using a different database and driver, we would be interested to hear how to successfully configure it.

Microsoft (Access, SQL server, etc.) - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Standard DATETIME
MySQL - Use REPLACE semantics, Use Standard DATETIME
Oracle - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Oracle-compatible DATETIME
PostgreSQL - Use PL/pgSQL stored procedure, Use UTF-8 Encoding for text fields, Use Text Times (Escape Backslashes in text fields only for old versions of postgresql)

Testing the ODBC export

Every computing environment is different, and with KeyServer's ODBC exporting you are using software from several different sources (KeyServer, ODBC, the ODBC drivers, the database, etc.) These factors increase the potential for problems, so you should be sure to verify that your configuration works properly.

One good strategy is to test your configuration at each stage. First set up your database and make sure you can get the proper access to it using the database's native tools. Next, set up the ODBC Data Source and verify that it works correctly using a simple ODBC-based query tool like Crystal Reports, MS Access, MS Query, or any tools that come with your database system. It is important at all steps to verify that you have granted the appropriate permissions to the account that KeyServer will be using to connect to your database.

Configure KeyServer's ODBC export module and click Export Now. Check that the tables were created properly. As a simple confirmation, you could use a query tool to run the SQL command "select count(*) from KSComputers" - this should return a number which matches the computer count at the bottom of KeyConfigure's Computer Window.

If you run into problems after configuring Exporting, check the Status Details to see what error is displayed and whether it contains enough information to understand the problem. If you can't see an error, or if it does not contain enough data, you can also configure Diagnostic logging to include more information about exporting. If you need to call Sassafras technical support for help in setting up ODBC exporting, we will most likely need to look at the diagnostic log.

In order for the linux version of this module to work, you will need to install unixODBC, which can be downloaded from http://www.unixodbc.org/.

MySQL Module

The MySQL module can only be used to export to MySQL databases. It is available to KeyServers running on OS X or linux, and requires libmysqlclient version 18. If you want to export to a MySQL database from a Windows KeyServer, you can download the ODBC driver for MySQL and use the ODBC exporting module.

This module looks very similar to the ODBC module, but it has fewer options, and there are a few slight differences in how it should be configured.

The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:

hostname/database

If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a MySQL server on 192.168.0.4, and the database named "ksdb".

In order for the linux version of this module to work, you will need to install the MySQL "Shared compatibility libraries" that MySQL provides. These can be downloaded from http://mysql.org/downloads/mysql/5.0.html (search for "Shared compatibility libraries" in the various Linux flavors).

PostgreSQL Module

The PostgreSQL module can only be used to export to PostgreSQL databases. It is available to KeyServers running on OS X or linux. If you want to export to a PostgreSQL database from a Windows KeyServer, you can download the ODBC driver for PostgreSQL and use the ODBC exporting module.

This module looks almost identical to the MySQL module.

The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:

hostname/database

If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a PostgreSQL server on 192.168.0.4, and the database named "ksdb".

In order for the linux version of this module to work, you will need to have libpq.so.3 installed, which can be downloaded from postgresql.org.

SQLite Module

The SQLite module can only be used to export to SQLite database files. It is available to KeyServers running on OS X. If you want to export to a SQLite database from a Windows KeyServer, you can download the ODBC driver for SQLite and use the ODBC exporting module.

The configuration for this module is very simple. All that is needed is to specify a file name in the Database field. This export will create this file in the Export Files directory (or Export Modules directory in older versions).

Text module

This module exports all databases in tab separated field format into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.

XML module

This module exports all databases as XML files into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.

CSV module

This module exports all databases in comma separated field format into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.

Special case for Term tables

After successfully configuring exporting, you may notice that all tables have been successfully exported, but all term tables are missing. If this is the case, try the following series of steps:

  1. Disconnect with KeyConfigure
  2. Quit KeyServer on the computer where it is running
  3. Start up KeyServer again
  4. Connect to KeyServer with KeyConfigure
  5. Open the Export Configuration dialog and click Export Now
  6. Wait for the export to complete and see if the term tables have been exported.

The reason that the KeyServer may need to be restarted in order to export the Term tables is that the Term tables, unlike all the other tables, very seldom change. Therefore, KeyServer only attempts to export them the first time it does an export after starting up. So if you have made changes to the export configuration, or manually deleted any tables from the external database server, you may be stuck in a situation where KeyServer won't try to export the Term tables again until you restart.