Incremental Subscription Options

Strict Apply Rules
(formerly Normal)

Normal handling. Any failure to successfully apply a DML operation will be flagged.

Allow UPSERT
(formerly Use Unique Constraints)

This option causes incremental subscriptions to copy the unique constraints on the source to the destination as unique constraints. When disabled (the default) Unique Indexes are mapped to Non-unique indexes and other constraints (Primary Keys, Foreign Constraints) are not copied to the destination.

When this option is enabled, an update that cannot be performed will be attempted as an insert.  Inserts that fail will be attempted as an Update.  Deletes that fail to update one row will be silently ignored.

Archive all DML Activity

This option results in the creation of an incremental subscription that treats all insert/updates/deletes as insert operations; i.e. just add the row with the "after" image to the destination table.  All DML activity is rendered as an "insert" to the destination table, providing the complete history of the DML operations.

In other respects the subscription's Apply functions are the same; an Altered or Missing notification from the Capture Agent will flag the subscription.  Truncate is ignored, since it is essentially a mass delete.  Special criteria opcodes are not handled.  No destination index is created since all operations only result in rows being inserted (i.e. no update or deletes using a where clause).  You may want to include derived columns for Capture Metadata such as @TS (timestamp) and @JED (Journal Entry details).

Note that in the case of deletes, all columns will be null, except the "before" key and any derived column expressions that do not depend upon image data (for example, journal entry details or other SQDR defined macros).

Ignore Deletes

This is passed as a configuration option to the SQDR Plus Capture Agent, and requests that the SQDR Plus agent NOT deliver "DELETE" operations for the Apply processing on the destination table. Changes that are associated with INSERT or UPDATE operations continue to be delivered for application on the destination table. This option is intended for users who wish to treat the destination as an "archive" in addition to maintaining current values. This scenario is common in Data Warehouse environments: for example if the source is a 24-month view of Sales Activity, the destination may be designed to maintain a superset of current data and historical data (e.g. five years of history). This option permits the purging of rows on the destination to be independent of the current activity. See also the advanced setting archiveIgnoreDeletes.

Decompose Update

This option instructs SQDR to replace Update operations with Delete+Insert.  This may provide better performance when the destination uses column store indexes or otherwise does not handle updates efficiently.

Requirements & considerations:

• A complete after image must be available.

• This option only modifies the behavior when using the Strict Apply Rules or Allow UPSERT destination option; it has no effect on the Archive or Only Stream Change Data apply options.

• The row count in the statistics will be twice the expected number, as each row update operation results in one Delete and one Insert operation.

Only Stream Change Data
(formerly Change Data processing only)

This option instructs SQDR Apply to perform all steps of processing the Change Data with the exception of updating the target table; select this option if you are using the Change Data Processing feature to call a stored procedure to perform functions other than updating a target table. Only the group stored procedure will be invoked; the destination table will not be modified by Apply. When using this option, SQDR processing will include a Before Image only if a criteria is specified for the subscription; you can specify a criteria such as 1=1.  See Change Data Processing for additional information.

Soft Delete
(formerly Treat Delete as Update)

If this option is enabled, deleting a row on the source does not delete the corresponding row at the destination. Instead, the destination row is updated with the values of any derived columns (e.g. columns using macros such as @ts; @LT; @ls).  In particular, a derived column defined with “@TYPE” macro is updated with the value of ‘D’ to indicate deletion. The values of non-derived columns in the destination row will not be updated.  This technique can be used to preserve a history of the deleted rows.  if a row is subsequently inserted in the destination with the same row identifier (i.e. primary key value), then the corresponding destination row is updated with all new column data from the Insert image.

If the destination contains the “@TYPE” column AND the “DELETE” baseline option is specified for the destination in the subscription, then running a subsequent baseline will not delete the rows with a value of ‘D’ from the destination.  If the “TRUNCATE” baseline option is specified for the destination, then all rows (including the rows with a ‘D' value) are dropped when a baseline is run.

Db2 for i source only: Alternatively, for a Db2 for i agent with collectLogDetail=true defined, a derived column defined with the “@JED(‘enttyp’)” macro is updated with the value of ‘DL’ to indicate deletion, and baseline behavior is the same as rows containing a 'D' value.