DB2

IBM DB2 is a relational database known for its high performance, scalability, and reliability in managing structured data. ClickPipes supports using DB2 as both a source and target database, helping you quickly build data pipelines. This guide will walk you through connecting a DB2 data source in ClickPipes.

Supported Versions and Architectures

Category	Description
Version	DB2 9.7~11.x, with extensive validation and support for DB2 11.x Supports various platforms, including Linux, UNIX, Windows, AIX, and AS400
Architecture	No limitations

Supported Data Types

Category	Data Types
String and Text	VARCHAR, CHARACTER, CLOB, LONG VARCHAR
Numeric	INTEGER, DECIMAL, BIGINT, SMALLINT, DOUBLE, REAL
Date and Time	TIMESTAMP, DATE, TIME
Boolean	BOOLEAN
Binary Large Object	BLOB
XML	XML

Limitations

• When performing incremental data synchronization with DB2 as the source, executing a DDL operation on the DB2 database requires running the following stored procedure immediately afterward to prevent potential synchronization failures:

  CALL SYSPROC.ADMIN_CMD('REORG TABLE <schema>.<table>');
• In scenarios where the ReadLog size in DB2 is very large, incremental synchronization based on a specified time may encounter slow LRI lookup initially, potentially leading to a longer time to start incremental synchronization.
• Due to variable-length fields in the DB2 database, DML operations may trigger space expansion, resulting in discrepancies between the expected and actual operation types in task monitoring or logs (e.g., insert and delete actions appearing within an update event). Although the log may not match expectations, data accuracy remains unaffected.
• Deploying a raw log service to parse DB2 incremental data changes requires configuring the service with the same national language code as the DB2 database.

    # UTF-8 Language Code
    DB2set DB2codepage=1208
    # GBK Language Code
    DB2set DB2codepage=1386
    # Country Code
    DB2set DB2country=86

Considerations

When capturing incremental data changes, periodic calls to the DB2 ReadLog API may introduce load on the database and consume network bandwidth and disk I/O resources.

Preparation

Before connecting to a DB2 database, you need to complete account authorization and other preparatory steps. This guide provides examples assuming the DB2 database is deployed on a Linux platform.

1. Execute the following commands in sequence to create a user for data replication/transformation tasks. On Linux, log in and execute the following commands to create a DB2 database user and set a password:

   # Replace username with your desired username
   sudo useradd username
   sudo passwd username

2. Grant permissions to the newly created account.

   1. Log in to DB2 as a user with DBA privileges.

   2. Execute the following commands to grant object management and data read/write permissions to a specific Schema. You can customize the permissions further based on your business needs. For more details, refer to GRANT TABLE.

      Full Data Synchronization

      -- Replace username and schema_name with the actual username and schema name
      GRANT ON SCHEMA schema_name TO USER username;

      Incremental Data Synchronization

      -- Switch to the root container
      -- Replace username with the actual username
      GRANT DBADM TO username;

      tip

      Since incremental extraction relies on DB2's ReadLog API, system admin or database admin privileges are required.

Source Config

• Connection Settings

  • Name: Enter a unique name with business significance.
  • Type: Specify whether DB2 is used as a source or target.
  • DB Address: Enter the database connection address.
  • Port: The service port for the database, default is 50001.
  • Service Name: Enter the database name.
  • Schema: Schema name, one connection per Schema. For multiple Schemas, create multiple data connections.
  • Additional Connection String Parameters: Additional connection parameters, default is empty.
  • User and Password: Enter the database username and password.