Duplicate Detection for System Transactions

Learn how to detect and resolve system transaction execution failures.

truncate table and certain supported DDL commands are not logged, although they can be replicated to standby and replicate databases. See the Adaptive Server Enterprise Reference Manual for information about each DDL command.

Replication Server copies these commands as system transactions, in which Replication Server “sandwiches” the truncate table or similar command between two complete transactions. Execution of the first transaction is recorded in the replicate database in the secondary_qid column of the rs_lastcommit table and in the origin_qid column of that table. If Replication Server records the second transaction, the system transaction has completed, and Replication Server clears the secondary_qid column.

The following message, after a system failure, indicates that a system command has not completed. The connection shuts down.

5152 DSI_SYSTRAN_SHUTDOWN,"There is a system
transaction whose state is not known. DSI will be
shutdown."

You must verify whether the command within the system transaction has executed at the replicate database.

If the command has executed, or if you execute the command yourself, you can skip the first transaction in the queue and continue with the second transaction when you resume the connection. At the replicate Replication Server, enter:
```
resume connection to data_server.database
skip transaction
```
If the command has not executed, you can fix the problem, then execute the first command in the queue. At the replicate Replication Server, enter:
```
resume connection to data_server.database
execute transaction
```

You must include the skip transaction or execute transaction clause with resume connection. Otherwise, Replication Server does not reset the secondary_qid correctly, and the error message reappears.