This topic describes the data types and parameters that are supported by Db2 Writer and how to configure Db2 Writer by using the codeless user interface (UI) and code editor.

Notice Db2 Writer supports only exclusive resource groups for Data Integration, but not shared resource groups or custom resource groups for Data Integration. For more information, see Create and use an exclusive resource group for Data Integration, Use a shared resource group, and Add a custom resource group for Data Integration.

Background information

Db2 Writer writes data to tables stored in Db2 databases. Db2 Writer connects to a remote Db2 database by using Java Database Connectivity (JDBC), and executes an INSERT INTO statement to write data to the Db2 database. The data is written to the Db2 database in batches.

Db2 Writer is designed for extract, transform, load (ETL) developers to import data from data warehouses to Db2 databases. Db2 Writer can also be used as a data migration tool by users such as database administrators.

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

The version of the Db2 JDBC driver that Db2 Writer uses 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.

Data types

Db2 Writer 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 Writer.
Category 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
Boolean N/A
Binary BLOB

Parameters

Parameter Description Required Default value
jdbcUrl The JDBC URL of the Db2 database. In accordance with the official Db2 specifications, the URL must be in the jdbc:db2://ip:port/database format. You can also specify the information of the attachment facility. Yes No default value
username The username that you use to connect to the Db2 database. Yes No default value
password The password that you use to connect to the Db2 database. Yes No default value
table The name of the table to which you want to write data. Yes No default value
column The names of the columns to which you want to write data. Separate the names with commas (,), such as "column": ["id", "name", "age"]. If you want to write data to all the columns in the destination table, set this parameter to an asterisk (*), such as "column": ["*"]. Yes No default value
preSql The SQL statement that you want to execute before the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to delete outdated data. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. No No default value
postSql The SQL statement that you want to execute after the synchronization node is run. For example, you can set this parameter to the SQL statement that is used to add a timestamp. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. No No default value
batchSize The number of data records to write at a time. Set this parameter to an appropriate value based on your business requirements. This greatly reduces the interactions between Data Integration and Db2 and increases throughput. If you set this parameter to an excessively large value, an out of memory (OOM) error may occur during data synchronization. No 1024

Configure Db2 Writer by using the codeless UI

This method is not supported.

Configure Db2 Writer by using the code editor

In the following code, a synchronization node is configured to write data to a Db2 database by using the code editor. For more information, see Create a sync node by using the code editor. 
{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"db2",// The writer type. 
            "parameter":{
                "postSql":[],// The SQL statement that you want to execute after the synchronization node 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 to which you want to write data. 
                "username":"",// The username that you use to connect to the Db2 database. 
                "preSql":[]// The SQL statement that you want to execute before the synchronization node 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 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"
            }
        ]
    }
}