|
AUTOMAP |
Scroll |
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. Note, it is possible to "override" the automatic mapping for individual Target fields or columns by simply mapping them manually prior to the APPLY command statement.
Automap eliminates the need to map individual fields and columns when the Source and Target datastores are identical or nearly identical with some limitations:
1.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.
2.Field or column names do not have to be identical but the sequence of fields or columns must match
3.Target description can specify by example the "style" of field/column names such as snake_case, camelCase, (Proper) CamelCase and even kebab-case.
4.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.
5.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:
1.RDBMS Target Database Catalog
2.IMS DBD's
3.DESCRIPTION KEY IS parameter
4.DATASTORE KEY IS parameter
Syntax
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.
Keyword and Parameter Descriptions
<target_datastore> Specifies the alias of the Datastore which will be the Target of the replication.
<source_description> | <target_description> Specifies the alias of the description to be used for the Target Datasstore.
Example 1
Apply changes to the EMP and DEPT tables in a DB2 database to similar tables in an Oracle database that require only a bit of transformation logic in mapping Procs .
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
Replicate changes to the EMP and DEPT tables in a DB2 database to identically named tables in an Oracle database.
PROCESS INTO TARGET
SELECT
{
AUTOMAP (TARGET) APPLY (TARGET) }
}
FROM CDCIN;
Note, this example would be better handled by the REPLICATE Command than the AUTOMAP, APPLY sequence since no intervention was required.
Example 3
Replicate changes to the EMP and DEPT tables in a DB2 database to the USER and ORG tables in an Oracle database. Because the table names are different, a case statement is required to identify the Source table before the REPLICATE command can be used. While this does require a line of instructions for each table to be replicated, it still eliminates the need for column level mapping.
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;
