Q Replication and Event Publishing

Prompting a Q Capture program to ignore unwanted transactions

You can specify that a Q Capture program ignore unwanted transactions, and these transactions are not captured for replication or publishing.

About this task

You can specify which transactions to ignore by using one or more of the following identifiers:

Transaction ID
Authorization ID
z/OS: Authorization token
z/OS: Plan name

For example, your operation might run very large purge jobs once a month and you prefer to run these jobs against each database rather than replicate the data. In this case you could use a specific and unique authorization ID for the purge jobs, and specify that Q Capture ignore transactions from that ID.

To prompt the Q Capture program to ignore a single transaction based on its transaction identifier, you use an asnqcap command parameter when you start the Q Capture program.

To ignore transactions based on authorization ID, authorization token, or plan name, you use SQL to insert the identifiers into the IBMQREP_IGNTRAN control table at the Q Capture server. You can use a wild card character (%) to ignore groups of transactions (see below for more detail).

Caution: Ignoring a transaction that will ultimately be aborted causes no data integrity issues. But ignoring a transaction that was committed at the source server typically causes divergence between source and target servers, and you might need to take other actions to synchronize the servers, for example triggering a new load of the target table or using the asntdiff table compare utility.

Procedure

To prompt the Q Capture program to ignore transactions:

Use one of the following methods depending on the identifier you plan to use:

Identifier Procedure

Identifier	Procedure
Transaction ID	Use the asnqcap command with the ignore_transid parameter to specify one transaction to be ignored. The format of the command is as follows: `asnqcap capture_server=q_capture_server capture_schema=q_capture_schema ignore_transid=transaction_ID` DB2® sources The `transaction_ID` is a 10-byte hexadecimal identifier in the following format: z/OS® 0000:`xxxx`:`xxxx`:`xxxx`:`mmmm` Where `xxxx`:`xxxx`:`xxxx` is the transaction ID, and `mmmm` is the data-sharing member ID. You can find the member ID in the last 2 bytes of the log record header in the LOGP output. The member ID is 0000 if data-sharing is not enabled. The first four characters are always 0000. For example, the following command specifies that a transaction be ignored in a data-sharing environment, with a member ID of 0001: `asnqcap capture_server=sample capture_schema=ASN ignore_transid=0000:BD71:1E23:B089:0001` Linux, UNIX, Windows Version 9.7 and below `nnnn`:0000:`xxxx`:`xxxx`:`xxxx` Where `xxxx`:`xxxx`:`xxxx` is the transaction ID, and `nnnn` is the partition identifier for partitioned databases (this value is 0000 for non-partitioned databases). The second four characters are always 0000. For example, the following command specifies that a transaction be ignored in a non-partitioned database: `asnqcap capture_server=sample capture_schema=ASN ignore_transid=0000:0000:0000:0000:BE97` Version 10.1 or higher with compatibility of 1001 or higher, or Version 9.8 0000:`llll`:`xxxx`:`xxxx`:`xxxx` Where `xxxx`:`xxxx`:`xxxx` is the transaction ID, and `llll` is the log stream identifier for databases with the DB2 pureScale® Feature. The first four characters are always 0000. For example, the following command specifies that a transaction be ignored in a database with the DB2 pureScale Feature: `asnqcap capture_server=sample capture_schema=ASN ignore_transid=0000:0001:0000:0000:BE97` Oracle sources The transaction ID is the value of XID from the V$CONTENTS view of the Oracle Log Miner utility. The ID is a RAW(8) value. When displayed in text, it is formatted as a hexadecimal string (16 digits total). To ignore more than one transaction, stop the Q Capture program and start it in warm mode with another transaction identifier specified.
Authorization ID, authorization token (z/OS), or plan name (z/OS)	Use SQL to insert one or more of the identifiers into the IBMQREP_IGNTRAN control table. Insert the appropriate identifiers into the following columns: AUTHID The authorization ID. Oracle sources: The authorization ID is the value of USERNAME from the V$CONTENTS view of the Oracle Log Miner utility. AUTHTOKEN z/OS: The authorization token (job name). PLANNAME z/OS: The plan name. For example, the following statement specifies that the Q Capture program ignore a transaction with an authorization ID of REPLDBA: `insert into schema.IBMQREP_IGNTRAN ( AUTHID, AUTHTOKEN, PLANNAME) values ( 'REPLDBA', NULL, NULL);` You can use a wild card character, the percentage sign (%), to represent any number of characters or none, which allows you to ignore groups of authorization IDs, authorization tokens, or plan names. For example, the following insert statement tells Q Capture to ignore any transaction with plan names that ends with the string "PLAN11": `INSERT INTO IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) VALUES (null, null, '%PLAN11', 'N')` This example tells Q Capture to ignore any transaction with an authorization ID that starts with "AUTH" and contains the substring "ID". Also, the authorization token has to match a string starting with "TOK11" (z/OS only): `INSERT INTO IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) VALUES ('AUTH%ID%', 'TOK11%', null, 'N')` If the percentage sign is part of the data, use a backslash character (\) to escape the percentage sign. For example, this statement specifies that Q Capture ignore the authorization ID AUTH%ID: `INSERT INTO IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) VALUES ('AUTH\%ID', null, null, 'N')` Pattern matching is case sensitive.

