Configuring Table-level Column Mapping with COLMAP

Use the COLMAP option of the MAP and TABLE parameters to:

■ map individual source columns to target columns that have different names.

■ specify default column mapping when an explicit column mapping is not needed.

■ Provide instructions for selecting, mapping, translating, and moving data from a source column into a target column.

12.6.2.1 Specifying the Columns to be Mapped in the COLMAP Clause

The COLMAP syntax is the following:

COLMAP ([USEDEFAULTS, ] target_column = source_expression)

In this syntax, target_column is the name of the target column, and source_

expression can be any of the following, allowing you to map the source column by name, so as to pass the source value exactly as recorded in the trail, or to transform the data before passing it to the target column:

■ The name of a source column, such as ORD_DATE.

■ Numeric constant, such as 123.

■ String constant enclosed within single quotes, such as ’ABCD’.

■ An expression using an Oracle GoldenGate column-conversion function. Within a COLMAP statement, you can employ any of the Oracle GoldenGate

column-conversion functions to transform data for the mapped columns, for example:

@STREXT (COL1, 1, 3)

If the column mapping involves case-sensitive columns from different database types, specify each column as it is stored in the database.

■ If the database requires double quotes to enforce case-sensitivity, specify the case-sensitive column name within double quotes.

■ If the database is case-sensitive without requiring double quotes, specify the column name as it is stored in the database.

The following shows a mapping between a target column in an Oracle database and a source column in a case-sensitive SQL Server database.

COLMAP ("ColA" = ColA)

See Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for more information about specifying names to Oracle GoldenGate.

See Section 12.5, "Globalization Considerations when Mapping Data" for globalization considerations when mapping source and target columns in databases that have different character sets and locales.

Avoid using COLMAP to map a value to a key column (which causes the operation to become a primary key update), The WHERE clause that Oracle GoldenGate uses to locate the target row will not use the correct before image of the key column. Instead, it will use the after image. This will cause errors if you are using any functions based on that key column, such as a SQLEXEC statement, as shown in the following example.

■ Source table TCUSTMER1

■ Target table TCUSTMER2

■ Column layout, both tables:

Column 1 = Cust Column 2 = Name Column 3 = City Column 4 = State

■ Primary key consiste of the Cust, Name, and City columns.

■ SQLEXEC query in the MAP statement:

SQLEXEC (id mytest, query ’select city from TCUSTMER1 WHERE state = 'CA'’, noparams, ERROR RAISE)

■ COLMAP statement in the MAP statement:

COLMAP ( usedefaults, city = mytest.city ) This is the sequence of events that causes the error:

1. INSERT statement inserts the following:

INSERT into TCUSTMER1 values (Cust = '1234', Name = 'Ace', City = 'SF', State = 'CA');

Commit;

The SQLEXEC query returns the correct value, and the target table also has a value of SF for City and CA for State.

mytest.city = 'SF’

2. UPDATE statement changes City from SF to LA on the source. This does not succeed on the target. The SQLEXEC query looks up the City column in TCUSTMER1 and returns a value of LA. Based on the COLMAP clause, the before and after versions of City both are now LA. This generates SQL error 1403 when executing the target WHERE clause, because a value of LA does not exist for the City column in the target table.

12.6.2.2 Using USEDEFAULTS to Enable Default Column Mapping

You can use the USEDEFAULTS option of COLMAP to specify automatic default column mapping for any corresponding source and target columns that have identical names.

USEDEFAULTS can save you time by eliminating the need to map every target column explicitly.

Default mapping causes Oracle GoldenGate to map those columns and, if required, translate the data types based on the data-definitions file (see Section 12.6.2.3,

"Determining Whether COLMAP Requires a Data-definitions File"). Do not specify default mapping for columns that are mapped already with an explicit mapping statement.

The following example of a column mapping illustrates the use of both default and explicit column mapping for a source table ACCTBL and a target table ACCTTAB. Most columns are the same in both tables, except for the following differences:

■ The source table has a CUST_NAME column, whereas the target table has a NAME column.

■ A ten-digit PHONE_NO column in the source table corresponds to separate AREA_

CODE, PHONE_PREFIX, and PHONE_NUMBER columns in the target table.

■ Separate YY, MM, and DD columns in the source table correspond to a single TRANSACTION_DATE column in the target table.

To address those differences, USEDEFAULTS is used to map the similar columns

automatically, while explicit mapping and conversion functions are used for dissimilar columns.

See Section 12.6.4, "Understanding Default Column Mapping" for more information about the rules followed by Oracle GoldenGate for default column mapping.

12.6.2.3 Determining Whether COLMAP Requires a Data-definitions File

When using COLMAP, you might need to create a data-definitions file. To make this determination, you must consider whether the source and target column structures are identical, as defined by Oracle GoldenGate.

For source and target structures to be identical, they must:

■ be of the same database type, that is, all Oracle.

■ have the same character set and locale.

Table 12–1 Sample Column Mapping

Parameter statement Description MAP SALES.ACCTBL,

TARGET SALES.ACCTTAB,

Maps the source table ACCTBL to the target table ACCTTAB.

COLMAP( Begins the COLMAP statement.

USEDEFAULTS, Maps source columns as-is when the target column names are identical.

NAME = CUST_NAME, Maps the source column CUST_NAME to the target column NAME.

TRANSACTION_DATE =

@DATE (’YYYY-MM-DD’, ’YY’, YEAR, ’MM’, MONTH, ’DD’, DAY),

Converts the transaction date from the source date columns to the target column TRANSACTION_DATE by using the @DATE column conversion function.

AREA_CODE =

Converts the source column PHONE_NO into the separate target columns of AREA_CODE, PHONE_PREFIX, and PHONE_

NUMBER by using the @STREXT column conversion function.

■ contain the same number of columns.

■ have identical column names (including case, white spaces, and quotes if applicable).

■ have identical data types.

■ have identical column lengths.

■ have the same column length semantics for character columns (bytes versus characters).

■ define all of the columns in the same order.

When using COLMAP for source and target tables that are not identical in structure, you must:

■ generate data definitions for the source tables, the target tables, or both, depending on the Oracle GoldenGate configuration and the databases that are being used.

■ transfer the definitions file to the system where they will be used.

■ use the SOURCEDEFS parameter to identify the definitions file for Replicat on a target system or use the TARGETDEFS parameter to identify the definitions file for Extract or a data pump on a source system or intermediary system.

When using COLMAP for source and target tables that are identical in structure, and you are only using COLMAP for other functions such as conversion, a source definitions file is not needed. When a definitions file is not being used, you must use the

ASSUMETARGETDEFS parameter instead. See Reference for Oracle GoldenGate for Windows and UNIX for more information.

See Chapter 13, "Associating Replicated Data with Metadata" for more information about using a definitions file.