GoldenGate: Replicate data from SQLServer to TERADATA – Part 2

This steps should still be performed in SQLserver Host:

The pump process configuration is very simple, its only function is to transport the trail files to destination.

ADD extract P_MSQL, exttrailsource ./dirdat/tr

C:\goldengate> edit param P_MSQL

extract P_MSQL
SOURCEDB db0sql1, USERID ggate, PASSWORD ??????
CACHEMGR CACHESIZE 2GB
rmthost teradata1.net, mgrport 8809
rmttrail ./dirdat/td

--TABLE MAP
TABLE dbo.DLOG_ERRORS;
TABLE dbo.SAC_DATA;
TABLE dbo.SAC_LIST;
TABLE dbo.SAC_TITLE;

Still in the SQLserver Host, is need to create a definition file, wich will be used in gg-teradata.
First, create a “tables.def” file that should contain a dblogin and tables that will be replicated.

defsfile tables_sqlserver.sql purge 
SOURCEDB db0sql1, 
USERID ggate, PASSWORD ?????? 
TABLE dbo.DLOG_ERRORS; 
TABLE dbo.SAC_DATA; 
TABLE dbo.SAC_LIST; 
TABLE dbo.SAC_TITLE;

C:\goldengate> defgen.exe paramfile tables.def

This process result a new file (tables_sqlserver.sql), copy this file to destination (gg-teradata).

This steps must be perfomed in GG-Teradata Host:

To configure the goldengate teradata you must install TERDATA ODBC Driver, to allow the goldengate access the teradata base, you can download ODBC driver here.

After install ODBC Driver is  need to adjust the odbc.ini, which should contain teradata connection information.

Here is the example of the odbc.ini file.

[teradata_dev]
Driver=/opt/teradata/client/ODBC/lib/tdata.so
Description=Teradata base
DBCName=teradata1.net
LastUser=
Username=GG_TERA
Password=????????
Database=
DefaultDatabase=dbs
LoginTimeout=3600
SessionMode=ANSI
DateTimeFormat=AAA
NoScan=Yes
characterSet=UTF16

After configuring odbc.ini, add an environment variable in S.O, making the file visible to goldengate.

export ODBCINI=$GGATE_HOME/.odbc.ini

*You can add this “export” on oracle user profile, if its no set, goldengate will fail.

Now, let’s configure Replicate process:

ADD REPLICAT R_MSQL, EXTTRAIL ./dirdat/td NODBCHECKPOINT

teradata1.net:/oracle/ggate> edit param R_MSQL

replicat R_MSQL

--This information comes from odbc.ini file
targetdb teradata_dev 

SOURCECHARSET PASSTHRU

discardfile ./dirrpt/R_MSQL.dsc, MEGABYTES 1024, purge
sourcedefs  ./dirdef/tables_sqlserver.sql

--Map

MAP dbo.DLOG_ERRORS,     TARGET T_DB1_SAC_V.VW_DLOG_ERRORS;
MAP dbo.SAC_DATA,	 TARGET T_DB1_SAC_V.VW_SAC_DATA;
MAP dbo.SAC_LIST,  	 TARGET T_DB1_SAC_V.VW_SAC_LIST;
MAP dbo.SAC_TITLE, 	 TARGET T_DB1_SAC_V.VW_SAC_TITLE;

This is a simple example of replication between SQL server and Teradata, a bunch of customizations can be performed depending on the business need.

Enjoy.
Dieison.