Devart DB2 Destination is designed for loading data into DB2 tables.
The DB2 table you want to migrate data to can be selected in the Table Name combo box on the Component Properties tab of the Devart DB2 Destination data flow object.
The action that should be performed with data during migration is defined by the Action parameter of Devart DB2 Destination object, that provides the following options: Insert, Update, Delete, and BulkInsert. As all of the actions have certain peculiarities:
- Insert - as this case is the most common, we decided to cover it in a detailed tutorial. It uses simple INSERT statements to load data to DB2.
- BulkInsert - uses DB2-specific technique to quickly load data to DB2.
- Update - Devart DB2 Destination component in the Update mode enables updating
data in a DB2 table.
Note:Peculiarity of this action consists in the fact, that performing it requires using primary key values of the DB2 table. Thus, when performing an update, you must know and specify the primary key values of the DB2 table to update a record.
- Delete - Devart DB2 Destination component enables deleting data from a
DB2 table.
Note:
As is the case with the Update action, the ID column from the local data source corresponding to the Primary key values column in the DB2 table, must be used.
The amount of data that can be migrated at a time is limited by the BatchSize property.
BatchSize defines the maximum number of rows to be sent as a batch to the server, its default value is 0, which means that one commit will be issued at the end of the data insertion.
Additionally, Devart DB2 Destination allows setting the BulkInsertKeepIdentity property, which determines if the values, supplied for the identity columns are passed to the database. If False, they will not be sent to the database, and they will be autogenerated by the database. The BulkInsertTableLock property allows you to lock the table before the load operation. The BulkInsertTrancate property allows you to truncate data in the target table before the load operation.
Devart DB2 Data Flow components support error outputs, which allow you to manage how the component treats row-level errors in both incoming and outgoing data.
The error output includes the column set taking part in the data flow and additional columns, they are ErrorCode that identifies the error, ErrorColumn that contains the lineage identifier of the error column and ErrorDescription that provides a detailed description of the error.
The ErrorCode column can accept the following values:
| ErrorCode | Description |
|---|---|
| -1 | Means that the record is valid. |
| >0 | Means that the record causes an error. |
Additionally, when the primary key values are automatically generated for the target table, Devart DB2 Destination retrieves the generated values and adds them to its output columns.