Loading data to Microsoft SQL Server

These are the properties that can be set to customize the bulk load operation when the stage is configured for the Microsoft SQL Server database type.

Loading data to Microsoft SQL Server is supported only if InfoSphere® DataStage® engine is installed on a Microsoft Windows machine.

For Linux or UNIX operating systems, you can still select Bulk insert write mode in the Dynamic RDBMS stage configured for Microsoft SQL Server database, but the job with that stage will fail to run successfully and an error message will be reported in the job log stating that the stage supports bulk load operation to Microsoft SQL Server databases only on Windows.

The following is the list of properties that can be set to customize the bulk load operation in the Dynamic RDBMS stages configured for Microsoft SQL Server database type.

Commit size

Specifies the number of rows to load before committing them to the target table. The rows are loaded in delayed mode, and are stored to the table only after commit call is issued on the database load interface. The default value is 100 which means that the rows are committed to the target table in batches of 100 rows.

Use source identity data

Specifies whether values for identity column are provided with the data or if the database dynamically created them.

The supported values are:

Yes: The identity column values are supplied with the data.
No: The identity column values are generated by the database.; This is the default value.

Keep nulls

Specifies whether to preserve NULL values during the load.

Valid values are:

Yes: NULL values are inserted in the column.
No: NULL values in the data are replaced with the default value for the column.; This is the default value.

Check constraints

Specified the method for handling table constraints.

The supported values are:

Yes: The constraints are checked during the data load and the rows that violate the constraints are not loaded.
No: The constraints are ignored.; This is the default value.

Bind names

Specified the mechanism to use for associating columns on the link with the columns in the target table.

The supported values are:

Yes: The association is done by name. Columns on the link are matched with columns in the target table by name, irrespective of the column positions.; This is the default value.
No: The association is done by position. The first column on the link is associated with the first column in the table, the second column on the link with the second column in the table, and so forth.

Load action

Specifies the action to perform on the table before loading the data to the table.

The supported values are:

Clear table then load: Deletes rows from the table.
Truncate table then load: Issues truncate operation on the table.; This is the default value.
Append to the table: Preserves the existing content of the table.

Transaction isolation level

Specifies the transaction isolation level.

The supported values are:

ReadUncommitted: Dirty reads, non-repeatable reads and phantom reads are possible.
ReadCommitted: Non-repeatable and phantom reads are possible.; This is the default value.
RepeatableRead: Phantom reads are possible.
Serializable: The highest isolation level, none of the dirty reads, non-repeatable reads or phantom reads are possible.

Tracing level

Specifies the level of tracing information added to the log.

The actual value specified for the property is the sum of the flag values from the list. For example the value 3 means that property values and performance indicators are logged.

The supported values are:

0: No tracing.; This is the default value.
1: Traces stage property values.
2: Traces performance indicators.
4: Traces important events.

Before load statement

Specifies a semicolon-separated list of SQL statements to execute after connecting to the database and before loading any data to the target table.

There is no default value.

Continue before load

Specifies the action to take if a statement in the Before load statement set of statements fails.

The supported values are:

Yes: Logs any error as a warning and the next statement is processed. Each successfully completed statement is committed.; This is the default value.
No: If any statement fails to execute successfully, the transaction is rolled back and the job fails. A single commit is issued at the end if all the statements complete successfully.

After load statement

Specifies a semicolon-separated list of SQL statements to execute after all the data has been loaded and before the job ends.

There is no default value.

Continue after load

Same as Continue before load except it applies to the statements in the After load statement set of statements.