An easy way to track changed rows on replicate ASE tables

[sladebe]

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