This article concerns replicating data from the SAP Adaptive Server Enterprise (“ASE”) database server (formerly Sybase ASE) to the SAP Replication Server (aka “repserver,” formerly the Sybase repserver)

In certain critical transactions, it might be desirable for the application to wait until the most recent transaction has made it out of the ASE transaction log into the repserver, before continuing

This can be done using the “sp_help_rep_agent” stored proc in ASE.  Here’s how it would work.

Just after the transaction commit, make the following call:

> sp_help_rep_agent test1_db,scan_verbose;
Replication Agent Scan Status
 dbname      pathname scanner_spid start_marker end_marker current_marker log_pages_left log_recs_scanned scanner_type    oldest_transaction
 ----------- -------- ------------ ------------ ---------- -------------- -------------- ---------------- --------------- ------------------
 test1_db    n/a                16 (233000,25)  (235087,5) (234854,32)               719           706500 syslogs_scanner (234854,28)

Note the values of the current_maker and the end_marker.  The numbers are interpreted as (transaction_log_page_number, row_within_the_transaction_log_page)

The end_marker points to the most recent data written to the transaction log (235087,5).

The current_maker is the most recent transaction log page sent to the repserver (234854,32).

If the current_marker equals the end_marker, then all data in the transaction log has been sent to the repserver and you’re done.  No need to wait.

If the current_marker is less than the end_marker, then all data between the current_marker and the end_marker is waiting to be sent to the repserver.

In this case, you want to wait until the data you just wrote at the end_marker is sent to the repserver (according to the current_marker).  Ie., you want to wait for the current_marker value to be equal to or greater than the end_marker value in the above output (235087,5).

After waiting a while, here’s the next sample:

> sp_help_rep_agent test1_db,scan_verbose;
Replication Agent Scan Status
 dbname      pathname scanner_spid start_marker end_marker  current_marker log_pages_left log_recs_scanned scanner_type    oldest_transaction
 ----------- -------- ------------ ------------ ----------- -------------- -------------- ---------------- --------------- ------------------
 test1_db    n/a                16 (235087,5)   (235576,16) (235389,14)               186           728393 syslogs_scanner (235389,12)

So you can see that the new current marker value (235389,14) is greater than the end_marker value from the first sample (235087,5).  For comparison purposes, you can treat the numbers like decimals.  Ie., 235389.14 >= 235087.5 is true.

So now you know your transaction was sent to the repserver.   If your primary ASE server suddenly explodes, you have less chance of losing critical committed transactions waiting in the transaction log.

It might be possible to start your wait after the last data modification in the transaction, but before the transaction commit.  Then, once all the data is sent, commit the transaction and wait again for the commit transaction log record to flush.

The benefit of doing this is, there’s a smaller amount of time for the fully committed transaction to sit waiting in the transaction log (and a smaller window of time for it to be lost if the box explodes).  But, if there is a significant backlog in the transaction log at the time of the commit, you could still have to wait a while for the commit transaction log record to flush.