Search conditions to filter rows (unidirectional replication)

By default when you create Q subscriptions for unidirectional replication, all rows from the source table are replicated to the target table or stored procedure. However, when you create a Q subscription for unidirectional replication, you can specify a WHERE clause with a search condition to identify the rows that you want to be replicated.

When the Q Capture program detects a change in the Db2® recovery log that is associated with a source table, the Q Capture program evaluates the change against the search condition to determine whether to replicate the change to the target table or stored procedure.

If you are creating a single Q subscription, then the Create Q Subscriptions wizard in the Replication Center helps you add a WHERE clause to replicate a subset of the rows from the source table. If you are creating multiple Q subscriptions at one time, then, on the Review page of the Create Q Subscriptions wizard, select the individual Q subscription for which you want to subset rows and edit the properties for that Q subscription to add the WHERE clause.

If you define a Q subscription so that the target table is initially loaded with source data, the search condition for the Q subscription is evaluated when the target table is loaded. Because the row filter is used while loading the target table, the target table initially contains a subset of the rows in the source table.

When you specify a WHERE clause, you can specify whether the column is evaluated with values from the current log record. If you want a column in the WHERE clause to be evaluated with values from the current log record, place a single colon directly in front of the column name.

Example of WHERE clause that evaluates a column with values from the current log record:

WHERE :LOCATION = 'EAST' AND :SALES > 100000

In the above example, LOCATION and SALES are column names in the source table that are evaluated with values from the current log record. Here, the Q Capture program sends only the changes from the source table that involve sales in the East that exceed $100,000. When you type a column name, the characters fold to uppercase unless you enclose the name in double quotation marks. For example, type "Location" if the column name is mixed case.

If the Q Capture program replicates a column that is part of the WHERE clause, it might need to change the type of operation that needs to be sent to the target table or stored procedure.

Example where the Q Capture program must change the type of operation because of a WHERE clause:

WHERE :LOCATION = 'EAST'
AND :SALES > 100000

Suppose that the following change occurs at the source table:

INSERT VALUES ( 'EAST', 50000 )
UPDATE SET SALES = 200000 WHERE LOCATION = 'EAST'

Because the before value does not meet the search condition of the WHERE clause, the Q Capture program sends the operation as an INSERT instead of an UPDATE.

Likewise, if the before value meets the search condition but the after value does not, then the Q Capture program changes the UPDATE to a DELETE. For example, if you have the same WHERE clause as before:

WHERE :LOCATION = 'EAST'
AND :SALES > 100000

Now suppose that the following change occurs at the source table:

INSERT VALUES ( 'EAST', 200000 )
UPDATE SET SALES = 50000 WHERE LOCATION = 'EAST'

The first change, the insert, is sent to the target table or stored procedure because it meets the search condition of the WHERE clause (200000 > 100000 is true). However, the second change, the update, does not meet the search condition (50000 > 100000 is false). The Q Capture program sends the change as a DELETE so that the value will be deleted from the target table or stored procedure.

Complex search conditions

Q Replication allows you to specify more complex WHERE clauses. However, complex search conditions might impact performance. For example, you can specify a more complex WHERE clause with a subselect that references other tables or records from either the source table or another table.

Example of WHERE clause with a subselect:

WHERE :LOCATION = 'EAST'
AND :SALES > (SELECT SUM(EXPENSE) FROM STORES WHERE STORES.DEPTNO = :DEPTNO)

In the above example, the Q Capture program sends only the changes from the East that resulted in a profit, where the value of the sale is greater than the total expense. The subselect references the STORES table and the following columns in the source table: LOCATION, SALES, and DEPTNO.

When you define a Q subscription with a subselect in a WHERE clause, the following problems might occur:

Performance might be slower because, for each change in the source table, the Q Capture program computes a large select on the STORES table to compute the SUM(EXPENSE) value. Also, this type of select might compete for locks on the tables.
The subselect might produce unexpected results. For example, because the subselect is evaluated against the current database values, the example above produces a wrong answer if the EXPENSE value changes in the database, whereas columns in the WHERE clause are substituted with the older log record values. If the table name that the subselect references does not change, then the search condition produces the proper results.

Restrictions for search conditions

Search conditions cannot contain column functions, unless the column function appears within a subselect statement.
Invalid WHERE clause with column functions:
```
#-----------------------------------------------------------------
#  Incorrect:  Don't do this
#-----------------------------------------------------------------

WHERE :LOCATION = 'EAST' AND SUM(:SALES) > 1000000
```
The Replication Center validates search conditions when the Q Capture program evaluates them, not when the Replication Center creates the Q subscription. If a Q subscription contains an invalid search condition, then that Q subscription will fail when the invalid condition is evaluated, and the Q subscription will be deactivated.

Search conditions cannot contain an ORDER BY or

GROUP
BY

clause unless the clause is within a subselect statement.

Invalid WHERE clause with GROUP BY:


#-----------------------------------------------------------------
#  Incorrect:  Don't do this
#-----------------------------------------------------------------

WHERE :COL1 > 3 GROUP BY COL1, COL2

Valid WHERE clause with GROUP BY:


WHERE :COL2 = (SELECT COL2 FROM T2 WHERE COL1=1 GROUP BY COL1, COL2)

Search conditions cannot reference the actual name of the source table that you are replicating changes from. Do not use the schema.tablename notation in a WHERE clause for the actual name of the source table. However, you can reference another table name in a subselect by using schema.tablename notation.
Invalid WHERE clause with actual name of source table and column name:
```
#-----------------------------------------------------------------
#  Incorrect:  Don't do this
#-----------------------------------------------------------------

WHERE :ADMINISTRATOR.SALES > 100000
```
In the above WHERE clause, the table that is being replicated is ADMINISTRATOR and the column name is SALES. This invalid WHERE clause is intended to select only the values of the SALES column of the administrator table, for which SALES is greater than 100000.
Valid WHERE clause with column name:
```
WHERE :SALES > 100000
```
In the above WHERE clause, SALES is the column name.
Search conditions cannot reference values that were in columns before a change occurred; they can reference values only after a change occurred.
Search conditions cannot contain EXISTS predicates.
Search conditions cannot contain a quantified predicate, which is a predicate using SOME, ANY, or ALL.
Search conditions cannot reference LOB values.