Forum Stats

  • 3,855,574 Users
  • 2,264,523 Discussions
  • 7,906,071 Comments

Discussions

Retrieve entire before update row image

user12000691
user12000691 Member Posts: 3 Blue Ribbon
edited Dec 8, 2010 3:30PM in GoldenGate
I am attempting to capture the entire row in a goldengate trail file with an ascii text file format. I am able to extract my captured changes to a flat file with use of the formatascii option in the extract parameter file. I am able to get some of the before image values for columns referenced in the update by using getupdatebefores. However, I would like to see before images for all columns in the row, not just those columns that updated.

A sample:

sjb_ext.prm:
extract sjb_ext
userid ggs_user, password ggs_pass
tranlogoptions excludeuser ggs_iccon
statoptions, reportfetch
cachemgr cachesize 50MB
formatascii, sqlloader
exttrail /ggs_trail/sb
wildcardresolve dynamic
getupdatebefores
table sjb.my_table;

from sqlplus:
create table sjb.my_table
(col_a number not null,
col_b varchar2(1) not null,
col_c date not null
);
alter table sjb.my_table add constraint my_table_pk primary key(col_a);

insert into sjb.my_table (1,'A',to_date('01-JAN-2010'));
commit;

in sb000000:
IAN1 NAN2010-01-01:00:00:00

sqlplus:
update sjb.my_table set col_b = 'E' where col_a = 1;
commit;

in sb000000:
VBN1 NA
VAN1 NE

I only get the fields which the update statement itself manipulated plus the primary key fields. As default behavior, this makes perfect sense to me. However, I'd like to get the before images for all columns on an update, not just the columns affected by the update statement. How can I configure my extract to retrieve the before images for all columns in the update statement?

desire trail file output:
VBN1 NEN2010-01-01:00:00:00
VAN1 NEN2010-01-01:00:00:00
Tagged:

Best Answer

  • 770151
    770151 Member Posts: 4
    Answer ✓
    All columns need to be sent to the transaction log at the source end. Execute ADD TRANDATA owner.table, COLS(col1,col2,..col<n>) after connecting to source DB using DBLOGIN and thereafter retry the complete process.

    Satish

Answers

  • 770151
    770151 Member Posts: 4
    Answer ✓
    All columns need to be sent to the transaction log at the source end. Execute ADD TRANDATA owner.table, COLS(col1,col2,..col<n>) after connecting to source DB using DBLOGIN and thereafter retry the complete process.

    Satish
  • Hi,

    I just want to add that instead of adding supplemental logging you can set it to be add automatically with each TABLE statement like this:

    TABLE <schema>.<table>, FETCHCOLS (*);

    for all columns
    or

    TABLE <schema>.<table>, FETCHCOLS (COL1,COL2...);

    for just desired columns

    Cheers
    Sebastian
This discussion has been closed.