DB2 targets have the following options:

Loading method

INSERT - Insert the rows only. This is the default.

REPLACE - Drops the existing destination table, and creates a new table.

Note: this option is not available for z/OS.

Informix targets have the following options:

Lock tables while load

Table locking prevents other users from changing data in a table that is being loaded. Locking is necessary for maintaining data integrity while concurrent users access database information.

Recognize HEX binary data in char

Recognizes hexadecimal data in character fields.

Check syntax only

Checks the syntax, but not the data being loaded.

First rows to ignore in input file

Numbers of rows to ignore before starting to load data.

Commit every row(s)

The row interval to commit or write transactions to the database.

Maximum number of rows with errors

The maximum number of errors allowed before loading stops. The default is the target RDBMS's default.

Nucleus targets have the following options:

Version of ndl

Single-user

Starts the server in exclusive mode. No other database sessions may be established.

Multi-user

The server must already be started in multi-user mode and listening on the default port 1285.

Overwrite the error log file

yes

Creates a new a file

no

Appends to existing log.ndl file.

Commit loaded rows if error occurs

yes

Issues a commit,

no

Issues a rollback.

First input file bytes to ignore

Number of bytes to skip before loading data.

Null indicator

Specifies the character to represent null.

First input file rows to skip

Number of rows to skip before loading data.

Number of input file rows to process

Number of rows to process before stopping.

Disconnect all other connections

Disconnects connections.

Skip rows w/ unprintable characters

Skips rows with unprintable characters

Server password

NDL server password. Required if Single User (ndls) mode and a server password actually exists.

Oracle targets have the following options:

Use direct path load

A direct path load builds blocks of data in memory and saves these blocks directly into the extents allocated for the table being loaded. A direct path load uses the field specifications to build whole Oracle blocks of data, and write the blocks directly to Oracle data files. Direct path load is much faster than conventional load, but entails some restrictions.

Bind size

Size (in bytes) of the bind array. When the array is full, the array is loaded into the target.

Read size

Size (in bytes) of the read buffer.

Unrecoverable

If yes, the transaction will not generate redo entries.

Loading method

APPEND - If data already exists in the table, new rows are appended to it. If data does not already exist, the new rows are simply loaded. This is the default.

REPLACE - Drops the existing destination table, and creates a new table.

INSERT - Insert the rows only.

Number of logical records to skip

Specifies the number of logical records from the beginning of the file that should not be loaded. By default, no records are skipped.

Number of logical records to load

Specifies the maximum number of logical records to load. By default all records are loaded. No error occurs if fewer than the maximum number of records are found.

Number of errors to allow

Specifies the maximum number of insert errors to allow. If the number of errors exceeds this value, the load is terminated. The default is 50.

Commit every row(s)

The row interval to commit or write transactions to the database.

Version of sqlldr

Version of the SQL*Loader utility.

Red brick targets have the following options:

Start record number in input field

Row number to begin copying data to the target.

End record number in input field

Row number to end copying data to the target.

Locale

The combination of language and location.

Maximum number of discarded records

The maximum number of discarded records allowed before loading stops. The default is the target RDBMS's default.

Discard file name

File where duplicate records are discarded. The records will be stored for possible reloading.

RI discard file name

File where discarded records based on referential integrity will be stored for possible reloading.

Loading mode

INSERT - Insert the rows only. If the table is not empty, the load operation ends. This is the default.

APPEND - Used to insert additional rows of data into an existing table. Each new row must have a primary-key value that does not already exist in the table. Otherwise, the record is discarded.

REPLACE - Replaces the entire contents of a table.

MODIFY - Used to insert additional rows or to update existing rows in a table. If the input row has the same primary-key value as an existing row, the new row replaces the existing row. Otherwise, it is added as a new row.

UPDATE - Updates existing rows in an existing table. Each new row must have a primary-key value that is already present in the table. Otherwise, the record is discarded.

MODIFY AGGREGATE - If the primary key of the input row matches an existing row in the table, the existing row is updated as defined for the specified aggregate operator. If the primary key of the input row does not match an existing row in the table, the row is inserted.

UPDATE AGGREGATE - If the primary key of the input row does not match the primary key of a row already in the table, the input row is discarded. If it does match an existing row, the existing row is updated as defined for the specified aggregate operator.

Optimize

OFF - Indexes are updated when each input row is inserted into the data file, which provides better performance when the data being loaded contains many duplicate rows. This is the default.

