AUTOMAP - connect_cdc_sqdata - Latest

Performs the automatic mapping of source to target fields/columns. While It does not automatically Apply (write) to the Target Datastore it can significantly reducing the size and complexity of the Engine script. The AUTOMAP command also provides much higher performance with less CPU and other overhead as compared to explicit field/column mapping.

Automap 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 either compatible, including Relational > Relational (same or combinations of DB2, Oracle, SQL Server), IMS > IMS, VSAM > VSAM OR the target must be a JSON formatted target such as Kafka.
• Field or column names do not have to be identical but the sequence of fields or columns must match
• Target description can specify by example the "style" of field/column names such as snake_case, camelCase, (Proper) CamelCase and even kebab-case.
• Additional fields or columns may be present in the Target datastore Description but must come after all the fields present in the Source Description. It also does not generate header metadata columns when the target is a JSON or AVRO format datastore type.
• Field or column types must be "compatible"

Automap allows manual mapping of individual fields/columns when required, simply by mapping them manually prior to the Automap command statement.

Automap requires the specification of key fields or columns for all targets. They may be supplied using different methods:

• RDBMS Target Database Catalog
• IMS DBD's
• DESCRIPTION KEY IS parameter
• DATASTORE KEY IS parameter

AUTOMAP (<target_datastore> | <source_description>, <target_description>)

Only the target_datastore is required if the Source and Target table 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 TARGET
         DESCRIBED BY GROUP HR_ORA;
...

PROCESS INTO TARGET
SELECT
{
   CASE DB2TBLNAME(CDCIN)
      WHEN 'EMPLOYEE'   { CALLPROC(P_EMP) AUTOMAP (I_EMP, T_EMP) APPLY (TARGET, T_EMP) }
      WHEN 'DEPARTMENT' { CALLPROC(P_DEPT) AUTOMAP (I_DEPT, T_DEPT) APPLY (TARGET, T_DEPT) }
}
FROM CDCIN;

Example 2

PROCESS INTO TARGET
SELECT
{
  AUTOMAP (TARGET) APPLY (TARGET) }
}
FROM CDCIN;

Example 3

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

BEGIN GROUP HR_ORA
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 HR_ORA;
...

PROCESS INTO TARGET
SELECT
{
   CASE DB2TBLNAME(CDCIN)
      WHEN 'EMP'  { AUTOMAP (I_EMP, T_USER) APPLY (TARGET, T_USER) }
      WHEN 'DEPT' { AUTOMAP (I_DEPT, T_ORG) APPLY (TARGET, T_ORG) }
}
FROM CDCIN;