To use Db2 for Linux, UNIX, and Windows (LUW) sources in database ingestion and replication tasks, first prepare the source database and review the usage considerations.
Source preparation
•Database Ingestion and Replication requires users to have the SELECT privilege on several system catalog tables and views. Use the following grant statement:
GRANT SELECT ON <catalog_table> TO <dbmi_user>
Issue this grant for each of the following catalog tables or views:
- SYSCAT.COLUMNS
- SYSCAT.DATAPARTITIONEXPRESSION
- SYSCAT.DATAPARTITIONS
- SYSCAT.INDEXCOLUSE
- SYSCAT.INDEXES
- SYSCAT.SCHEMATA
- SYSCAT.TABLESSYSCAT.TABLES
- SYSIBM.COLUMNS
- SYSIBM.SYSDUMMY1
- SYSIBM.SYSPLAN
- SYSIBM.SYSVERSIONS
•To create and execute the Db2 package required for Database Ingestion and Replication to successfully connect to the Db2 database and issue SQL requests, ensure that the BINDADD privilege is granted to the Database Ingestion and Replication user dbmi_user:
GRANT BINDADD ON DATABASE TO <dbmi_user>
•The Db2 for LUW Database Ingestion connector uses JDBC connection. When you connect to the database for the first time using the DataDirect JDBC driver, you need the create package privileges to create and bind the following JDBC packages:
- DDJC360A
- DDJC360B
- DDJC360C
- DDJR360A
- DDJR360B
- DDJR360C
- DDJS360A
- DDJS360B
- DDJS360C
- DDJU360A
- DDJU360B
- DDJU360C
For Log-based CDC
•Because Log-based CDC uses the IBM db2ReadLog API to read logs, the Database Ingestion and Replication user must have SYSADM or DBADM authority.
•The Db2 DATA CAPTURE CHANGES option must be set for the tables participating in Log-based CDC if the tables were not created with that option. You can use the following SQL statement:
ALTER TABLE <TABLE_NAME> DATA CAPTURE CHANGES;
If you use the deprecated LONG VARCHAR or LONG VARGRAPHIC data types, define the tables with the INCLUDE LONGVAR COLUMNS clause:
ALTER TABLE <TABLE_NAME> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
Alternatively, when you create a database ingestion and replication incremental load or combined load task, run or download the generated CDC script that enables DATA CAPTURE CHANGES for each table selected for the task.
•You must install the DB2 11.5 client on the Secure Agent server and on the Db2 database system that will host the capture catalog tables.
•Ensure that archive logging is active for the Db2 source database from which change data will be captured.
•Ensure that a valid Db2 environment exists for the Database Ingestion and Replication user. To set up the Db2 environment, "source" the db2 profile script:
source INSTHOME/sqllib/db2chrc
This script sets up the environment variables , such as DB2INSTANCE, PATH, and LD_LIBRARY_PATH, which are needed for the current session to interact with the Db2 instance.
On Linux, ensure that the path to the Db2 client is specified in the PATH and LD_LIBRARY_PATH environment variables.
•Catalog the Db2 environment on the Secure Agent machine by performing the following steps:
1Catalog the TCP/IP node by using the following command:
db2 catalog tcpip node <node_name> remote <database_server_name> server <port_number>
2Catalog the Db2 database and register it with the client by using the following command:
db2 catalog database <database_name> as <database_alias> at node <node_name>
3If you’re prompted to refresh the cache, use this command:
db2 terminate
4Check that you can connect to the database from the Secure Agent machine by using the following command:
db2 connect to <database_name> user <user_name> using passwd
•If you want to use an encrypted Db2 for LUW source database, complete the following steps to create and configure the database and keystore:
1Create a keystore by using the following command:
2Stop the Db2 for LUW instance by using the following command:
db2stop
3Update the database manager (DBM) configuration with the keystore information. Use the following command:
update dbm cfg using keystore_location "C:\Program Files\IBM\gsk8\bin\db2pkeystorep.p12" keystore_type pkcs12
4Start the Db2 for LUW instance by using the following command:
db2start
5Create an encrypted database by using the following command:
db2 create database cdirencr encrypt
6Verify you can connect to the encrypted database by using the following command:
db2 connect to cdirencr
7Confirm the configuration for the encrypted database by using the following command:
db2 get db config for cdirencr
Check that Encrypted Database parameter is set.
Usage considerations
•Database ingestion and replication incremental load and combined initial and incremental load jobs that have a Db2 for LUW source can use either the Query-based or Log-based change data capture method.
- Query-based CDC uses a SQL statement with a WHERE clause that references a common CDC query column to identify the rows with Insert and Update changes. Configuration of the source database is limited to adding the CDC query column to each source table. Users must have at least read only access to the source tables. The CDC query column type must be equivalent to timestamp, without a time zone. Currently, this CDC method has been tested with Snowflake targets only.
- Log-based CDC captures Inserts, Updates, and Deletes for the source tables on Linux or Windows from the Db2 transaction logs in near real time. It uses a capture catalog table that is generated for each task in the Db2 source database when you run the job. The table stores information about the Db2 source tables, column definitions, and valid Db2 log positions.
Note: In the current release, access to the Log-based CDC method is controlled by a flag on your organization. If you do not have access to this CDC method, contact Informatica Global Customer Support.
•Because Database Ingestion and Replication expects each source table row to be unique, Informatica recommends that each source table have a primary key. Database Ingestion and Replication does not honor unique indexes in place of a primary key. If no primary key is specified, Database Ingestion and Replication treats all columns as if they are part of the primary key.
•You can use a standby database in read-only mode as an alternate server in case of a failed connection to the primary database. To specify one or more standby databases, enter the AlternateServers parameter in the Advanced Connection Properties field of the Db2 for LUW Database Ingestion connection properties, as in the following example:
•If you use a Secure Agent group with multiple agents and the active agent goes down unexpectedly, database ingestion and replication jobs can automatically switch over to another available agent in the Secure Agent group. The automatic switchover occurs after the 15 minute heartbeat interval elapses. For database ingestion and replication jobs that have a Db2 for LUW source, automatic switchovers are subject to following limitations:
- Jobs cannot have persistent storage enabled.
- Jobs that have Kafka targets must store checkpoint information in the Kafka header. For any jobs that existed before the July 2025 release, automatic switchovers can't occur because checkpoint information is stored in the checkpoint file in the Secure Agent.
- Jobs that use the Query-based CDC method must have an Amazon Redshift, Databricks, Google BigQuery, Microsoft Azure Synapse Analytics, Oracle, PostgreSQL, Snowflake, or SQL Server target.
•Database ingestion and replication initial load jobs or incremental load or combined load jobs that use Query-based CDC can replicate data from columns that have LOB data types to Microsoft Azure Data Lake Storage Gen 2, Microsoft Azure Synapse Analytics, Microsoft Fabric OneLake, or Snowflake targets. For information about the default mappings of supported source data types to target data types, see Default data-type mappings.
•For Log-based CDC, the following considerations apply:
- The tasks must have a Db2 for LUW version 11.x or 12.x source on AIX, Linux, or Windows with an Amazon Redshift, Databricks, Kafka, Oracle, PostgreSQL, Azure SQL Server, or Snowflake target.
- You can include the tasks in a CDC staging group.
- The source tables cannot be on AWS systems or in Db2 pureScale environments.
- LOB, XML and user-defined DISTINCT and STRUCT data types are not supported.
•Log-based CDC uses the IBM db2ReadLog API to read logs. This API can't be used on a standby database for CDC.