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 {NAMEStarget_column
=source_column
| PREFIXprefix
| SUFFIXsuffix
| 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 |
---|---|
CUST_CODE CUST_NAME CUST_ADDR PHONE |
CUSTOMER_CODE CUSTOMER_NAME CUSTOMER_ADDRESS PHONE |
CUST_CODE CUST_NAME ORDER_ID ORDER_AMT |
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
.