Log record variables to filter rows (unidirectional replication)

You can use variables from the database recovery log such as authorization ID or DML operation to filter which rows to replicate for unidirectional Q subscriptions. You can also combine these variables with an SQL WHERE clause to create more extensive row filters.

These variables describe how a change in the table came about, and include information about the operation of the change, the user who owns the transaction, and on z/OS® the job name or plan name. You can use the following log record variables:

$OPERATION	The DML operation. Valid values are I (insert), U (update), and D (delete).
$AUTHID	The authorization ID of a transaction.
$AUTHTOKEN	z/OS: The authorization token (job name) of a transaction.
$PLANNAME	z/OS: The plan name of a transaction.
$PARTITION	z/OS: For range-partitioned tables, the physical partition number to which the changed row belongs.

You create a predicate that uses these variables, and when the Q Capture program reads the log it determines whether or not to replicate transactions based on the predicate. This function enables you to create filters that are based on more than just the data in the base table. For example, you can filter on DML operations that might suit feeding a data warehouse, for example specifying that only insert operations are replicated ("$OPERATION IN 'I'"). You can also create ID-based filters at the Q subscription level, for example "$AUTHID = 'HR'", rather than using the Q Capture program's ability to ignore certain transactions, which operates at the Q Capture instance level.

To specify a predicate, you use the ASNCLP command-line program with the CHANGE CONDITION keyword of the CREATE QSUB or ALTER QSUB command. For example:

CREATE QSUB USING REPLQMAP DALLAS_ASN_TO_TOKYO_ASN
(SUBNAME SALES0002 OPTIONS CHANGE CONDITION 
"$AUTHID = 'HR'")

The predicate is stored in the CHANGE_CONDITION column of the IBMQREP_SUBS table.

Combining log record variables with search conditions

You can use log record variables in conjunction with the other type of row filter that Q Capture provides, the search condition that uses an SQL WHERE clause. Unlike values in the search condition, predicates that are based on log records do not need to be prefixed by the WHERE keyword.

If you specify a search condition with a WHERE clause and a predicate that uses log record variables, Q Capture combines the two predicates by using the AND operator. For example, the following search condition specifies that Q Capture only replicate rows where the value in the STATE column is CA (postal abbreviation for California):

WHERE :STATE = 'CA'

The following predicate with log record variables specifies that Q Capture replicate transactions with all authorization IDs except HR:

$AUTHID <> 'HR'

When the Q Capture program detects the WHERE clause in the SEARCH_CONDITION column of the IBMQREP_SUBS table and the log record predicate in the CHANGE_CONDITION column, it combines the two into a single predicate. Q Capture only replicates rows where the STATE is CA and the authorization ID of the transaction is not HR:

WHERE :STATE = 'CA' AND $AUTHID <> 'HR'

You could specify the entire combined predicate in the CHANGE CONDITION keyword of the CREATE QSUB or ALTER QSUB command and the result would be the same (as long as nothing was specified with the SEARCH CONDITION keyword). However, log record variables can only be used with CHANGE CONDITION.

Note: Although replication allows a CHANGE CONDITION of 2048 characters, Db2 might not be able to process all possible predicates that can be written in 2048 characters. If the statement is too long (combined with SEARCH CONDITION) or too complex, Db2 might return an SQL0101N error ("The statement is too long or too complex. SQLSTATE=54001").

Using $PARTITION for range-partitioned tables (z/OS)

You can specify the $PARTITION variable to replicate subsets of rows based on their partition number in a Db2 range-partitioned table. For example, the following predicate specifies that Q Capture replicate all rows from partition 1:

WHERE $PARTITION = 1

Q Capture reads the partition number for each row from the Db2 log and evaluates it in memory. Only simple change conditions such as =, <>, NOT, IN, LIKE, AND, OR are supported with $PARTITION. To skip replication of rows that are in partitions 1 through 4, you would use this predicate:

WHERE $PARTITION NOT IN (1,2,3,4)

Load considerations

Predicates with log record variables are not used when loading the target table because the variables are based on information that is not part of the source table, from which the load utilities select data. Search conditions are used when loading the target because they specify source table columns from which the load utility can select.

In the example above, a load utility invoked by the Q Apply program would use the following predicate when selecting source data to load into the target:

WHERE :STATE = 'CA'

If you want to load the target table with a subset of source data in addition to using log record variables, make sure to build your predicates carefully.

Considerations for asntdiff utility

If you use the asntdiff utility to compare data in source and target tables, be aware that many of the insert operations that the utility recommends to synchronize the tables are due to the predicate with log record variables. Do not use the asntrep utility to repair the table if you suspect that this is the case because the repair would undo the effect of filtering at the source.