Altering a table to capture changed data

You can use the ALTER TABLE statement to write data changes for that table to a log in an expanded format for use in data replication processing.

About this task

The DATA CAPTURE attribute of a table specifies whether a full or partial before image is written out to the log for updates to table rows. Tables are created with the DATA CAPTURE NONE attribute by default. This attribute means that Db2 writes out only partial before images in log records for updates to the table rows. To enable data replication, a table must be defined with the DATA CAPTURE CHANGES attribute, so that Db2 writes out full before images in log records instead of partial before images.

When a table is defined with the DATA CAPTURE CHANGES attribute, the full before images in the log records include the following information not included in the partial images:

  • SQL data change operations
  • Adding columns (using the ADD COLUMN clause)
  • Changing columns (using the ALTER COLUMN clause)

Data replication tools such as the following products use the expanded log records for replication processing:

The expanded format log records are not available for LOB and XML data.

Procedure

  • To enable replication products to capture data changes , complete the following steps:
    1. Issue an ALTER TABLE statement with the DATA CAPTURE CHANGES clause.
    2. Activate replication of the source table to the target table.
      The data replication tool checks the Db2 catalog field SYSTABLES.DATACAPTURE to ensure that DATA CAPTURE is enabled, otherwise the activation fails.
    3. The data replication tool starts consuming the full log records of the source table through IFCID 0306 or some other method.
  • To disable the capture of data changes by replication products, complete the following steps:
    1. Deactivate replication of the source table to the target table.
    2. Ensure that data replication tool stops consuming Db2 log records for the source table.
    3. Issue an ALTER TABLE statement with the DATA CAPTURE NONE clause.

Results

As part of the DATA CAPTURE alteration processing, Db2 completes the following actions:
  • Quiesce static packages that depend on the altered table
  • Quiesce and invalidate cached and stabilized dynamic statements that depend on the altered table

Because the DATA CAPTURE alteration waits for applications that depend on the altered table to commit, continuous concurrent activity on the table might cause the ALTER TABLE statement to fail.