Using GoldenGate Tokens with the COLMAP clause

We can use the @TOKEN function to extract data which is stored in what is called the user token area of the GoldenGate trail file record header.

We can populate this Token data from information stored in the header portion of trail records using the GGHEADER option of the GETENV function or by capturing information about the GoldenGate environment obtained via the GGENVIRONMENT option of GETENV function. We can also populate the tokens with data obtained from some database queries or functions.

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

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.

In the example below, the source table has two columns (SAL and COMM) and the target table has some other columns in addition to these two columns which we will populate using Tokens and the @DATENOW function which will populate the column with the current timestamp.

Source table

SQL> desc mytest
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAL                                                NUMBER(10)
 COMM                                               NUMBER(10)

Target table

SQL> desc mytest
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAL                                                NUMBER(10)
 COMM                                               NUMBER(10)
 HOSTNAME                                           VARCHAR2(20)
 OSUSER                                             VARCHAR2(10)
 DBNAME                                             VARCHAR2(10)
 TRAN_DATE                                          DATE

The column hostname is populated by the token TK_HOST which obtains the hostname information via the @GETENV function. Similarly the database name is obtained via the DBENVIRONMENT option of the GETENV function using the token TK_DBNAME

Finally we populate the date column TRAN_DATE using the @DATENOW function.

These are the contents of the Extract parameter file.

EXTRACT myext
USERID idit_prd, PASSWORD idit_prd
RMTHOST idb02, MGRPORT 7809
RMTTRAIL ./dirdat/yy
TABLE idit_prd.mytest, TOKENS ( TK_HOST = @GETENV("GGENVIRONMENT" , "HOSTNAME"), TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"), TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ));

These are the contents of the Replicat parameter file

REPLICAT myrep
ASSUMETARGETDEFS
USERID idit_prd,PASSWORD idit_prd
MAP idit_prd.mytest, TARGET idit_prd.mytest,
COLMAP (USEDEFAULTS,
hostname = @token ("tk_host"),
osuser= @token ("tk_osuser"),
dbname= @token ("tk_dbname"),
tran_date = @DATENOW());

Let us now test this.

On the source database we insert a record which populates the two columns SAL and COMM.

SQL> insert into mytest
  2  values
  3   (1000,5000);

1 row created.

SQL> commit;

SQL> select * from mytest;

       SAL       COMM HOSTNAME             OSUSER     DBNAME     TRAN_DATE
---------- ---------- -------------------- ---------- ---------- ---------
      1000       5000 db01             oracle     GGDB1      24-MAR-11