Universal DIFF table

The universal DIFF table is used by the asntdiff utility to record the differences between all of the table pairs that it is comparing. Each record in the DIFF table represents a row-specific difference between the source and target, where each row can be identified by its key values.

Table 1. Column in the universal DIFF table
Column name Description
DIFF Data type: CHAR(4); Nullable: Yes

A flag that describes how a key-specific row is different between the source and target tables. It could be one of three types: U (UPDATE), D (DELETE) and I (INSERT).

U
Rows with the same key value exist at both the source and target, but at least one non-key column is different at the target.
D
A row with the key value exists only at the target and not at the source.
I
A row with the key value exists only at the source and not at the target.
When the key value exists in both tables but is not unique in either table, two rows (DELETE and INSERT) are inserted into the DIFF table. The DIFF_TIME value of the row insert is one millisecond later than the DIF_TIME value of the row delete.
DIFF_IS_PERSISTENCE Data type: CHAR(1); Nullable: No

A flag to indicate if the difference that was found is persistent (saved to disk):

U (default)
UNKNOWN: The difference could be persistent or not persistent. When the comparison is in non-live mode (LIVE=N), the column value is always U. In live-mode comparisons, if the difference is not persistent the only possible cause is a long-committed transaction. That means, the parameter CS_READ is set to N or a timeout is returned after a key-specific fetch using the CS isolation level.
T
TIMEOUT: The difference could be persistent or not persistent. The limit of comparisons was reached and the rows are still different because the same row keeps being updated or not committed. The limit of comparisons is controlled by the RETRY_COMPARE parameter.
P
PERSISTENT: The difference is persistent.
CCRC Data type: VARCHAR(32) FOR BIT DATA; Nullable: Yes

The 64-bit checksum of this difference. The first 32-bit values are the source-side row-based checksum; the subsequent 32-bit value is the row-based checksum of the target. For non-parallel ASNTDIFF, the value is NULL.

DIFF_TIME Data type: TIMESTAMP; Nullable: No

The timestamp in GMT when the row was fetched for the last comparison.
BLOCK_NUM Data type: INTEGER; Nullable: Yes

The block number where this difference was identified. For non-parallel ASNTDIFF or the difference-recheck mode, the value is NULL.

SOURCE_SCHEMA Data type: VARCHAR(128); Nullable: Yes

The schema of the source table.
SOURCE_TABLE Data type: VARCHAR(128); Nullable: Yes

The name of the source table.
TARGET_SCHEMA Data type: VARCHAR(128); Nullable: Yes

The schema of the target table.
TARGET_TABLE Data type: VARCHAR(128); Nullable: Yes

The name of the target table.
KEY_COLS Data type: VARCHAR(15000); Nullable: Yes

The values of key columns that are used to identify the specific difference. The format follows the search condition in the SQL WHERE clauses. For example:

column_name=value AND column_name=value
AND column_name=value