2.29 COLMATCH

Valid for

Extract, Replicat

Description

Use COLMATCH to map columns when source and target tables are different. The COLMATCH parameter enables mapping between databases with similarly structured tables but different names. COLMATCH specifies rules for default column mapping that apply to all columns that match the specified name.

COLMATCH is required when the source and target columns are different.You can also use the COLMAP option of the Replicat MAP parameter.

Syntax

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

NAMES target_column = source_column

Matches a target column to a source column.

• target_column is the name of the target column.

• = is the assignment operator.

• source_column is the name of the source column.

PREFIX prefix

Specifies a prefix to ignore.

SUFFIX suffix

Specifies a suffix to ignore.

RESET

Turns off any COLMATCH rules previously specified.

Global rules and table names

It may be that a source and target database are identical except for slightly different names, as shown in the following table.

Source Database	Target Database
ACCT Table CUST_CODE CUST_NAME CUST_ADDR PHONE	ACCOUNT Table CUSTOMER_CODE CUSTOMER_NAME CUSTOMER_ADDRESS PHONE
ORD Table CUST_CODE CUST_NAME ORDER_ID ORDER_AMT	ORDER Table CUSTOMER_CODE CUSTOMER_NAME ORDER_ID ORDER_AMT

To map the source database columns to the target, you could specify each individual column mapping, but an easier method is to specify global rules using COLMATCH as follows:

COLMATCH NAMES CUSTOMER_CODE = CUST_CODE
COLMATCH NAMES CUSTOMER_NAME = CUST_NAME
COLMATCH NAMES CUSTOMER_ADDRESS = CUST_ADDR;

Specifying matches this way enables all columns in ACCT to be mapped to ACCOUNT, and all columns in ORD to map to ORDER. When performing default mapping, Extract checks for any matches according to global rules (this enables mapping of CUST_CODE, CUST_NAME and CUST_ADDR). In addition, exact column name matches are checked as always (enabling mapping of ORDER_ID, ORDER_AMT and PHONE).

Global rules and suffixes

Another frequently encountered situation is:

Source table	Target table
CUST_CODE CUST_NAME ORDER_ID ORDER_AMT	CUST_CODE_K CUST_NAME_K ORDER_ID ORDER_AMT

In this case, a global rule can specify that the _K suffix appended to columns in the target table be ignored, as in: COLMATCH SUFFIX _K.

This also resolves the opposite situation:

Source table	Target table
CUST_CODE_K CUST_NAME_K ORDER_ID ORDER_AMT	CUST_CODE CUST_NAME ORDER_ID ORDER_AMT

The same principle can be applied to column prefixes: COLMATCH PREFIX P_

Source table	Target table
P_CUST_CODE P_CUST_NAME ORDER_ID ORDER_AMT	CUST_CODE CUST_NAME ORDER_ID ORDER_AMT

Global rules and map entries

Global rules can be turned off for subsequent map entries with COLMATCH RESET.