You can define a CDC staging group that includes multiple database ingestion and replication incremental load or combined initial and incremental load tasks that have a Db2 for i, Db2 for LUW, Db2 for z/OS, Oracle, Microsoft SQL Server, or SAP HANA source. Db2 for LUW, Oracle, and SAP HANA sources must use the Log-based CDC method.
If you enable staging groups, a CDC Staging Task reads data from the source database in a single pass and writes the data to common storage. The apply tasks that you associate with the group can then read data from the common storage and apply the data to different targets.
This feature can improve CDC performance and scalability because data is read from the source logs once by the CDC Staging Task instead of being read multiple times by the apply jobs. Also, if you use a Secure Agent group, the apply jobs can run on different agents in the same Secure Agent group.
Note:
To get access to this feature, contact Informatica Global Customer Support. They'll set the cdir.cdc.group.enabled flag to true for your organization.
The user who starts a CDC staging job must be in the organization in which the staging group was enabled. If you enable the staging group while using a sub-organization, you must start the CDC staging job by a user from that sub-organization. If you try to run the CDC staging job from the parent organization instead, the job doesn't start.
When you create a new task in the task wizard, you can add it to an existing staging group or create a new staging group. You cannot add a pre-existing task to a staging group.
When determining whether to use a CDC staging group, consider the following points
•For Db2 for i sources, all of the member apply tasks must use the same journal.
•For log-based sources, including Db2 for LUW, Db2 for z/OS, Oracle, and SQL Server, the tasks in the group can process different tables with different schemas in the same source database.
•Db2 for LUW and Oracle sources must use the Log-based change capture method.
•SQL Server sources must use the CDC Tables or Log-based change capture method.
•SAP HANA sources must use the Log-based change capture method and the incremental load or combined load type. If you set the capture type to Log Based in the connection to the source database, each apply task that uses the connection has the Log-based method and CDC staging is automatically enabled with the same staging group. After you select a staging group or create a new one for the first task, all subsequent apply tasks that use the same connection are added the same group. You can't use a CDC Staging Task with SAP HANA sources that use the Trigger-based change capture method.
For Db2 for z/OS sources, you might need to perform some prerequisite tasks before using staging groups: 1) create the INFA_DATA’ table that stores DBID, OBID and PSID information for all of the selected source tables, if this information might be greater than 16 KB and 2) install the Db2 stored procedure dated 11/01/2024 or later that can use this table to identify the selected tables for processing. For stored procedure installation instructions, go to the Database Ingestion and Replication documentation on the Doc Portal and see Installing and configuring the stored procedure for Db2 for z/OS CDC. For information about creating the INFA_DATA table, see INFA_DATA table for Db2 for z/OS sources in a staging group.
The CDC Staging Task uses the following types of common storage:
•For Db2 for i, Db2 for LUW, Db2 for z/OS, Oracle, and Microsoft SQL Server sources, the CDC Staging Task writes data to cloud Iceberg storage. The cloud storage can be in Amazon S3, Google Cloud Storage, or Microsoft Azure Data Lake Storage Gen2 (with Shared Key Authentication). The staged data is retained in storage for 14 days by default, but you can adjust this retention period up to 365 days.
•For SAP HANA sources, the CDC Staging Task writes data to an SAP HANA or Oracle cache, as specified in the SAP HANA Database Ingestion connection properties. Each CDC Staging Task must have its own cache database and connection. The Secure Agent stages data to the cache database, and when full records are available, the Secure Agent writes the data to the target. By default, after the data is stored in the cache for 14 days, it's compacted but not deleted. You can adjust this period using the Log Clear connection property.
In the monitoring interfaces, you can filter for the asset type of "CDC Staging Task" and view the task's progress in capturing the source change data to cloud storage. In the Task Summary section of the job details, the Target reflects the SAP HANA source connection instead of the cache database type or an ultimate target type.
The following status interactions between the staging task and dependent tasks can occur:
•If the CDC Staging Task fails or is stopped or aborted, all dependent database ingestion and replication apply jobs stop. You should undeploy the stopped apply jobs. You can then undeploy the CDC Staging Task.
Note:
If you do not want the dependent apply jobs to stop, you can set the source custom property stopDependentsWhenGroupJobTerminated to false.
•If all jobs in the staging group fail, the CDC Staging Task continues running.
•If all jobs in a staging group are undeployed, the CDC Staging Task stops. You can then undeploy it.
•You can undeploy a CDC Staging Task that has the status of Aborted, Deployed, Failed, or Stopped from any of the monitoring interfaces. In the task row, select Undeploy from the Actions menu. Do not undeploy a CDC Staging Task if any job in the group is not undeployed.
Undeploying a CDC Staging Task cleans up temporary files in the Secure Agent Database Ingestion service area and in cloud cache. It also cleans up staging tables that are in Apache Iceberg format.
•If you undeploy and redeploy a CDC Staging Task, staging data to cloud storage starts over again, without preserving the previous contents in cloud storage.
INFA_DATA table for Db2 for z/OS sources in a staging group
If you expect the information that identifies selected source tables to the CDC staging task to be greater than 16 KB, you must install or manually create the INFA_DATA table. The table stores DBID, OBID and PSID information for each of the source tables selected in the tasks that are associated with the staging group.
If the INFA_DATA table does not exist and the computed length of these values is greater than 16 KB, the CDC staging task will fail.
You can create the INFA_DATA table by using the #INFATBL installation member. The installation member includes the following SQL statements for creating the table:
CREATE TABLE <schema>.INFA_DATA (INFA_CODE CHARACTER(2) NOT NULL ,INFA_GROUP_ID INTEGER NOT NULL ,INFA_SEQ INTEGER NOT NULL ,INFA_TS TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL ,INFA_DATA VARCHAR(4096) ,PRIMARY KEY(INFA_CODE,INFA_GROUP_ID,INFA_SEQ,INFA_TS) ) APPEND NO DATA CAPTURE CHANGES CCSID EBCDIC;
Note:
You can use a table name other than the default name of INFA_DATA if you prefer.
Grant the following privileges on the INFA_DATA table to the user specified in the connection properties for the primary source connection:
GRANT INSERT ON <schema>.INFA_DATA to <stagingtask_connecton_user>; GRANT DELETE ON <schema>.INFA_DATA to <stagingtask_connecton_user>; GRANT EXECUTE <schema>.INFA_DATA to <stagingtask_connecton_user>; COMMIT;
Where:
•<schema> is specified by the pwx.cdcreader.ZOS.InfaTableSchema custom property, or if this custom property is not specified, <schema> defaults the schema name of the Db2 stored procedure
•INFA_DATA is the default table name. If you specified another name when creating the table, use that name.
•The <schema>.<table> value in the grant statements must match the information in the CREATE statement in the #INFATBL installation member.