Sybase Replication Server: ignoring duplicate keys

Sybase’s Replication Server allows you to replicate data entry from one database into another (there can be more than one replicate database).  They don’t necessarily have to be even from the same vendor.

Duplicate rows will occur when an application inserts data into the primary and replicate database(s), if the data being entered in a replicated table.  Replication Server’s DSI connection will stop saying that it has detected a duplicate key and requires a DBA to tell it what to do.  If this duplicate key can be ignored, then the DBA will skip the transaction, which will make a note of the transaction and will skip it (go on to the next transaction).

resume connection to MYSERVER.MYDB skip transaction

The problem with this approach is that if there are a lot of duplicate keys, not only could you be sitting for a while skipping the transactions, you run the risk of skipping a transaction that isn’t a duplicate key.  Say if someone deleted the table on the replicate database..  You could easily make a mess of things if you arbitrarily skip transactions.

Replication Server has a feature called error classes that you can define the course of action if an error occurs with a DSI connection.  The only real issue is that the lowest level of granularity is at the DSI connection level and the highest is all insert dbms type (i.e. ASE) replicated systems.  To create an error class:

create error class ASEallowdupsErrorClass

The error classes can be inherited so if you wanted an error class to ignore duplicate keys and another to stop replication on a duplicate key, you would do something like so:

RSSD> rs_init_erroractions ASEallowdupsErrorClass, rs_sqlserver_error_class

Sybase ASE’s error number for a duplicate key is 2601, but ASE will also raise the 3621 (aborted transaction) error.  We need to set the error class ASEallowdupsErrorClass to ignore duplicate keys:

assign action ignore for ASEallowdupsErrorClass to 2601
assign action ignore for ASEallowdupsErrorClass to 3621

Now that we’ve created the error class and set it to ignore duplicates, we need to do two last things:

  1. alter the DSI connections to use the new error class
  2. suspend and then resume the DSI connections for the DSIs to use the new error class
alter connection to MYSERVER.MYDB
set error class to ASEallowdupsErrorClass
suspend connection to MYSERVER.MYDB
resume connection to MYSERVER.MYDB

Generally, applications should not be performing data entry of the same data across the replicated databases as Replication Server is made for it.

Share Button

6 Replies to “Sybase Replication Server: ignoring duplicate keys”

  1. Very brief nice and quite well explaining. sometimes grammatical flaws make it hard to understand the statement otherwise its good.

  2. E. 2015/02/11 10:42:23. ERROR #1028 DSI EXEC(372(1) – dsiqmint.c(3062)
    Message from server: Message: 257, State 1, Severity 16 — ‘Implicit conversion from datatype ‘INT’ to ‘VARCHAR’ is not allowed. Use the CONVERT function to run this query.

    Did all the same steps as above for error class ASEallowalterErrorClass
    assign action ignore for ASEallowalterErrorClass to 257

    Worked perfectly thanks

  3. Thanks Jason, This work wonderfully on my DSS databases after table corruption on a PDB which forced me to get rows back from the standby.

  4. Hi Jason ,

    Nice blog, One doubt here will it keep track of skip transaction & store it somewhere so that when one can apply or ignore that duplicate transactions.

    Rajat Rajawat

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.