DataWorks:DB2

Supported versions

DB2 Reader and DB2 Writer use the IBM Data Server Driver for JDBC and SQLJ 4.11.77 driver. For more information about the version mapping between the DB2 driver and the database service, see the official documentation.

Limits

• DB2 Reader and DB2 Writer support Serverless resource groups and exclusive resource groups for Data Integration. Public resource groups and custom resource groups are not supported.

• DB2 Writer retrieves data from the Reader through the data synchronization framework and writes the data to DB2 using insert into statements. If a primary key or unique index conflict occurs, the conflicting rows are not written. For better performance, DB2 Writer uses PreparedStatement + Batch with rewriteBatchedStatements=true. Data is buffered in the thread context buffer, and a write request is sent only when the buffer reaches a specified threshold.

  Note

  The task requires at least the insert into permission. Other permissions may be required depending on the statements that you specify in the preSql and postSql parameters when you configure the task.

• Offline synchronization supports reading data from views.

• The DB2 AES encryption algorithm is not supported.

Supported field types

DB2 Reader and DB2 Writer support most DB2 data types for offline read and write operations. However, some data types are not supported. We recommend that you check your data types carefully.

The following table lists the DB2 data types that DB2 Reader and DB2 Writer support.

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source management. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

FAQ

• Data restoration issues in active/standby synchronization

  In an active/standby disaster recovery configuration for DB2, the secondary database continuously restores data from the primary database using binlogs. A time lag is inherent in this synchronization process. This lag, especially when exacerbated by network latency, means the data on the secondary database may not be a complete, real-time mirror of the primary database. This can result in significant data discrepancies between the two databases.

• Consistency constraints

  DB2 is a relational database management system (RDBMS) that provides query interfaces with strong consistency. For example, if another data source writes data to the database while a sync task is running, DB2 Reader does not retrieve the updated data because of the database's snapshot feature.

  The preceding description applies to the data synchronization consistency of DB2 Reader in a single-threaded model. DB2 Reader can concurrently extract data based on your configurations. Therefore, strict data consistency cannot be guaranteed.

  When DB2 Reader partitions data based on the splitPk parameter, it initiates multiple concurrent tasks to synchronize the data. These concurrent tasks do not belong to the same read transaction and are separated by time intervals. For raw data, snapshots read by multiple concurrent tasks may be incomplete or inconsistent.

  A technical solution for consistent snapshots in a multi-threaded environment is not available. You can address this issue only from an engineering perspective, which involves trade-offs. Consider the following solutions based on your requirements:

  • Use single-threaded synchronization. In this mode, data is not partitioned. The disadvantage is that the synchronization speed is slow, but data consistency is well-maintained.

  • Disable other data writers to ensure that the source data is static. For example, you can lock tables or disable synchronization to the secondary database. The disadvantage is that this may affect online services.

• Database encoding issues

  DB2 Reader uses Java Database Connectivity (JDBC) to extract data. JDBC is natively compatible with various encodings and performs encoding conversion at the underlying layer. Therefore, you do not need to specify an encoding for DB2 Reader. DB2 Reader can automatically detect and transcode the encoding.

• Methods for incremental data synchronization

  DB2 Reader uses JDBC SELECT statements to extract data. Therefore, you can use SELECT…WHERE… to perform incremental data extraction in the following ways:

  • When an online application writes data to the database, a `modify` field is populated with a timestamp that indicates when the change occurred. This includes new, updated, and logically deleted data. For this type of application, you can configure DB2 Reader to add a `WHERE` clause that uses the timestamp of the last synchronization.

  • For new append-only data, you can configure DB2 Reader to add a `WHERE` clause that uses the maximum auto-increment ID from the previous synchronization.

  If your business logic does not use a specific field to distinguish between new and modified data, DB2 Reader cannot perform incremental data synchronization. In this case, only full data synchronization is possible.

• SQL security

  DB2 Reader provides the querySql parameter that lets you customize SELECT statements for data extraction. DB2 Reader does not perform any security checks on the querySql statement.

Appendix: Script demo and parameter description

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        {
            "stepType":"db2",// The plug-in name.
            "parameter":{
                "password":"",// The password.
                "jdbcUrl":"",// The JDBC connection string for the DB2 database.
                "column":[
                    "id"
                ],
                "where":"",// The filter condition.
                "splitPk":"",// The field used for data partitioning.
                "table":"",// The table name. 
                "username":""// The username.
            },
            "name":"Reader",
            "category":"reader"
        },
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records.
        },
        "speed":{
             "throttle":true,// If you set throttle to false, the mbps parameter does not take effect and no rate limit is imposed. If you set throttle to true, a rate limit is imposed.
             "concurrent":1, // The number of concurrent jobs.
             "mbps":"12"// The rate limit. 1 mbps is equal to 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"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 to execute after the data synchronization task.
                "password":"",// The password.
                "jdbcUrl":"jdbc:db2://ip:port/database",// The JDBC connection string for the DB2 database.
                "column":[
                    "id"
                ],
                "batchSize":1024,// The number of records to submit in a single batch.
                "table":"",// The table name.
                "username":"",// The username.
                "preSql":[]// The SQL statement to execute before the data synchronization task.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records.
        },
        "speed":{
            "throttle":true,// If you set throttle to false, the mbps parameter does not take effect and no rate limit is imposed. If you set throttle to true, a rate limit is imposed.
            "concurrent":1, // The number of concurrent jobs.
            "mbps":"12"// The rate limit. 1 mbps is equal to 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}