Support for Timestamp in Oracle GoldenGate for Sybase

The timestamp datatype is defined as varbinary(8) in Sybase ASE database. Despite its name, the timestamp in Sybase ASE database doesn't have any relation with the date and time. Timestamp is a column to keep a record of the column insertions and updates. There are actually two kinds of timestamps, TIMESTAMP and CURRENT TIMESTAMP. The difference is that CURRENT TIMESTAMP is only set on insert.

Each time a row with a timestamp column is inserted or updated, the timestamp column is updated automatically by the Sybase ASE database.

During the data replication, you should avoid update the timestamp columns in the Sybase database. This is explained in Oracle GoldenGate for Sybase Installation Guide:

"Timestamp columns must be excluded from Oracle GoldenGate when Sybase database is a target database. This is because the timestamps columns are populated automatically by the Sybase ASE database, which generates errors on the target if Replicat attempts to apply a replicated timestamp value. To exclude timestamp columns from being captured by Oracle GoldenGate, use the COLSEXCEPT option of the TABLE parameter. Because the system generates the timestamps, the source and target values will be different."

However, if the timestamp is used in Sybase ASE source database, you can capture data from the timestamp columns and setup a one-way replication. For example, if the source database is Sybase database and the target is Oracle database, you can create the mapping of Sybase timestamp to Oracle raw(8).  

The following is an example.The setup on the source is shown as follows:

Source (Sybase)

Set up Database

isql -Ususer -Psybaseuser -SASE1 -D sydatabase

create table transactions

(transaction_id numeric(6,0) not null,

 transaction_amount numeric (5,0) not null,

 transaction_ts timestamp,

primary key (transaction_id))

Add TRANDATA

dblogin sourcedb sydatabase@ASE1 userid ogguser
password ogguser

list table suser.*

add trandata suser.transactions

Generate the Definition File

defgen.prm:

defsfile./dirout/regions.def 
sourcedb sydatabase@ASE1 userid ogguser PASSWORD ogguser 
TABLE suser.regions;
TABLE suser.transactions;

shell ./defgen paramfile dirprm/defgen.prm

Add the Extract and Pump Group

emastr.prm:
extract emastr
exttrail ./dirdat/em

sourcedb sydatabase@ASE1 userid ogguser password ogguser
statoptions resetreportstats

report at 00:01

reportcount every 60 seconds, rate

table suser.regions;

table suser.transactions;


pmastr.prm: 
extract pmastr
rmthost localhost, mgrport 15021, compress, timeout 30

rmttrail ./dirdat/pm

passthru

table suser.regions;

table suser.transactions;

Start the Extract/Pump Process

start *

Insert/Check New DB Record

1> insert into transactions(transaction_id,
transaction_amount) values (1, 300)

2> go

(1 row affected)

1> select * from transactions

2> go

transaction_id transaction_amount transaction_ts     

 ----------
-- -------------- ----------------
              1           300 0x00000000000ad3f0


(1 row affected)

 The setup on the target database is shown as follows:

Target (Oracle)

Set up Database

sqlplus sybase_user/sybase_user

create table transactions (

transaction_id number primary key,

transaction_amount number not null,

transaction_ts raw(8));

Copy the Definition File

su - root
copy /home/ogg_for_sybase/dirout/regions.def 
/home/ogg_for_oracle/dirdef/regions.def 

chown oracle /home/ogg_for_oracle/dirdef/regions.def

Add the Replicat Group

rmastr.prm:
replicat rmastr
userid orclgg, password oracle

sourcedefs ./dirdef/regions.def

discardfile ./dirrpt/rmastr.dsc, purge

map suser.regions, target sybase_user.regions;

map suser.transactions, target sybase_user.transactions;

Start the Replicat Process

start *

Check the Replicated DB Record

SQL> select * from transactions
  2  /

TRANSACTION_ID TRANSACTION_AMOUNT TRANSACTION_TS

----------
-- -------------- --------------
             1                300 00000000000AD3F0

Feel free to let me know if you have any comments on the tip provided or need more information.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today