Transaction ID

Use the asnqcap command with the ignore_transid parameter to specify one transaction to be ignored. The format of the command is as follows:

asnqcap capture_server=q_capture_server
capture_schema=q_capture_schema
ignore_transid=transaction_ID

DB2® sources

The transaction_ID is a 10-byte hexadecimal identifier in the following format:

z/OS®

0000:xxxx:xxxx:xxxx:mmmm

Where xxxx:xxxx:xxxx is the transaction ID, and mmmm is the data-sharing member ID. You can find the member ID in the last 2 bytes of the log record header in the LOGP output. The member ID is 0000 if data-sharing is not enabled. The first four characters are always 0000.

For example, the following command specifies that a transaction be ignored in a data-sharing environment, with a member ID of 0001:

asnqcap capture_server=sample
capture_schema=ASN
ignore_transid=0000:BD71:1E23:B089:0001

Linux, UNIX, Windows

Version 9.7 and below

nnnn:0000:xxxx:xxxx:xxxx

Where xxxx:xxxx:xxxx is the transaction ID, and nnnn is the partition identifier for partitioned databases (this value is 0000 for non-partitioned databases). The second four characters are always 0000.

For example, the following command specifies that a transaction be ignored in a non-partitioned database:

asnqcap capture_server=sample
capture_schema=ASN
ignore_transid=0000:0000:0000:0000:BE97

Version 10.1 or higher with compatibility of 1001 or higher, or Version 9.8

0000:llll:xxxx:xxxx:xxxx

Where xxxx:xxxx:xxxx is the transaction ID, and llll is the log stream identifier for databases with the DB2 pureScale® Feature. The first four characters are always 0000.

For example, the following command specifies that a transaction be ignored in a database with the DB2 pureScale Feature:

asnqcap capture_server=sample
capture_schema=ASN
ignore_transid=0000:0001:0000:0000:BE97

Oracle sources

The transaction ID is the value of XID from the V$CONTENTS view of the Oracle Log Miner utility. The ID is a RAW(8) value. When displayed in text, it is formatted as a hexadecimal string (16 digits total).

To ignore more than one transaction, stop the Q Capture program and start it in warm mode with another transaction identifier specified.

Authorization ID, authorization token (z/OS), or plan name (z/OS)

Use SQL to insert one or more of the identifiers into the IBMQREP_IGNTRAN control table. Insert the appropriate identifiers into the following columns:

AUTHID: The authorization ID.
Oracle sources: The authorization ID is the value of USERNAME from the V$CONTENTS view of the Oracle Log Miner utility.
AUTHTOKEN: z/OS: The authorization token (job name).
PLANNAME: z/OS: The plan name.

For example, the following statement specifies that the Q Capture program ignore a transaction with an authorization ID of REPLDBA:

insert into schema.IBMQREP_IGNTRAN (
  AUTHID,
  AUTHTOKEN,
  PLANNAME)
values (
  'REPLDBA',
  NULL,
  NULL);

You can use a wild card character, the percentage sign (%), to represent any number of characters or none, which allows you to ignore groups of authorization IDs, authorization tokens, or plan names.

For example, the following insert statement tells Q Capture to ignore any transaction with plan names that ends with the string "PLAN11":

INSERT INTO 
IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) 
VALUES (null, null, '%PLAN11', 'N')

This example tells Q Capture to ignore any transaction with an authorization ID that starts with "AUTH" and contains the substring "ID". Also, the authorization token has to match a string starting with "TOK11" (z/OS only):

INSERT INTO 
IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) 
VALUES ('AUTH%ID%', 'TOK11%', null, 'N')

If the percentage sign is part of the data, use a backslash character (\) to escape the percentage sign. For example, this statement specifies that Q Capture ignore the authorization ID AUTH%ID:

INSERT INTO 
IBMQREP_IGNTRAN (AUTHID, AUTHTOKEN, PLANNAME, IGNTRANTRC) 
VALUES ('AUTH\%ID', null, null, 'N')

Pattern matching is case sensitive.

Optional: Specify whether you want the Q Capture program to insert a row into the IBMQREP_IGNTRANTRC table when it ignores a transaction. By default, this tracing is disabled. To turn on the tracing, use the following SQL statement:
```
update schema.IBMQREP_IGNTRAN set IGNTRANTRC='Y'
WHERE identifier = transaction_identifier
```
The identifier would be any one of the AUTHID, AUTHTOKEN, or PLANNAME columns that was used to identify the transaction or transactions to skip.
When you use the Q Apply parameter insert_bidi_signal=n, setting IGNTRANTRC to N (no tracing) prevents the Q Capture program from inserting a row into the IBMQREP_IGNTRANTRC table for each transaction that it does not recapture and reduces maintenance overhead on the table.

Send your feedback | Information roadmap | The Q+SQL Replication Forum

Last updated: 2013-10-25