ON - Overrides the global optimize mode setting in the rbw.config file.

SQL Server targets have the following options:

Format file version

Version of the file used to store the format information for each field.

Maximum number of errors

The maximum number of errors allowed before loading stops. The default is the target RDBMS's default.

First row to copy

Row number to begin copying data to the target.

Last row to copy

Row number to end copying data to the target.

Packet size to send

The packet size in bytes.

Commit every row(s)

The row interval to commit or write transactions to the database.

Sybase targets have the following options:

Format file version

Version of the file used to store the format information for each field.

Maximum number of nonfatal errors

The maximum number of errors allowed before loading stops. The default is the target RDBMS's default.

First row to copy from input file

Row number to begin copying data to the target.

Last row to copy from input file

Row number to end copying data to the target.

Commit every row(s)

The row interval to commit or write transactions to the database.

Sybase IQ targets have the following options:

Delimiter

Is the delimiter character(s) used between fields. It is used by the intermediate flat file used to load the database.

The delimiter can be up to four characters and can be specified as:

TAB - a tab character. This is the default.

a - A character string, for example ~.

0x nn - a hex code, for example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for Windows or UNIX systems and EBCDIC for IBM Mainframes.

Note: For a Sybase Adaptive Server IQ target, the load will fail if the database returns any errors, such as a unique index constraint violation.

Teradata targets have the following options:

Rejected records maximum number

The maximum number of rejected records allowed before loading stops. The default is the target RDBMS's default.

TDP Id

The Teradata Director Program Id.

Account Id

Account Id used to access the database.

Start record number in source

Row number to begin copying data to the target.

End record number in source

Row number to end copying data to the target.

Work table

Name of the work table.

Acquisition phase errors table

This table provides information about all errors that occur during the acquisition phase of your Update operator job, as well as some errors that occur during the application phase if the Teradata RDBMS cannot build a valid primary index.

Application phase errors table

This table provides information about uniqueness violations, field overflow on columns other than primary index fields, and constraint errors.

Loading Method

INSERT - Inserts the rows only. This is the default.

UPSERT – Does inserts for missing update rows.

Note: This option is available for adapters configured with Call-Level Interface (CLI).

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

This shows the Key Matching Logic Load option.

The Insert records from memory Load option looks like this:

The Bulk load utility via a disk file Load option looks like this for SQL Server. Other relational targets will have different options.

The Slowly Changing Dimensions Load option looks like this:

The Enable Change Data Capture Load option looks like this:

The Main Options tab of Target Properties window for relational targets contains the following target options:

Adapter

Is the target type.

Connection

Is the connection for the data target. For a relational data target, this is a database server. For ODBC, this is a data source.

Synonym Application Directory

Is the directory where the synonym is located.

Synonym

Is the synonym's name.

Table

Is the name of data target table.

Keys (only available for new targets)

Is the number of key columns.

Prior to Load Options: (only available for existing targets)

These options are only available for existing targets.

No changes

Does not delete the records already in a data target.

Delete all rows from table

Deletes all rows and creates a database log.

Truncate table

Deletes all rows from the table but does not generate a database log. This is faster option that Delete all rows from table.

The Main Options tab also includes the following load options:

Load Type:

Note: Although Load Type appears in every data target, it is set on a per flow basis. Changing the type in one target will reset it for all targets.

Key Matching Logic

Specifies DataMigrator's behavior while loading data.

Key Matching Logic options can be set on a per target basis.

If you select this option, you can set a behavior when duplicate records are found from the If the record exists drop-down menu. Key Matching Logic options are:

Include the record allows the relational database target to handle duplicates.

Note: If you select Include the record, the record is passed directly to the relational database, which determines whether to accept it or not. If inserting a record would result in a duplicate key, the RDBMS will reject it due to a Unique Index constraint violation and return an error. Processing continues even if such errors occur, up to the number of errors specified under Stop processing after __ DBMS errors in the flow's General properties.

Reject the record issues a SELECT command against the target table to see if a record exists with the key values. If there are no key columns, DataMigrator screens all columns. If the record is found, the record is rejected.

Update the existing record updates records if the key value on the input record, or entire record if there are no keys, are found in the table. All non-key values are updated in the target.

Delete the existing record deletes the record if the key on the input record (or entire record if no keys) is found in the table.

