This topic describes the data types and parameters that Db2 Writer supports and how to configure it by using the code editor.

Notice Db2 Writer supports only exclusive resource groups for Data Integration, but not the default resource group or custom resource groups. For more information, see Use exclusive resource groups for data integration, Use the default resource group, and Add a custom resource group.

Background information

Db2 Writer allows you to write data to tables stored in Db2 databases. Specifically, 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. Internally, data is submitted to Db2 database in batches.

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

Db2 Writer obtains data from a Data Integration 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, Db2 Writer buffers data, and submits a write request when the amount of data in the buffer reaches a specific threshold.
Note A sync node that uses Db2 Writer must have at least the permission 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 mapping between the versions of Db2 JDBC drivers and Db2 versions, see IBM Support.

Data types

Db2 Writer supports most Db2 data types. Make sure that your data types are supported.

The following table describes the data types that Db2 Writer supports.
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 N/A
username The username that you can use to connect to the Db2 database. Yes N/A
password The password that you can use to connect to the Db2 database. Yes N/A
table The name of the destination table. Yes N/A
column The columns in the destination table to which data is written. Separate the columns with commas (,), for example, "column":["id","name","age"]. To write data to all the columns in the destination table, set the value to an asterisk (*), for example, "column":["*"]. Yes N/A
preSql The SQL statement to execute before the sync node is run. For example, you can clear outdated data before data synchronization. You can execute only one SQL statement on the codeless user interface (UI) and multiple SQL statements in the code editor. No N/A
postSql The SQL statement to execute after the sync node is run. For example, you can add a timestamp after data synchronization. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor. No N/A
batchSize The number of data records to write at a time. Set this parameter to a proper value based on your actual needs. This can greatly reduce the interactions between Data Integration and the Db2 database on the network, and increase the throughput. However, an excessively large value may lead to the out of memory (OOM) error during the data synchronization process. No 1024

Configure Db2 Writer by using the codeless UI

The codeless UI is not supported for Db2 Writer.

Configure Db2 Writer by using the code editor

The following example shows how to configure a sync node to write data to a Db2 database. 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 to execute after the sync node is run.
                "password":"",// The password that you can use to connect to the 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 destination table.
                "username":"",// The username that you can use to connect to the Db2 database.
                "preSql":[]// The SQL statement to execute before the sync node is run.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":false,// Specifies whether to enable bandwidth throttling. A value of false indicates that the bandwidth is not throttled. A value of true indicates that the bandwidth is throttled. The maximum transmission rate takes effect only if you set this parameter to true.
            "concurrent":1// The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}