DataWorks:Db2 data source

Supported Db2 versions

The version of the Db2 JDBC driver that Db2 Reader and Db2 Writer use is IBM Data Server Driver for JDBC and SQLJ 4.11.77. For more information about the mappings between the versions of Db2 JDBC drivers and the Db2 versions, see IBM Support.

Limits

• Db2 Reader and Db2 Writer support only exclusive resource groups for Data Integration, but not the shared resource group for Data Integration or custom resource groups for Data Integration.

• Db2 Writer obtains data from a reader and writes the data to the destination database by executing the INSERT INTO statement. If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows. To improve performance, Db2 Writer executes batch updates by using a PreparedStatement object and sets the rewriteBatchedStatements parameter to true. This way, Data Integration commits a write request when the amount of the buffered data that it accumulates reaches a specific threshold.

  Note

  A synchronization task that uses Db2 Writer must have at least the permissions to execute the INSERT INTO statement. Whether other permissions are required depends on the SQL statements that you specify in the preSql and postSql parameters when you configure the task.

• Data of views can be read during batch synchronization.

• The Db2 AES encryption algorithm is not supported.

Data type mappings

Batch data read and write

Db2 Reader and Db2 Writer support most Db2 data types. Make sure that the data types of your database are supported.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization node to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure synchronization settings to implement batch synchronization of all data in a database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

Additional information

• Data synchronization between primary and secondary databases

  A secondary Db2 database can be deployed for disaster recovery. The secondary database continuously synchronizes data from the primary database based on binary logs. Data latency between the primary and secondary databases cannot be prevented. This may result in data inconsistency.

• Data consistency control

  Db2 is a relational database management system (RDBMS) that supports strong consistency for data queries. A database snapshot is created before a synchronization task starts. Db2 Reader reads data from the database snapshot. Therefore, if new data is written to the database during data synchronization, Db2 Reader cannot obtain the new data.

  Data consistency cannot be ensured if you enable Db2 Reader to use parallel threads in a single synchronization task.

  Db2 Reader shards the source table based on the value of the splitPk parameter and uses parallel threads to read data. These parallel threads belong to different transactions and read data at different points in time. Therefore, the parallel threads observe different snapshots.

  Data inconsistencies cannot be prevented if parallel threads are used for a synchronization task. The following workarounds can be used:

  • Enable Db2 Reader to use a single thread to read data in a synchronization task. This indicates that you do not need to specify a shard key for Db2 Reader. This way, data consistency is ensured, but data is synchronized at low efficiency.

  • Make sure that no data is written to the source table during data synchronization. This ensures that the data in the source table remains unchanged during data synchronization. For example, you can lock the source table or disable data synchronization between primary and secondary databases. This way, data is efficiently synchronized, but your ongoing services may be interrupted.

• Character encoding

  Db2 Reader uses JDBC to read data. This enables Db2 Reader to automatically convert the encoding formats of characters. Therefore, you do not need to specify the encoding format.

• Incremental data synchronization

  Db2 Reader uses JDBC to connect to a database and uses a SELECT statement with a WHERE clause to read incremental data.

  • For batch data, incremental add, update, and delete operations (including logically delete operations) are distinguished by timestamps. Specify the WHERE clause based on a specific timestamp. The time indicated by the timestamp must be later than the time indicated by the latest timestamp in the previous synchronization.

  • For streaming data, specify the WHERE clause based on the ID of a specific record. The ID must be greater than the maximum ID involved in the previous synchronization.

  If the data that is added or modified cannot be distinguished, Db2 Reader can read only full data.

• Syntax validation

  Db2 Reader allows you to specify custom SELECT statements by using the querySql parameter but does not verify the syntax of these statements.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization node, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization node by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for Db2 Reader

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        {
            "stepType":"db2",// The plug-in name.
            "parameter":{
                "password":"",// The password that you use to connect to the Db2 database.
                "jdbcUrl":"",// The JDBC URL of the Db2 database.
                "column":[
                    "id"
                ],
                "where":"",// The WHERE clause. 
                "splitPk":"",// The shard key.
                "table":"",// The name of the table. 
                "username":""// The username that you use to connect to the Db2 database.
            },
            "name":"Reader",
            "category":"reader"
        },
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
             "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
             "concurrent":1 // The maximum number of parallel threads. 
             "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for Db2 Reader

Code for Db2 Writer

{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"db2",// The plug-in name. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization task is run. 
                "password":"",// The password that you use to connect to the Db2 database. 
                "jdbcUrl":"jdbc:db2://ip:port/database",// The JDBC URL of the Db2 database. 
                "column":[
                    "id"
                ],
                "batchSize":1024,// The number of data records to write at a time. 
                "table":"",// The name of the table. 
                "username":"",// The username that you use to connect to the Db2 database. 
                "preSql":[]// The SQL statement that you want to execute before the synchronization task is run. 
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for Db2 Writer