DataWorks:DB2

Supported versions

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

Supported capabilities

Supported field types

DB2 Reader and DB2 Writer support the following DB2 data types for offline read and write operations. Check your data types before configuring a synchronization task.

Limitations

• Resource groups: 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.

• Write behavior: DB2 Writer writes data using insert into statements. If a primary key or unique index conflict occurs, the conflicting rows are not written. For performance, DB2 Writer uses PreparedStatement + Batch with rewriteBatchedStatements=true, buffering data in a thread context buffer and sending a write request when the buffer reaches the configured threshold.

  The synchronization task requires at least the insert into permission. Additional permissions may be required depending on the SQL statements configured in the preSql and postSql parameters.

• Encryption: The DB2 AES encryption algorithm is not supported.

Add a data source

Add the DB2 data source to DataWorks before developing a synchronization task. Follow the instructions in Data source management. Parameter descriptions are available in the DataWorks console when you add the data source.

Develop a data synchronization task

FAQ

Why might the standby database be missing data during active/standby synchronization?

In an active/standby disaster recovery setup, the standby database continuously applies changes from the primary database via binlogs. This process has an inherent time lag, which network latency can make worse. As a result, the standby database may not reflect the primary database in real time, and significant data discrepancies between the two are possible.

Does DB2 Reader guarantee data consistency during concurrent extraction?

DB2 is a relational database management system (RDBMS) with strong consistency query interfaces. In single-threaded mode, DB2 Reader reads a consistent snapshot—if another source writes data while the task runs, DB2 Reader does not retrieve those updates.

When you configure concurrent extraction using the splitPk parameter, DB2 Reader launches multiple tasks that are not part of the same read transaction and run at different times. In this case, the snapshots read by concurrent tasks may be incomplete or inconsistent. Two workarounds are available:

• Use single-threaded synchronization (no data partitioning). This preserves data consistency but reduces synchronization speed.

• Disable writes to the source during synchronization, for example by locking tables or pausing replication to the standby database. This keeps source data static but may affect online services.

Do I need to configure the encoding for DB2 Reader?

No. DB2 Reader uses Java Database Connectivity (JDBC) to extract data. JDBC handles encoding conversion at the underlying layer, so DB2 Reader automatically detects and transcodes the encoding.

How do I set up incremental data synchronization?

DB2 Reader extracts data using JDBC SELECT statements, so you can use SELECT...WHERE... for incremental extraction in two ways:

• If the source application populates a timestamp field (such as a modify column) when data changes, configure a where clause using the timestamp from the last synchronization run. This captures new, updated, and logically deleted rows.

• For append-only data, configure a where clause using the maximum auto-increment ID from the previous synchronization run.

If no field distinguishes new data from existing data, only full synchronization is possible.

Is the querySql parameter safe from SQL injection?

DB2 Reader does not perform security checks on the querySql statement. Review and validate any custom SQL before configuring this parameter.

Appendix: Script demo and parameter description

Use the following scripts and parameter references when configuring a batch synchronization task in the code editor. For the general procedure, see Configure a task in the code editor.

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "db2",
            "parameter": {
                "datasource": "",
                "jdbcUrl": "",
                "username": "",
                "password": "",
                "table": "",
                "column": [
                    "id"
                ],
                "where": "",
                "splitPk": ""
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "stream",
            "parameter": {},
            "name": "Writer",
            "category": "writer"
        }
    ],
    "setting": {
        "errorLimit": {
            "record": "0"
        },
        "speed": {
            "throttle": true,
            "concurrent": 1,
            "mbps": "12"
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "stream",
            "parameter": {},
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "db2",
            "parameter": {
                "jdbcUrl": "jdbc:db2://ip:port/database",
                "username": "",
                "password": "",
                "table": "",
                "column": [
                    "id"
                ],
                "preSql": [],
                "postSql": [],
                "batchSize": 1024
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "setting": {
        "errorLimit": {
            "record": "0"
        },
        "speed": {
            "throttle": true,
            "concurrent": 1,
            "mbps": "12"
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}