The user does not wish to replicate some DML transactions from a batch job. How can this be accomplished?
To prevent DML from replicating, run the create_ignore.sql utility script, which creates a public procedure named SHAREPLEX_IGNORE_TRANS in the source database.
When executed at the start of a transaction, the procedure makes the Capture process ignore DML operations that occur from the point of execution until the transaction is either committed or rolled back. Thus, the affected operations are not replicated.
- Only DML operations are affected by the SHAREPLEX_IGNORE_TRANS procedure. It does not cause SharePlex to skip DDL operations, including TRUNCATE. DDL operations are implicitly committed by Oracle, so they render the procedure invalid.
To run create_ignore.sql
- Run the create_ignore.sql script from the util sub-directory in the SharePlex product directory.
- Run it as the SharePlex Oracle user so that procedure is created in the SharePlex schema.
The script executes the following PL/SQL:
CREATE OR REPLACE PROCEDURE SHAREPLEX_IGNORE_TRANS AS TNUM NUMBER;
BEGIN
INSERT INTO SHAREPLEX_TRANS (TRANS_NUM, QUE_SEQ_NO_1, QUE_SEQ_NO_2,
COMBO, OP_TYPE) VALUES (-999,0,0,'DUMMY',0);
DELETE FROM SHAREPLEX_TRANS WHERE TRANS_NUM=-999 AND COMBO='DUMMY'
AND OP_TYPE=0;
END;
/
GRANT EXECUTE ON SHAREPLEX_IGNORE_TRANS TO PUBLIC;
/
To execute SHAREPLEX_IGNORE_TRANS
Call SHAREPLEX_IGNORE_TRANS only at the beginning of a transaction containing operations that you do not want replicated. If it is called in the middle of a transaction, replicated operations preceding the start of the procedure will remain in the post queue indefinitely awaiting a COMMIT, because SharePlex does not release messages without one. The COMMIT will not arrive because Capture ignores all operations in the
transaction after the procedure is called. The Read process will retain unwanted cache information on those records indefinitely.
1. Log onto SQL*Plus as the SharePlex user.
$ sqlplus name/password
2. Execute the SHAREPLEX_IGNORE_TRANS procedure at the beginning of the
transaction that you want to skip.
SQL> execute SharePlex_schema.SHAREPLEX_IGNORE_TRANS;
3. In one transaction, make the changes to the database that you do not want replicated.
They will be ignored by Capture.
4. After the COMMIT or ROLLBACK, replication of subsequent transactions resumes normally.
Also refer to Solution # SOL55971 that delves on a new feature whereby Capture can be directed to ignore activities conducted by a certain user in the source database. This is done by configuring the Capture parameter SP_OCT_DENIED_USERID on source Shareplex. The new feature has its pros and cons that the solution describes.
This feature is only available from Shareplex 7.0 and up.
