After-image columns and before-image columns

When you register a source for change-capture replication, by default only the changed (after-image) value in a column is captured. You can also choose to capture the previous (before-image) value.

z/OS®, Linux, UNIX, Windows: You can select whether to capture before-image values for individual columns in a table.
IBM® i: You can select whether to capture before images for all or none of the columns in a table. You cannot select this option for each individual column.
Sybase or Microsoft SQL Server: A table can contain only one column of type TIMESTAMP. When the data source is Sybase or Microsoft SQL Server and the source table has a column of type TIMESTAMP, select after images only for this column when you define it as part of the replication source.

Restriction: You cannot include before-image values in the CD table for columns with LOB data types.

The sections below discuss when you should choose each option.

Capturing after-image values only

For each column that you register for change-capture replication, you can choose for the Capture program or triggers to record only the after-image value for each change. When you select to capture after-image values only, the CD (or CCD) table contains one column for each changed value, which stores the value of the source column after the change occurred.

You do not need before images if you plan to use only base aggregate and change aggregate target-table types for this source. Before-image columns do not make sense if you plan to use your target table for computed values because there is no before image for computed columns. All other target-table types can make use of before-image columns.

Capturing before-image and after-image values

For each column that you register for change-capture replication, you can choose for the Capture program or triggers to record both the before-image and after-image value for each change. When you select to capture before-image and after-image values, the CD (or CCD) table contains two columns for each changed value: one for the value in source column before the change occurred, and one for the value after the change occurred.

When you choose to store both the before and after images in the CD (or CCD) table, the before-image columns and after-image columns have different values for different actions performed on the source tables:

Insert: The before-image column contains a NULL value. The after-image column contains the inserted value.
Update: The before-image column contains the column value before the change occurred. The after-image value contains the column value after the change occurred.
When you choose to have updates captured as delete and insert pairs, the delete row contains the before image from the update in both the before-image and after-image columns of the row, and the insert row contains NULL values in the before-image column and the after image in the after-image column.
Delete: The before-image and after-image columns contain the column value before the change occurred.

For columns that have before-images defined, replication limits column names to 127 characters because the entire column name can have only 128 characters. If the column name is longer, replication truncates the additional characters from the right by default, unless you have set your profile to truncate from the left. Because replication adds a before-image column identifier (the default is X) to target columns and each column name must be unique, you cannot use column names that are longer than 127 characters. For tables that you do not plan to replicate, you can use longer column names, but consider using 127-character names in case you might want to replicate these columns in the future.

The following list describes cases in which you might want to capture before-image values:

For keeping a history of your source data: If you want to keep data for auditing purposes, you might want to select both before and after images so that you have a record of how the data has changed over a period of time. A set of before-image and after-image copies is useful in some industries that require auditing or application rollback capability.
For update-anywhere configurations with conflict detection: In update-anywhere configurations where conflicts are possible between replica tables (where conflict detection is set to anything other than None), you must register both after-image and before-image columns for the CD table of the replicas so that changes can be rolled back if a conflict occurs.
When the key columns at the target are subject to update: When registering a source, consider the potential target tables that you might define by using this table as the source. Typically target tables are condensed and require a column or set of columns that make each row in that target table unique. These unique columns make up what is called the target key. If any of these target key columns might be updated at the source, SQL Replication requires special handling to ensure that the correct rows at the target table are updated. To ensure that SQL Replication updates the correct rows in the target table with the new key value, you can select to capture both after-images and before-images for the columns that will make up the target key. The Apply program needs the before-image values for these registered columns when it applies the changes of non-key source columns to target key columns in the target table. When applying the changes, the Apply program searches in the target table for the row by looking for the target key values that match the before-image value in the source's CD (or CCD) table, and then it updates that target row with the after-image value in the source CD (or CCD) table.
Although you register these before-image values when you register the source table or view, replication does not know that your application will make updates to the target key. Later when you define which targets subscribe to this source (by creating subscription sets), you can specify for the Apply program to perform special updates when applying changes from non-key columns at the source to key columns at the target.