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:
- alter the DSI connections to use the new error class
- 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 go suspend connection to MYSERVER.MYDB go resume connection to MYSERVER.MYDB go
Generally, applications should not be performing data entry of the same data across the replicated databases as Replication Server is made for it.