Transaction zapping in Sybase Replication Server

[DilipVoora]

Hi Experts,

Details :

PDS.PDB : ASE1.D30
RDS.RDB:ASE2.D30
SRS : ASE_RS

I tried zapping a transaction from the outbound queue of ASE2.D30 in two scenarios by using sysadmin sqm_zap_tran and they were successful with a message "The number of zapped/unzapped commands is xx" and also "END ZAPPED OR UNZAPPED TRANSACTION DUMP FOR 103:0" from RepServer errorlog. However when I dumped the outbound queue of ASE2.D30, I still see the details of the zapped transaction(secnario1) and the zapped transaction still got replicated to ASE2.D30(scenario2)

Scenario 1:

1. Created a test table Employee and inserted 10 rows(duplicates) with Emp_ID=10 in ASE1.D30 and it replicated successfully to ASE2.D30.
2. Deleted the inserted data (becauase to test the zapping) using a direct delete command (delete from Employee where Emp_Id=10 in ASE1.D30. DSI of the target(ASE2.D30) went down as expected and then I skipped the transaction to resume.
3. Dumped the outbound queue (ASE2.D30) and found the transactions and noted down the lqid to zap.
4. Took the second row using lqid 0:13:1 of the transaction I want to zap and zapped it using "sqm_zap_tran". 

Scenario 2:

1. Suspended DSI for target i.e; ASE2.D30.
2. Inserted a row in Employee table in ASE1.D30.
3. Checked the output of sqm(ASE2.D30) and noticed the data is in the queue as the FirstSeg.Block != LastSeg.Block
4. Dumped the outbound queue of ASE2.D30 and found the transaction.
5. Zapped the transaction using lqid and it was successful. 
6. Resumed the DSI of ASE2.D30.
7. The zapped transaction got replicated to the table in ASE2.D30 successfully.

Here I would like to get clarified on the below and help me to understand the behaviour.

1. How zapping a transaction internal works and how SRS will understand it and proceed with the next transaction in the queue?
2. Zapped transaction(s) won't be removed from the queue?
3. Zapped zransaction will be deleted from the queue only if the entire segment (64 blocks) is full?
4. Why the zapped transaction is replicated to target(w.r.t secnario 2) even though the zapping was successful?
5. RepServer's errorlog showed the zap status. Do we need to get anything other than this?
6. Do we need to do anything after zapping the transaction?

Attached the scenarios output, queue dump(file1,file-2 for scenario1 and the other for scenario2) before and after zapping the transaction.

Regards,
Dilip Voora

[Mark_A_Parsons]

tl:dr

Appears to be a bug. (see details - below)

If you have a support contract I'd suggest opening a tech support case to get a definitive answer.

-----------------------

Assorted notes ....

NOTE: While not mentioned in the body of the question the attached files show you did put the repserver into hibernation mode before running sysadmin sqm_zap_tran.

Going back through an assortment of manuals (15.x, 16.x) I can only find references to putting the repserver into standalone mode (ie, the manuals don't mention hibernation mode as being an option).

Of course, if the repserver is not in standalone or hibernation mode and you run sysadmin sqm_zap_tran you receive the error message:

Replication Server must be in standalone or hibernation mode to issue commands 'zapping' messages from the queue.

It's been a long time since I've zapped any transactions but the notes I have (from early 15.x) show that I was able to successfully zap transactions (and commands) by putting the repserver into hibernation mode.

Results of running tests on RS 16.0 SP03 PL07:

• whether I put the repserver in standalone or hibernation mode I see the same update of the status bits (+32768) after running sysadmin sqm_zap_tran
• I can duplicate your issue (ie, zapped txn is applied against the RDB) with the following steps: suspend DSI, DML in PDB, hibernate/on, zap, hibernate/off, resume DSI
• I get the desired result (ie, zapped txn is not applied against the RDB) with the following steps: suspend DSI, DML in PDB, bounce repserer (-M/standalone mode), zap, bounce repserver (normal mode), resume DSI
• I can also get the desired result (ie, zapped txn is not applied against the RDB) with the following steps: suspend DSI, DML in PDB, hibernate/on, zap, hibernate/off, bounce repserver (normal mode), resume DSI

Net result: I can only get the desired result (ie, zapped txn is not applied against the RDB) if there's a bounce of the repserver involved. This would appear (to me) to be a bug ... unless there's another command required that's not in the docs (and not in my notes from several years ago) ... ?

-----------------------

As for the issue of the transaction not being removed from the queue, this is expected behavior.

zapping a transaction performs a logical delete on the queue by setting a delete flag.  You can see this in the change of the status bits (+32768 == marked for deletion).

When the DSI sees the delete flag/bit is set it merely ignores said transaction.

This approach (logical delete) is required if simply because the related sqm_unzap_tran would not be possible if the transaction were physically removed from the queue. sqm_unzap_tran just has to clear the delete flag/bit.

[DilipVoora]

Hi Mark,

The status bit you are highlighting is next to lqid in the dump right? If it is correct, status bit for the transaction that I zapped (for scenario 2) is showing as 32769. 

ENTRY ver=1100 len=212 orig=102 lorig=0 oqid=00000000000021df000032b70020000032b7001e0000b164011e8e000000000000000001 lqid=0:14:0 st=32772 tr= '000000000000' ! 'df001e' ASE1D30 '000000' f '00000000' comlen=88 begin transaction
ENTRY ver=1100 len=272 orig=102 lorig=0 oqid=00000000000021df000032b70020000032b7001e0000b164011e8e000000000000000002 lqid=0:14:1 st=-1071607808 tr= '000000000000' ! 'df001e' ASE1D30 '000000' f '00000000' comlen=148 insert into dbo.Employee (Emp_No, Emp_Name) values (5, 'Ravi')
ENTRY ver=1100 len=188 orig=102 lorig=0 oqid=00000000000021df000032b70020000032b7001e0000b164011e8e000000000000000003 lqid=0:14:2 st=32769 tr= '000000000000' ! 'df001e' ASE1D30 '000000' f '00000000' comlen=63 commit transaction

Regards,
Dilip Voora