DataSource, Table & Column Properties

The Destination, Table and Column Properties form lists all current data source, table and column records.

This is accessible via either the toolbar  button or by selecting Maintain ® Datasource, Tables and Columns… from the main menu.

The recommended way to add new data sources, tables and columns is to execute the Import Catalog Wizard. This can be found on the Tools ® Import Catalog Wizard menu and is also available from a button at the top of the Datasource, Table and Column Properties form. Note the wizard can be safely used on an already existing datasource or table record as it will not change any existing column definitions but will simply merge any new columns.

 DataSource Items

DataSourceID – lists the unique identifier for each datasource.

DataSource – lists the name for each datasource.

UserName – if a username is required for SLIK to connect to the database, the user name should be entered into this field.

Password – if a password is required for SLIK to connect to the database, the password should be entered into this field by pressing on the column button.  This displays the following screen.

The password will be masked on entry.

AdministratorID – if an Administrator is assigned to the mapping record, the administrator name should be entered here.

CloseOnCompletion - if enabled, the CloseOnCompletion  check box will cause the SLIK server to drop the database connection between each run.  In general this should only be set for datasources that have a low frequency of runs or that require the connection to be closed before other applications can access the file.

Disabled – enabling a record in this field indicates that the datasource will not be available for data transfer. If an active datasource is disabled then the next time the SLIK Server references the datasource it will be closed and the batch will fail. This provides a logical way to disable a datasource that may be unavailable due to system maintenance or other outage.

MutexLock - if enabled, reflects that a Mutex Lock (Mutually Exclusive Lock) applies to this datasource name.  A MUTEX lock limits the number of operations that can be conducted on the same data source at any one time to a single instance.

QueryTimeout – if specified it indicates the time in seconds that a executing SQL statement or query for the datasource will wait before timing out. A timeout generally only occurs if no response is received from the server. It does not apply to long running queries that indicate they are still being processed by the database.

CommitRecordCount – the CommitRecordCount is an optional integer value that causes the SLIK server to perform a commit database transaction for each ‘N’ records during a transfer. This is primarily useful for some ODBC drivers (eg, Text Files etc) that if you try to perform a large transfer within a single transaction it causes the driver to run out of resources (eg, disk space).

Driver ID – a value in this field reflects the driver associated with the data transfer. It is optional and is currently only used for informational purposes.

Table Items

Table ID – lists the unique identifier for each table.

TableName – lists the name for each table. You can specify more that a single table here by separating a list of tables with commas. In this case you must define how SLIK joins the multiple tables

TableAlias – if applicable, lists the alias for a table.

CommitRecordCount – the CommitRecordCount is an optional integer value that causes the SLIK server to perform a SQL commit operation for each ‘N’ records during a transfer. 

CreateOnDemand - if enabled, SLIK tests for the existence of the destination table prior to performing a transfer. If the table does not exist it constructs a “CREATE TABLE” SQL statement that is sent to the destination database. If the current operation is a TableMap transfer then all the columns from the source table are defined in the destination table.  Otherwise for a Column Map operation all of the designated destination columns are created with the same data type and length as the source columns.  The SLIK server also creates any of the source table indexes on the destination table for TableMap transfers. 

AutoNumber – if enabled, provides a mechanism to place a unique integer key value as a column in the destination table. The SLIK server records the "Last Number Used" and increments it for each row inserted into the destination table. The AutoNumber feature works with the create table on demand option.  If specified then the table has an extra column added as part of the table creation process.

In the event that multiple concurrently executing threads are writing data to the same destination table SLIK ensures the number assigned to the AutoNumber column does not ever overlap.  SLIK achieves this by creating a mutually exclusive lock based on the “DataSource:TableName” pair.  This causes any subsequent thread using this table to wait until the first transfer thread completes before gaining access to the next AutoNumber (i.e, next starting) value.

If the AutoNumberColumnName field is empty then the AutoNumber column is assumed to be the first column in the destination table.  However if the Auto Number Column Name field is specified then this allows you to nominate the column to use.  As SLIK does not interrogate the destination table it is necessary to specify the name of the source column where you want the AutoNumber to be placed after in the destination table.  For example, given the column mapping;

Specifying the AutoNumber column name as SrcIntergerCol2 will cause the next column in the destination table, PrimaryKeyCol3, to be the target of the AutoNumber value.

MutexLock – if enabled, reflects that a Mutex Lock (Mutually Exclusive Lock) applies to this table name.  MUTEX Locks limit the number of operations that can be conducted on the same data source and table at any one time.

EnableSQL92 – if enabled, it indicates that the table supports the more efficient INNER, LEFT and RIGHT JOIN SQL syntax. Otherwise SLIK will use the WHERE Table1.Column1 = Table2.Column1 SQL join syntax. This option only applies to Multi-Table mappings (i.e., two or more table names separated by commas).

LastUserNumber – if the Autonumber feature is enabled then SLIK records the last number used here. This number is incremented for each row and is updated once a transfer completes.

AutoNumberColumn – The AutoNumber checkbox can be enabled to have SLIK generate a unique sequential numbered column value when the table is used as a destination table.  This is a useful feature if the source table does not contain a primary unique key but a unique key is required on the destination database, e.g., OneWorld/DB2-400.

To use the AutoNumber feature on Column Maps you first define a virtual column  called SLIK_AutoNumber. This is accomplished by adding a column definition in the source table with the following details:

In the column mapping you map this virtual column to the desired destination column. The destination column must be a numeric or character data type large enough to hold the result.

Using a large integer value (65000) ensures the ODBC driver treats this as an ODBC INTEGER data type (32 bit) rather than an ODBC SHORT (16 bit <=32756).

Specify the AutoNumber Column Name as SLIK_AutoNumber in the TableName Maintenance form. This causes SLIK to match the virtual column name and increment the value on each row during transfer. It is important to use the special name SLIK_AutoNumber otherwise it will attempt to update the column following the nominated column (as per Table Map).

Column Items

ColumnID - lists the unique identifier for each column.

ColumnName – lists the name for each column

ColumnTypeID – indicates the type of data held in the column. This field is optional. SLIK will query the source datasource for the meta-data once before executing a transfer.

ColumnLength – defines the number of characters allowable in the column. This field is optional, as SLIK will query the source datasource for the meta-data when executing a transfer. It is mainly provided to allow sufficient space to be specified when performing data conversions on the destination column. For example converting a database date to a char field where a database date may be represented as an integer value and the final destination date column wants it to be saved as a character value. By specifying the data type as char(32) then SLIK will perform an implicit data type conversion that is compatible with the destination column.

ColumnAlias - if applicable, lists the alias for a table.

DataControlID – the data control is used to have SLIK perform quoting around column names when executing an SQL statement. Three options are provided.

If no DataControl is specified then SLIK will treat the ColumnName value as a database column name and will quote the column name appropriately to avoid any possible use of SQL keywords or special characters from being incorrectly interpreted by the target database server.