GoldenGate COLMAP: Map to Different Table Name and Column Name

 

In this note, GoldenGate is used to replicate data from a source table named GG_TEST_TBL1 to a target table named GG_TEST_TABLE1. A column in the source table named PASS is also being replicated to a column in the target table named PASSWORD.

Prepare Environment

* See GoldenGate Tutorial: Simplest to setup environment.

Prepare Database

Create Source Table

sqlplus gg_src/password
 
-- Create src table
CREATE TABLE GG_SRC.GG_TEST_TBL1
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20),
PASS VARCHAR2(512),
CONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY
(
ID
)
ENABLE
);

Create Target Table

sqlplus gg_tgt/password
 
-- Create target table
CREATE TABLE GG_TGT.GG_TEST_TABLE1
(
ID VARCHAR2(20) NOT NULL,
NAME VARCHAR2(20),
PASSWORD VARCHAR2(512),
CONSTRAINT GG_TEST_TBL1_PK PRIMARY KEY
(
ID
)
ENABLE
);

Generate Source Definitions File

* Source definitions file is needed since source and target table definitions are different
* Create defgen parameter file from GGSCI: EDIT PARAMS gg_src_tbl1

DEFSFILE dirdef/gg_src_tbl1.def
USERID gg_src, PASSWORD password
TABLE gg_src.GG_TEST_TBL1;

* Generate source definitions file with the defgen command:
– From GG_HOME directory:

defgen paramfile dirprm/gg_src_tbl1.prm

For Source User: gg_src

Add Trandata

-- Login source schema
DBLOGIN USERID GG_SRC, PASSWORD password
-- Enable trandata for GG_TEST_TBL1
ADD TRANDATA gg_src.GG_TEST_TBL1

Prepare Extract Group

* Create extract group

ADD EXTRACT e_t1, TRANLOG, BEGIN now

* Create extract trail

ADD EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1, EXTRACT e_t1, MEGABYTES 20

* Edit extract parameters: EDIT PARAMS e_t1

-- Oracle instance
SETENV (ORACLE_HOME = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server")
SETENV (ORACLE_SID = "XE")
 
-- Specify extract group
EXTRACT e_t1
 
-- Used to login db
USERID gg_src, PASSWORD password
 
-- Specify extract trail file
EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1
 
-- Specify tables to extract
TABLE gg_src.GG_TEST_TBL1;

* Start Extract

-- Start manager process if not already done
start mgr
 
-- Start extract
start extract e_t1
 
-- View extract report
view report e_t1

For Target User: gg_tgt

Prepare Replicat

* Create REPLICAT group

ADD REPLICAT r_t1, EXTTRAIL /u01/Oracle/GoldenGate/gg1121/dirdat/t1, NODBCHECKPOINT, BEGIN now

* Edit REPLICAT parameters: EDIT PARAMS r_t1

-- Oracle instance
SETENV (ORACLE_HOME = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server")
SETENV (ORACLE_SID = "XE")
 
-- Replicat group
REPLICAT r_t1
 
-- Source definitions file
SOURCEDEFS ./dirdef/gg_src_tbl1.def
 
-- Login DB
USERID gg_tgt, PASSWORD password
 
-- Map tables AND columns
-- USEDEFAULTS: exact same names
-- PASSWORD = PASS: target column name PASSWORD should be mapped to source column named PASS
MAP gg_src.GG_TEST_TBL1, TARGET gg_tgt.GG_TEST_TABLE1, &
COLMAP (USEDEFAULTS, &
PASSWORD = PASS);

*Start REPLICAT

start replicat r_t1
view report r_t1

Test

Insert Data to Source Table

INSERT INTO GG_SRC.GG_TEST_TBL1 VALUES ('1','One','Pass');
commit;

Check Data in Target Table

SELECT * FROM GG_TGT.GG_TEST_TABLE1;
 
ID                   NAME                 PASSWORD
-------------------- -------------------- --------------------
1                    One                  Pass

References

* GoldenGate Tutorial: Simplest

This entry was posted in goldengate and tagged , . Bookmark the permalink.

2 Responses to GoldenGate COLMAP: Map to Different Table Name and Column Name

Leave a Reply

Your email address will not be published. Required fields are marked *


*

This site uses Akismet to reduce spam. Learn how your comment data is processed.