REPLICATE - connect_cdc_sqdata - Latest

This command expands on AUTOMAP by automatically performing the APPLY (write) to the Target datastore. REPLICATE can significantly reduce the size and complexity of the Engine script and also provides much higher performance with less CPU and other overhead as compared to explicit field/column mapping.

• Like Automap, Replicate eliminates the need to map individual fields and columns when the Source and Target datastores are identical or nearly identical with some limitations:
  • Source and Target datastore types must be compatible including Relational > Relational (same or combinations of DB2, Oracle, SQL Server), IMS > IMS, VSAM > VSAM
  • Field or column names do not have to be identical but the sequence of field or columns must match
  • Field or column types must be "compatible"

• Replicate allows manual mapping of individual fields/columns when required, simply by mapping them manually prior to the Replicate command statement.
• Replicate eliminates the need for Target DESCRIPTIONS:
  • When the Source and Target Datastores are the same Type (DB2 > DB2, Oracle > Oracle, etc). Target "schema" can be changed using DATASTORE FORCE QUALIFIER parameter
  • When the Target is an AVRO or JSON formatted datastore, typically Kafka or HDFS (Hadoop).
  • For JSON or AVRO formatted datastores, it also generates and populates the Target header metadata columns without the need for Target DESCRIPTIONS.

• Replicate allows specification of a Target DESCRIPTION containing "customized" field/column names as long as they are listed in the same sequence as the Source DESCRIPTION.
  • Precisely recommends using the same field/column names and style as the Source DESCRIPTION.
  • The Apply Engine will automatically change the style to lower snake_case when generating AVRO formatted datastores.

• Replicate requires the specification of key fields or columns for Relational, IMS and VSAM Targets:
  • Target Datastore Catalog or in the case of IMS the DBD.
  • DATASTORE KEY IS parameter
  • DESCRIPTION KEY IS parameter

REPLICATE (<target_datastore> [, <source_description> | <target_description>])

Only the target_datastore is required if the Source and Target table and column names are identical. Table (prefix, schema, owner) defaults to qualifier in Source DESCRIPTION unless FORCE QUALIFIER parameter is specified.

Example 1

BEGIN GROUP HR_DB2
DESCRIPTION DB2SQL DD:DB2DDL(EMP)  AS I_EMP;
DESCRIPTION DB2SQL DD:DB2DDL(DEPT) AS I_DEPT;
END GROUP;      

BEGIN GROUP HR_ORA
DESCRIPTION ORASQL DD:ORADDL(EMP)  AS T_EMP;
DESCRIPTION ORASQL DD:ORADDL(DEPT) AS T_DEPT;
END GROUP;          

DATASTORE RDBMS
         OF RELATIONAL
         AS HR_DB
         DESCRIBED BY GROUP HR_ORA;
...
REPLICATE (HR_DB)

Example 2

BEGIN GROUP DB2_SOURCE
DESCRIPTION DB2SQL ./DB2DDL(EMP)  AS I_EMP;
DESCRIPTION DB2SQL ./DB2DDL(DEPT) AS I_DEPT;
END GROUP;      

BEGIN GROUP ORA_TARGET
DESCRIPTION ORASQL ./ORADDL(USER) AS T_USER;
DESCRIPTION ORASQL ./ORADDL(ORG)  AS T_ORG;
END GROUP;          

DATASTORE RDBMS
         OF RELATIONAL
         AS TARGET
         DESCRIBED BY GROUP ORA_TARGET;
...

CASE DB2TBLNAME(CDCIN)
    WHEN 'EMP' REPLICATE (TARGET, T_USER)
    WHEN 'DEPT' REPLICATE (TARGET, T_ORG)

Example 3

BEGIN GROUP SOURCE
DESCRIPTION DB2SQL ./DB2DDL(EMP)  AS I_EMP;
DESCRIPTION DB2SQL ./DB2DDL(DEPT) AS I_DEPT;
END GROUP;      

DATASTORE kafka:///*.topic
         OF JSON
         AS TARGET
         DESCRIBED BY GROUP SOURCE;
REPLICATE (TARGET)