edit-icon download-icon

Configure DB2 writer

Last Updated: Mar 21, 2018

The DB2 Writer plug‑in provides the ability to write data into the target tables of DB2 databases. At the underlying implementation level, DB2 Writer connects to a remote DB2 database through JDBC, and runs the “insert into …” SQL statement to write data into DB2. Data is submitted and written into the database in batch within DB2.

DB2 Writer is designed for ETL developers to import data from data warehouses to DB2. DB2 Writer can also be used as a data migration tool by DBA and other users.

DB2 Writer acquires the protocol data generated by Reader by means of the Data Integration framework. If the primary key conflicts with the unique index, data cannot be written into the conflicting lines. To improve performance, we use PreparedStatement + Batch and configure rewriteBatchedStatements=trueto buffer data to the thread context buffer. A write request is submitted only when the amount of data in the buffer reaches the threshold.

Note:

The entire task requires at least the “insert into…” permission. Whether other permissions are required depends on the statements you specified in preSql and postSql when you configure the task.

DB2 Writer supports most data types in DB2. Check whether your data type is supported.

DB2 Writer converts DB2 data types as follows:

Category DB2 data type
Integer smallint
Floating point decimal, real, double
String char, character, varchar, graphic, vargraphic, long varchar, clob, long vargraphic, dbclob
Date and time date, time, timestamp
Boolean
Binary blob

Parameter description

  • jdbcUrl

    • Description: Information of the JDBC connection to the DB2 database. In accordance with the DB2 official specification, jdbcUrl in the db2 format is jdbc:db2://ip:port/database, and the URL attachment control information can be entered.

    • Required: Yes

    • Default value: None

  • username

    • Description: Username for the data source.

    • Required: Yes

    • Default value: None

  • password

    • Description: Password corresponding to the specified username for the data source.

    • Required: Yes

    • Default value: None

  • table

    • Description: The table selected for synchronization.

    • Required: Yes

    • Default value: None
  • column

    • Description: The fields of the target table into which data is required to be written. These fields are separated by commas. For example: “column”: [“id”, “name”, “age”]. Use * if it is required to write data into all columns in sequence. For example: “column”: [“*”]

    • Required: Yes

    • Default value: None
  • preSql

    • Description: The SQL statement that runs before the data synchronization task runs. Currently, you can run only one SQL statement. For example, clear old data.

    • Required: No

    • Default value: None
  • postSql

    • Description: The SQL statement that runs after the data synchronization task runs. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, add a timestamp.

    • Required: No

    • Default value: None
  • batchSize

    • Description: The quantity of records submitted in one operation. Setting this parameter can greatly reduce the interactions between Data Integration and DB2 over the network, and increase the overall throughout. However, an excessively large value may cause the running process of Data Integration to become out of memory (OOM).

    • Required: No

    • Default value: 1024

Development in wizard mode

Currently, development in wizard mode is not supported.

Development in script mode

Configure the data synchronization job to write data to DB2:

  1. {
  2. "type": "job",
  3. "version": "1.0",
  4. "configuration": {
  5. "reader": {},
  6. "writer": {
  7. "plugin": "db2",
  8. "parameter": {
  9. "jdbcUrl": "jdbc:db2://ip:port/database",
  10. "username": "username",
  11. "password": "password",
  12. "table": "table",
  13. "column": [
  14. "id", "name", "age"
  15. ],
  16. "preSql": [
  17. "delete from XXX;"
  18. ]
  19. }
  20. }
  21. }
  22. }
Thank you! We've received your feedback.