Search conditions to filter rows in publications

By default when you create publications, all rows from the source table are published. However, when you create a publication, you can specify a WHERE clause with a search condition to identify the rows that you want to be published.

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 publish the change.

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

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 publishes 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

Event Publishing 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 publication 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 publication. If a publication contains an invalid search condition, then that publication will fail when the invalid condition is evaluated, and the publications 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 publishing 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 this example of a WHERE clause that has the actual names of the source table and columns, the table that is published is ADMINISTRATOR and SALES is the column name. 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 this example of a WHERE clause that has a column name, SALES is the column name.
  • Search conditions cannot reference values that were in columns before a change occurred; they can reference only after values.
  • 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.