Configuring Global Column Mapping with COLMATCH

Use the COLMATCH parameter to create global rules for column mapping. With COLMATCH, you can map between similarly structured tables that have different column names for the same sets of data. COLMATCH provides a more convenient way to map columns of this type than does using table-level mapping with a COLMAP clause in individual TABLE or MAP statements.

Case-sensitivity is supported as follows:

For MySQL, SQL Server, and Teradata, if the database is case-sensitive, COLMATCH looks for an exact case and name match regardless of whether or not a name is specified in quotes.
For Oracle Database and DB2 databases, where names can be either case-sensitive or case-insensitive in the same database and double quotes are required to show case-sensitivity, COLMATCH requires an exact case and name match when a name is in quotes in the database.

Syntax

COLMATCH
{NAMES target_column = source_column |
PREFIX prefix |
SUFFIX suffix |
RESET}

Argument Description

Argument	Description
NAMES `target_column` = `source_column`	Maps based on column names. Put double quotes around the column name if it is case-sensitive and the database requires quotes to enforce case-sensitivity. For these database types, an unquoted column name is treated as case-insensitive by Oracle GoldenGate. For databases that support case-sensitivity without requiring quotes, specify the column name as it is stored in the database. If the `COLMATCH` is between columns in different database types, make certain the names reflect the appropriate case representation for each one. For example, the following specifies a case-sensitive target column name `"aBc`" in an Oracle Database and a case-sensitive source column name `aBc` in a case-sensitive SQL Server database. COLMATCH NAMES "aBc" = aBc
PREFIX `prefix` \| SUFFIX `suffix`	Ignores the specified name prefix or suffix. Put double quotes around the prefix or suffix if the database requires quotes to enforce case-sensitivity, for example "`P_"`. For those database types, an unquoted prefix or suffix is treated as case-insensitive. For databases that support case-sensitivity without requiring quotes, specify the prefix or suffix as it is stored in the database. For example, `P_` specifies a capital `P` prefix. The following example specifies a case-insensitive prefix to ignore. The target column name `P_ABC` is mapped to source column name `ABC`, and target column name `P_abc` is mapped to source column name `abc`. COLMATCH PREFIX p_ The following example specifies a case-sensitive `suffix` to ignore. The target column name `ABC_k` is mapped to the source column name `ABC`, and the target column name "`abc_k`" is mapped to the source column name "abc". SUFFIX "_k"
RESET	Turns off previously defined `COLMATCH` rules for subsequent `TABLE` or `MAP` statements.

NAMES target_column = source_column

Maps based on column names.

Put double quotes around the column name if it is case-sensitive and the database requires quotes to enforce case-sensitivity. For these database types, an unquoted column name is treated as case-insensitive by Oracle GoldenGate.

For databases that support case-sensitivity without requiring quotes, specify the column name as it is stored in the database.

If the COLMATCH is between columns in different database types, make certain the names reflect the appropriate case representation for each one. For example, the following specifies a case-sensitive target column name "aBc" in an Oracle Database and a case-sensitive source column name aBc in a case-sensitive SQL Server database.

COLMATCH NAMES "aBc" = aBc

PREFIX prefix | SUFFIX suffix

Ignores the specified name prefix or suffix.

Put double quotes around the prefix or suffix if the database requires quotes to enforce case-sensitivity, for example "P_". For those database types, an unquoted prefix or suffix is treated as case-insensitive.

For databases that support case-sensitivity without requiring quotes, specify the prefix or suffix as it is stored in the database. For example, P_ specifies a capital P prefix.

The following example specifies a case-insensitive prefix to ignore. The target column name P_ABC is mapped to source column name ABC, and target column name P_abc is mapped to source column name abc.

COLMATCH PREFIX p_

The following example specifies a case-sensitive suffix to ignore. The target column name ABC_k is mapped to the source column name ABC, and the target column name "abc_k" is mapped to the source column name "abc".

SUFFIX "_k"

RESET

Turns off previously defined COLMATCH rules for subsequent TABLE or MAP statements.

The following example illustrates when to use COLMATCH. The source and target tables are identical except for slightly different table and column names.The database is case-insensitive.

ACCT Table	ORD Table
CUST_CODE CUST_NAME CUST_ADDR PHONE S_REP S_REPCODE	CUST_CODE CUST_NAME ORDER_ID ORDER_AMT S_REP S_REPCODE

ACCOUNT Table	ORDER Table
CUSTOMER_CODE CUSTOMER_NAME CUSTOMER_ADDRESS PHONE REP REPCODE	CUSTOMER_CODE CUSTOMER_NAME ORDER_ID ORDER_AMT REP REPCODE

To map the source columns to the target columns in this example, as well as to handle subsequent maps for other tables, the syntax is:

COLMATCH NAMES CUSTOMER_CODE = CUST_CODE
COLMATCH NAMES CUSTOMER_NAME = CUST_NAME
COLMATCH NAMES CUSTOMER_ADDRESS = CUST_ADDR
COLMATCH PREFIX S_
MAP SALES.ACCT, TARGET SALES.ACCOUNT, COLMAP (USEDEFAULTS);
MAP SALE.ORD, TARGET SALES.ORDER, COLMAP (USEDEFAULTS);
COLMATCH RESET
MAP SALES.REG, TARGET SALE.REG;
MAP SALES.PRICE, TARGET SALES.PRICE;

Based on the rules in the example, the following occurs:

Data is mapped from the CUST_CODE columns in the source ACCT and ORD tables to the CUSTOMER_CODE columns in the target ACCOUNT and ORDER tables.
The S_ prefix will be ignored.
Columns with the same names, such as the PHONE and ORDER_AMT columns, are automatically mapped by means of USEDEFAULTS without requiring explicit rules.
The previous global column mapping is turned off for the tables REG and PRICE. Source and target columns in those tables are automatically mapped because all of the names are identical.