Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
sladebe
Active Participant
The following example shows an easy and efficient way to find newly changed rows (ie., inserted or updated) in a replicate table in a SAP ASE relational database server without having to use triggers.  This is for tables replicated by the SAP replication server.

For my test environment, I have the following table on the primary (er, "Active") db server:
[109] MYPRIMARY.testdb1-11:02:08-1> sp_help tbl; | head -20
Name Owner Object_type Object_status Create_date
---- ----- ----------- ------------- -------------------
tbl dbo user table -- none -- Jun 28 2023 2:15PM

Column_name Type Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
----------- ------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
f1 int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
f2 varchar 16200 NULL NULL 0 0 NULL NULL NULL NULL 0

 

The object has the following indexes
index_name index_keys index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local
---------- ---------- -------------------- ----------------------- ---------------- -------------------- ------------------- ------------
cidx f1 clustered, unique 0 0 0 Jun 30 2023 11:02AM Global Index

 

For the subscription replicate copy of this db, I have the same table but with an extra column system of system type “timestamp” and an index on that column:
[133] MYREPLICATE.testdb1_sub-11:10:12-1> sp_help tbl; | head -20

Name Owner Object_type Object_status Create_date
---- ----- ----------- ------------- -------------------
tbl dbo user table -- none -- Jun 28 2023 2:23PM

Column_name Type Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity
----------- --------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ----------
f1 int 4 NULL NULL 0 0 NULL NULL NULL NULL 0
f2 varchar 16200 NULL NULL 0 0 NULL NULL NULL NULL 0
f3 timestamp 8 NULL NULL 1 0 NULL NULL NULL NULL 0

 

The replicate object has an index on the timestamp column (which is not on the primary):
index_name    index_keys index_description    index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local  plldegree disabled
------------- ---------- -------------------- ----------------------- ---------------- -------------------- ------------------- ------------ --------- --------
cidx f1 clustered, unique 0 0 0 Jun 30 2023 11:01AM Global Index 0 0
timestamp_idx f3 nonclustered 0 0 0 Jun 30 2023 11:07AM Global Index 0 0

 

When the replication server inserts to a replicate db, it uses commands like insert into tbl (f1,f2) values( a, 'aaaa'). ASE knows to auto-fill the timestamp column for an insert like this.

So for my tbl table, the primary contains this row:
[114] MYPRIMARY.testdb1-11:17:10-1> select * from tbl; -mvert   -- I'm using sqsh -mvert option because f2 happens to be a huge column
f1: 1
f2: aaaa

 

While the subscription replicate contains the same row, but with the timestamp column filled:
[134] MYREPLICATE.testdb1_sub-11:13:58-1> select * from tbl; -mvert
f1: 1
f2: aaaa
f3: 0x00000000031281d9

 

Then, on the subscription replicate, I can look for new rows (recently updated, or deleted/re-inserted) by running the command:
select * from tbl where f3>0x00000000031281d9; -mvert

 

For example, if I update the primary:
update tbl set f2="bbbb" where f1=1;

 

Then I can run this query on the subscription replicate to find the updated row:
[136] MYREPLICATE.testdb1_sub-11:19:34-1> select * from tbl where f3>0x00000000031281d9; -mvert
f1: 1
f2: bbbb
f3: 0x000000000312824c

 

This only works on replicates because the replication server uses the insert syntax:
insert into <mytable> (<primary column list>) values( <primary value list>)

for replicating inserts (ie., letting the timestamp value be implicitly filled in)

Problems with this idea include the possibility of slightly more deadlocks on the subscription replicate. Also, any query that uses “select *” on the replicate will pickup the timestamp column. There's a small possibility that could cause some hiccups for code which isn't expecting that.

Timestamps for the same db.table.row on different replicate servers would be different.

Also, when a table with already existing rows initially has the timestamp column added, all the timestamp values will be null (thus the non-unique timestamp index). You can use the search clause 'where f3>0x0' for the initial search. It will automatically ignore null values in f3 (0xNNNN is hex format in Sybase)

Ben Slade
Senior DBA @ NCBI.NLM.NIH.gov

PS. Note, if replicating a table with a timestamp column on the primary, see the documentation page:
Replicate timestamp Columns
1 Comment
Labels in this area