How to Use GoldenGate Token with COLMAP?

Tokens are used to capture and store the environment variable values in the header of the GoldenGate trail record file. The trail file header contains a lot of information about the physical environment that produced the trail file and trail file contents. We can use this information to map token data to a target column by using @TOKEN function in the source expression of a COLMAP clause.

Here is a list of predefined GoldenGate tokens.

GoldenGate Token with COLMAP

GGHEADER option of the GETENV function or the GGENVIRONMENT option of GETENV function is used to populate the Token data from information stored in the header portion of trail records. We can also populate the tokens with data obtained from a database queries or a function.

There is a limit on the total space allocated for token keys and values stored in the trail. The token area in the record header permits up to 2,000 bytes of data. Token names, the length of the data and the data itself must fit into that space.

Use the TOKENS option of the TABLE parameter in the extract parameter file to define the token. Once the trail file is shipped to the target side and read by the replicat, the token is mapped to a column in the target table.

We can then use this information in the tokens to populate columns in target tables by using the @TOKEN column conversion function in the COLMAP clause in a replicat parameter file. A practical purpose would be to can use this information for auditing.

Let’s look at an example.

In source there is a table EMP which has 5 columns.

DESC emp
Name Null? Type
---------------------------------- -------- ---------
EMP_NO NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2(128)
EMP_EMAIL NOT NULL VARCHAR2(400)
EMP_MOBILE NOT NULL VARCHAR2(16)

In target side there is EMP table with 14 columns.

DESC emp
Name Null? Type
---------------------------------- -------- ---------
EMP_NO NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2(128)
EMP_EMAIL NOT NULL VARCHAR2(400)
EMP_MOBILE NOT NULL VARCHAR2(16)
HOST VARCHAR2(128)
GG_GROUP VARCHAR2(128)
OSUSER VARCHAR2(128)
DBNAME VARCHAR2(128)
BA_IND VARCHAR2(128)
COMMIT_TS VARCHAR2(128)
POS VARCHAR2(128)
RBA VARCHAR2(128)
TABLENAME VARCHAR2(128)
OPTYPE VARCHAR2(128)

Now I want to replicat the records from source to target table.

Contents of the extract parameter file are as below:

extract ext1
USERID gguser, PASSWORD gguser
RMTHOST dev, MGRPORT 7809
rmttask replicat, group rep1
TABLE gguser.EMP,
TOKENS ( TK_HOST = @GETENV("GGENVIRONMENT" , "HOSTNAME"),
TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),
TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ),
TK_GROUP [email protected] (“GGENVIRONMENT”, “GROUPNAME”),
TK_COMMIT_TS [email protected] (“GGHEADER”, “COMMITTIMESTAMP”),
TK_POS [email protected] (“GGHEADER”, “LOGPOSITION”),
TK_RBA [email protected] (“GGHEADER”, “LOGRBA”),
TK_TABLE [email protected] (“GGHEADER”, “TABLENAME”),
TK_OPTYPE [email protected] (“GGHEADER”, “OPTYPE”),
TK_BA [email protected] (“GGHEADER”, “BEFOREAFTERINDICATOR”));

Contents of the replicat parameter file:

replicat rep1
SETENV (ORACLE_SID=orcl)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID gguser, PASSWORD gguser
DISCARDFILE ./dirrpt/rep1.dsc, PURGE
sourceDEFS ./dirsql/ref.sql
MAP gguser.EMP, target gguser.EMP, &
COLMAP (USEDEFAULTS,
HOST = @TOKEN ('TK_HOST'),
GG_GROUP = @TOKEN ('TK_GROUP'),
OSUSER = @TOKEN ('TK_OSUSER'),
DBNAME = @TOKEN ('TK_DBNAME'),
BA_IND = @TOKEN ('TK_BA'),
COMMIT_TS = @TOKEN ('TK_COMMIT_TS'),
POS = @TOKEN ('TK_POS'),
RBA = @TOKEN ('TK_RBA'),
TABLENAME = @TOKEN ('TK_TABLE'),
OPTYPE = @TOKEN ('TK_OPTYPE'));

When we insert a record in source, it will be replicated in target.

Leave a Reply

Your email address will not be published.