11 Mapping and Manipulating Data

To Use the \uFFFF Unicode Escape Sequence

• The \uFFFF Unicode escape sequence must begin with a lowercase u, followed by exactly four hexadecimal digits.

• Supported ranges are as follows:

  • 0 to 9 (U+0030 to U+0039)

  • A to F (U+0041 to U+0046)

  • a to f (U+0061 to U+0066)

\u20ac is the Unicode escape sequence for the Euro currency sign.

To Use the \377 Octal Escape Sequence

• Must contain exactly three octal digits.

• Supported ranges:

  • Range for first digit is 0 to 3 (U+0030 to U+0033)

  • Range for second and third digits is 0 to 7 (U+0030 to U+0037)

    \200 is the octal escape sequence for the Euro currency sign on Microsoft Windows

To Use the \xFF Hexadecimal Escape Eequence

• Must begin with a lowercase x followed by exactly two hexadecimal digits.

• Supported ranges:

  • 0 to 9 (U+0030 to U+0039)

  • A to F (U+0041 to U+0046)

  • a to f (U+0061 to U+0066)

\x80 is the hexadecimal escape sequence for the Euro currency sign on Microsoft Windows 1252 Latin1 code page.

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.

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.

See Specifying Object Names in Oracle GoldenGate Input for more information about case-sensitivity support.

Syntax

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

NAMES target_column = source_column

COLMATCH NAMES "aBc" = aBc

PREFIX prefix | SUFFIX suffix

COLMATCH PREFIX p_

SUFFIX "_k"

RESET

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.

CUST_CODE
CUST_NAME
CUST_ADDR
PHONE
S_REP
S_REPCODE

CUST_CODE
CUST_NAME
ORDER_ID
ORDER_AMT
S_REP
S_REPCODE

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. See Understanding Default Column Mapping for more information.

• 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.

Use a FILTER clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-conversion functions.

The syntax for FILTER in a TABLE statement is as follows:

TABLE source_table,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, filter_clause);

The sytax for FILTER in a MAP statement is as follows and includes an error-handling option.

MAP source_table, TARGET target_table,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR error_number]
, filter_clause);

Valid FILTER clause elements are the following:

• An Oracle GoldenGate column-conversion function. These functions are built into Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values, and so forth. See Testing and Transforming Data for more information about Oracle GoldenGate conversion functions.

• Numbers

• Columns that contain numbers

• Functions that return numbers

• Arithmetic operators:

  • + (plus)

  • - (minus)

  • * (multiply)

  • / (divide)

  • \ (remainder)

• Comparison operators:

  • > (greater than)

  • >= (greater than or equal)

  • < (less than)

  • <= (less than or equal)

  • = (equal)

  • <> (not equal)

  • Results derived from comparisons can be zero (indicating FALSE) or non-zero (indicating TRUE).

• Parentheses (for grouping results in the expression)

• Conjunction operators: AND, OR

Use the following FILTER options to specify which SQL operations a filter clause affects. Any of these options can be combined.

ON INSERT | ON UPDATE | ON DELETE  IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE

Use the RAISEERROR option of FILTER in the MAP parameter to generate a user-defined error when the filter fails. This option is useful when you need to trigger an event in response to the failure.

You can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example, the following splits the replication workload into two ranges (between two Replicat processes or two threads of a coordinated Replicat) based on the ID column of the source acct table.

REPERROR (9999, EXCEPTION)

MAP OWNER.SRCTAB,
TARGET OWNER.TARGTAB,

SQLEXEC (ID CHECK, ON UPDATE,
QUERY ' SELECT COUNT FROM TARGTAB '
'WHERE PKCOL = :P1 ',
PARAMS (P1 = PKCOL)),

FILTER (BALANCE > 15000),

FILTER (ON UPDATE, @BEFORE (COUNT) = CHECK.COUNT)

;

MAP OWNER.SRCTAB,
TARGET OWNER.TARGEXC,
EXCEPTIONSONLY,
COLMAP (USEDEFAULTS,
ERRTYPE = 'UPDATE FILTER FAILED');

Use any of the elements in Table 11-7 in a WHERE clause to select or exclude rows (or both) based on a conditional statement. Each WHERE clause must be enclosed within parentheses. Literals must be enclosed within single quotes.

PRODUCT_AMT

-123, 5500.123

'AUTO', 'Ca'

AND, OR

Oracle GoldenGate does not support FILTER for columns that have a multi-byte character set or a character set that is incompatible with the character set of the local operating system.

Arithmetic operators and floating-point data types are not supported by WHERE. To use more complex selection conditions, use a FILTER clause or a user exit routine. See Using User Exits to Extend Oracle GoldenGate Capabilities for more information.

The syntax for WHERE is identical in the TABLE and MAP statements:

TABLE table, WHERE (clause);

MAP source_table, TARGET target_table, WHERE (clause);

Use the appropriate function for the type of column that is being manipulated or evaluated. For example, numeric functions can be used only to compare numeric values. To compare character values, use one of the Oracle GoldenGate character-comparison functions. LOB columns cannot be used in conversion functions.

This statement would fail because it uses @IF, which is a numerical function, to compare string values.

@IF (SR_AREA = 'Help Desk', 'TRUE', 'FALSE')

The following statement would succeed because it compares a numeric value.

@IF (SR_AREA = 20, 'TRUE', 'FALSE')

See Manipulating Numbers and Character Strings for more information.

Use the @DATE, @DATEDIF, and @DATENOW functions to retrieve dates and times, perform computations on them, and convert them.

This example computes the time that an order is filled

To use tokens, you define the token name and associate it with data. The data can be any valid character data or values retrieved from Oracle GoldenGate column-conversion functions.

The token area in the record header permits up to 16,000 bytes of data. Token names, the length of the data, and the data itself must fit into that space.

To define a token, use the TOKENS option of the TABLE parameter in the Extract parameter file.

Syntax

TABLE table_spec, TOKENS (token_name = token_data [, ...]);

Where:

• table_spec is the name of the source table. A container or catalog name, if applicable, and an owner name must precede the table name.

• token_name is a name of your choice for the token. It can be any number of alphanumeric characters and is not case-sensitive.

• token_data is a character string of up to 2000 bytes. The data can be either a string that is enclosed within single quotes or the result of an Oracle GoldenGate column-conversion function. The character set of token data is not converted. The token must be in the character set of the source database for Extract and in the character set of the target database for Replicat. In the trail file, user tokens are stored in UTF-8.

TABLE ora.oratest, TOKENS (
TK-OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'),
TK-GROUP = @GETENV ('GGENVIRONMENT' , 'GROUPNAME')
TK-HOST =  @GETENV('GGENVIRONMENT' , 'HOSTNAME'));

As shown in this example, the Oracle GoldenGate @GETENV function is an effective way to populate token data. This function provides several options for capturing environment information that can be mapped to tokens and then used on the target system for column mapping.

To map token data to a target table, use the @TOKEN column-conversion function in the source expression of a COLMAP clause in a Replicat MAP statement. The @TOKEN function provides the name of the token to map. The COLMAP syntax with @TOKEN is:

Syntax

COLMAP (target_column = @TOKEN ('token_name'))

The following MAP statement maps target columns host, gg_group, and so forth to tokens tk-host, tk-group, and so forth. Note that the arguments must be enclosed within single quotes.

tk-host

:sysA

tk-group

:extora

tk-osuser

:jad

tk-domain

:admin

tk-ba_ind

:B

tk-commit_ts

:2011-01-24 17:08:59.000000

tk-pos

:3604496

tk-rba

:4058

tk-table

:oratest

tk-optype

:insert