If you select Update the existing record or Delete the existing record from the If the record exists drop-down menu, you can also set behavior when the record does not exist using the If the record does not exist drop-down menu:

Include the record includes the key in the data target.

Reject the record does not include the key in the data target.

Note: The Reject, Update, and Delete options can adversely affect performance because they determine the existence of a key value on an incoming record before performing the specified action. This is done by issuing a SELECT command against the target table, then waiting for the response. If one of these actions is required, try to limit the volume of records in the incremental change. These actions will perform best if there are unique keys on the table.

Commit every row(s) specifies the row interval to commit or write transactions to the database.

Insert records from memory

When you specify this load type you need to specify:

Commit every row(s) specifies the row interval to commit or write transactions to the database.

Number of records to load in a block specifies how many records you want to process at a time.

Bulk load utility via a disk file

These options will depend on your target type.

Slowly Changing Dimensions

This option is only available for existing relational targets.

Enables you to load a data target with column values that change over time. There are two column types. Type I simply overwrites the value. Type II allows you track activity before and after the change. Type II changes are handled either via an activation flag or end date/begin date. When you update a Type II column, you don't actually change its value. Instead, you update the activation flag to inactive, or the end date to today's date. You then add a new record with the new value and the activation flag on by default or the begin date set.

Commit every row(s) specifies the row interval to commit or write transactions to the database.

Enable Change Data Capture

Enables you to load a data target with only the records that have changed. This feature is an optional, add on component to DataMigrator. When this option is selected, the Prior to Load option is eliminated.

Commit every row(s) specifies the row interval to commit or write transactions to the database.

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

The Main Options tab of the Target Properties window for FOCUS/FDS or XFOCUS targets contains the following fields/options:

No changes

Does not drop (delete) the data target.

Drop Table

Drops and recreates the data target.

Load Type:

Key Matching Logic

Since DataMigrator uses Key Matching Logic to load FOCUS/FDS or XFOCUS targets, you can set a behavior when duplicate records are found from the If the record exists drop-down menu. These options can be set on a per target basis. They are:

Include the record includes the duplicate record in the data target.

Reject the record issues a SELECT command against the target table to see if a record exists with the key values. If there are no key columns, DataMigrator screens all columns. If the record is found, the record is rejected.

Update the existing record updates records if the key value on the input record, or entire record if there are no keys, are found in the table. All non-key values are updated in the target.

Delete the existing record deletes the record if the key on the input record (or entire record if no keys) is found in the table.

If you select Update the existing record or Delete the existing record from the If the record exists drop-down menu, you can also set behavior when the record does not exist using the If the record does not exist drop-down menu:

Include the record includes the key in the data target.

Reject the record does not include the key in the data target.

Commit every row(s) specifies the row interval to commit or write transactions to the database.

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

The Main Options tab of the Target Properties window for Delimited Flat File Targets contains the following fields/options:

Delimiter

Is the delimiter character(s) used between fields. The delimiter can be up to 30 characters. It can be specified as:

TAB - a tab character. This is the default.

a - A character string, for example ~.

0x nn - a hex code, for example, 0x44 (a comma), or 0x0D0A (a return and a linefeed). The hex code uses ASCII for WINDOWS or UNIX systems and EBCDIC for IBM Mainframes.

Header

Inserts column headings as the first row of data and surrounds the column names with the character specified in the Enclosure field.

Enclosure

This character is used to surround the column headings when Header is set to Yes.

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

The Main Options tab of the Target Properties window for Flat File Targets contains the following fields/options:

Adapter

Is the target type.

Synonym Application Directory

Is the directory where the synonym is located.

Synonym

Is the synonym's name.

Data Application Directory

Is the application directory for the data file pointed to by the synonym or <physical location> if the file is not written to an application directory.

Data File

Is the name of the data file pointed to by the synonym.

Code Page

Specifies a code page for the target data.

Prior to Load Options: (only available for existing targets)

No changes

Does not delete the data target.

Delete File

Deletes and recreates the data target.

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

The Main Options tab of the Target Properties window for Formatted File Targets contains the following fields/options:

Setting a data flow's record logging options allows you to write particular types of transactions to log files.

Although Record Logging options appear in every data target, they are set on a per flow basis. Changing the options in one target will reset them for all targets.

The Target Properties window is available by right-clicking a target object and selecting Target Properties.

The following types of transactions can be logged: