The correct sequence of steps involved in creation and running of SHAREPLEX_IGNORE_TRANS procedure to ignore a transaction. (4302911)

The following are the correct steps for creation of and running of SHAREPLEX_IGNORE_TRANS procedure to ignore a transaction (as taken from SharePlex Administrator Guide for version 5.3) as steps listed in Administrator Guide from subsequent versions are incorrect:

Preventing DML transactions from replicating with create_ignore.sql

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.

The following steps listed in SharePlex Administrator Guide in versions higher than 5.3 list the steps incorrectly as below and can be ignored (step 1 does not make any sense and step 3 is not necessary):

1. In the source database, create a table with the following description. Name it dml_statement. Grant SharePlex full DBA privileges to the table.

2. On the source system, run the create_ignore.sql script from the util sub-directory in the SharePlex product directory. This script creates the SHAREPLEX_IGNORE_TRANS public procedure in the database. When executed at the start of the transaction, the procedure directs the Capture process to ignore the DML operations that occur until the transaction is committed or rolled back. Thus, the affected operations are not replicated. For more information about the script, its limitations, and how to run it, see create_ignore.sql in the SharePlex Reference Guide.

3. Edit your patch script to call SHAREPLEX_IGNORE_TRANS before UPDATE or DELETE operations. This allows SharePlex to ignore the transaction and not send it to the target. The script will also have to be run on the target to bring the database back into sync.