DB2 Reader - Data Integration| Alibaba Cloud Documentation Center

Background information

Db2 Reader can read data from Db2.

Db2 Reader connects to a remote Db2 database by using Java Database Connectivity (JDBC), generates an SQL statement based on your configurations, and then sends the statement to the database. The system executes the statement on the database and returns data. Then, Db2 Reader assembles the returned data into abstract datasets of the data types supported by Data Integration and sends the datasets to a writer.

Db2 Reader generates an SQL statement based on the settings of the table, column, and where parameters and sends the generated statement to the Db2 database.
If you specify the querySql parameter, Db2 Reader sends the value of this parameter to the Db2 database.

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

Data types

Db2 Reader supports most Db2 data types. Make sure that the data types of your database are supported.

The following table lists the data types that are supported by Db2 Reader.


Data Integration data type	Db2 data type
Integer	SMALLINT
Floating point	DECIMAL, REAL, and DOUBLE
String	CHAR, CHARACTER, VARCHAR, GRAPHIC, VARGRAPHIC, LONG VARCHAR, CLOB, LONG VARGRAPHIC, and DBCLOB
Date and time	DATE, TIME, and TIMESTAMP
Binary	BLOB

Parameters


Parameter	Description	Required	Default value
datasource	The name of the data source. It must be the same as the name of the added data source. You can add data sources by using the code editor.	Yes	No default value
jdbcUrl	The JDBC URL of the Db2 database. The URL must be in the `jdbc:db2://ip:port/database` format in accordance with official Db2 specifications. You can also specify the information of the attachment facility.	Yes	No default value
username	The username that you use to connect to the database.	Yes	No default value
password	The password that you use to connect to the database.	Yes	No default value
table	The name of the table from which you want to read data. Each synchronization node can be used to synchronize data to only one table.	Yes	No default value
column	The names of the columns from which you want to read data. Specify the names in a JSON array. The default value is [ * ], which indicates all the columns in the source table. You can select specific columns to read. The column order can be changed. This indicates that you can specify columns in an order different from the order specified by the schema of the source table. Constants are supported. The column names must be arranged in compliance with the SQL syntax that is supported by Db2, such as `["id", "1", "'const name'", "null", "upper('abc_lower')", "2.3" , "true"]`. id: a column name. 1: an integer constant. 'const name': a string constant, which is enclosed in single quotation marks ('). null: a null pointer. upper('abc_lower'): a function expression. 2.3: a floating-point constant. true: a Boolean value. The column parameter must explicitly specify all the columns from which you want to read data. This parameter cannot be left empty.	Yes	No default value
splitPk	The field that is used for data sharding when Db2 Reader reads data. If you specify this parameter, the source table is sharded based on the value of this parameter. Data Integration then runs parallel threads to read data. This improves data synchronization efficiency. We recommend that you set the splitPk parameter to the name of the primary key column of the table. Data can be evenly distributed to different shards based on the primary key column, but not intensively distributed only to specific shards. The splitPk parameter supports sharding only for data of integer data types. If you set this parameter to a column of an unsupported data type, such as a string, floating point, or date data type, an error is reported.	No	""
where	The WHERE clause. Db2 Reader generates an SQL statement based on the settings of the table, column, and where parameters and uses the generated statement to read data. For example, you can set this parameter to `gmt_create > $bizdate` in an actual business scenario to read data that is generated on the current day. You can use the WHERE clause to read incremental data. If this parameter is left empty, Db2 Reader reads all the data in the source table.	No	No default value
querySql	The SQL statement that is used for refined data filtering. If you specify this parameter, Data Integration filters data based on the value of this parameter. For example, if you want to join multiple tables for data synchronization, set this parameter to `select a,b from table_a join table_b on table_a.id = table_b.id`. If you specify this parameter, Db2 Reader ignores the settings of the table, column, and where parameters.	No	No default value
fetchSize	The number of data records to read at a time. This parameter determines the number of interactions between Data Integration and the database and affects read efficiency. Note If you set this parameter to a value greater than 2048, an out of memory (OOM) error may occur during data synchronization.	No	1024

Configure Db2 Reader by using the codeless UI

This method is not supported.

Configure Db2 Reader by using the code editor

In the following code, a synchronization node is configured to read data from a Db2 database. For more information about how to configure a synchronization node by using the code editor, see Create a sync node by using the code editor.

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        {
            "stepType":"db2",// The reader type.
            "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 from which you want to read data. 
                "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 bandwidth throttling. The value false indicates that bandwidth throttling is disabled, and the value true indicates that bandwidth 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.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

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 node 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 node.

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 node. The following workarounds can be used:
- Enable Db2 Reader to use a single thread to read data in a synchronization node. 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.