DB Wiki

An Exception table will hold the details of all Conflicts that have occured within GoldenGate.
This is neccessary because Conflict Detection and Resolution (CDR) will be in place.

As the GGATE user create the EXCEPTIONS table …

CREATE TABLE GGATE.EXCEPTIONS (
EXCP_DATE              TIMESTAMP(6),
REP_NAME               VARCHAR2(10),
TABLE_NAME             VARCHAR2(56),
ERRNO                  NUMBER,
ERRMSG                 VARCHAR2(4000),
OPTYPE                 VARCHAR2(24),
COMMITTIMESTAMP        VARCHAR2(26),
RECCSN                 NUMBER,
RECSEQNO               NUMBER,
LOGRBA                 NUMBER,
LOGPOSITION            NUMBER,
GROUPTYPE              VARCHAR2(12),
FILENAME               VARCHAR2(50),
SRCROWID               VARCHAR2(40),
REPLAG                 NUMBER,
CNT_CDR_CONFLICTS      NUMBER,
CNT_CDR_RESOLUTIONS    NUMBER,
CNT_CDR_FAILED         NUMBER);

Now create a macro that will convert GoldenGate environment variables into values for inserting.
Save this macro as a file called exceptions.inc in $GGHOME/dirsql/macros/exceptions.inc …

MACRO #exceptioncols
BEGIN
excp_date = @DATENOW(),
rep_name = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
table_name = @GETENV ('GGHEADER', 'TABLENAME'),
errno = @GETENV ('LASTERR', 'DBERRNUM'),
errmsg = @GETENV ('LASTERR', 'DBERRMSG'),
optype = @GETENV ('LASTERR', 'OPTYPE'),
committimestamp = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
reccsn = @GETENV ('TRANSACTION', 'CSN'),
recseqno = @GETENV ('RECORD', 'FILESEQNO'),
logrba = @GETENV ('GGHEADER', 'LOGRBA'),
logposition = @GETENV ('GGHEADER', 'LOGPOSITION'),
grouptype = @GETENV ('GGENVIRONMENT', 'GROUPTYPE'),
filename = @GETENV ('GGFILEHEADER', 'FILENAME'),
srcrowid = @GETENV ('RECORD', 'ROWID'),
replag = @GETENV ('LAG', 'SEC'),
cnt_cdr_conflicts = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_CONFLICTS'),
cnt_cdr_resolutions = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_SUCCEEDED'),
cnt_cdr_failed = @GETENV ('DELTASTATS','TABLE', @GETENV ('GGHEADER', 'TABLENAME'),'CDR_RESOLUTIONS_FAILED')
END;

Within all 4 replicat parameter files add an “INCLUDE” line to describe the location of the macro file.
Also after every table involved in CDR, place the “MAP” statement below after it.

INCLUDE ./dirprm/macros/exceptions.inc
....
MAP MAILONLINE.USER_TRANSFER_ITEM, TARGET GGATE.EXCEPTIONS,
    EXCEPTIONSONLY, INSERTALLRECORDS, COLMAP (#exceptioncols());