Incremental Subscriptions and Where Clauses

Using references to other tables to construct selection criteria for incremental subscriptions has two consequences:

A View is created in the SQDR Schema referencing user tables on the source system. The views are created over the CT table (in SQDR) and any other user table that is referenced.

Unintended loss of incremental replication fidelity may ensue.

For example if a source table for replication is being selected based upon criteria in another table (the "filter" table) and changes to the "filter" table result in existing rows in the source table either being newly "selected" in the view or "de-selected", these changes will not be detected in the logs (as no inserts/delete activities occurred on the source table). This effect may be mitigated to the extent that a Foreign Key (FK) relationship exists between the source table and the filter table - which specifies "CASCADE DELETES" - so that changes to the filter table will either cause deletes in the log to appear for the source table, OR, rows must only be inserted into the source table AFTER a valid FK exists. This approach is not guaranteed to insure fidelity, because changes are evaluated at the time that Apply requests data, not at the time the transactions occur, so under some circumstances staged rows might be omitted, when they should be included, and vice versa.

We recommend that you avoid this use of horizontal partitioning of source by using only static data for criteria – i.e., use a constant expression in a partition rule.

For Db2 for i users: you cannot specify "criteria" when configuring incremental subscriptions for tables whose journaling is configured with Record Images=*AFTER; *BOTH record images must be journaled in order to use criteria.