This article examines the advantages and disadvantages of using SHAREPLEX_IGNORE_TRANS or SP_OCT_DENIED_USERID for the purpose of ignoring a transaction or a user activity.
Advantages of SHAREPLEX_IGNORE_TRANS
The SHAREPLEX_IGNORE_TRANS allows ignoring one transaction and all subsequent transactions are not ignored. In that sense it is less prone to omissions/errors. To ignore another transaction, the procedure will have to be called again and so on.
It can be called from within another procedure/function and is thus more conducive to use in a situation where ignoring of transaction should follow some program logic and the like.
If the source database is accessed by multiple instances of SharePlex and one wishes to ignore the transaction for only one SharePlex instance, then SHAREPLEX_IGNORE_TRANS is a convenient choice.
Disadvantages of SHAREPLEX_IGNORE_TRANS
It has to follow a set of steps which may be deemed cumbersome by some users. If the steps are not religiously followed, then the transaction will not be ignored and will make it to target. This will require complex steps to then undo the transaction on the target. The purpose of using the feature (say, to avoid undue replication activity) will be lost.
It can only ignore DML operations and not DDL operations. The reason being, it is designed to stop ignoring activity once a COMMIT or ROLLBACK is encountered.
Advantages of SP_OCT_DENIED_USERID
Steps involved are very simple, namely configuring the parameter SP_OCT_DENIED_USERID so that all actions of a particular user will be ignored. Due to simplicity, chances of missing steps that would result in undesired transactions making it to the target are very remote.
The feature can be used to ignore not only the DML but also any other user action which would include DDL, CREATE statements and the like.
If the source database is accessed by multiple instances of SharePlex and one wishes to ignore the transaction for all the SharePlex instances, then configuring SP_OCT_DENIED_USERID is a better choice.
Disadvantages of SP_OCT_DENIED_USERID
Once the parameter is configured, all actions of that user are ignored. Unless a user is specifically created for the purpose of ignoring any activity, the parameter needs to be unset when the actions of that user are not to be ignored anymore as otherwise it can have unintended ramifications. Such omission can result in target not getting the changes that it was meant to receive and would require resync of the impacted actions like DML, DDL, CREATE, etc.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center