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

Start of change

FL 500 The DATA CAPTURE alteration does not wait for concurrent statements that depend on the altered table to commit, and the alteration is immediately visible to other concurrent threads on the same Db2 member, even before the ALTER commits. As a result, concurrent statements in the same transaction might write out log records in different formats.

For example, the following table shows how concurrent threads in the same transaction might see log records with different formats:

Thread 1 statements Thread 2 statements Statement results
UPDATE TB1 SET COL1 ...   The UPDATE statement sees the DATA CAPTURE NONE attribute of TB1 and, and it writes out a log record with a partial before image.
  ALTER TABLE TB1 DATA CAPTURE CHANGES The alteration is immediately visible to Thread 1. However, the catalog change is not visible to other threads until the ALTER TABLE statement commits.
UPDATE TB1 SET COL2 ...   The UPDATE statement sees the new DATA CAPTURE CHANGES attribute of TB1, and it writes out a full log record before the ALTER TABLE statement or its own transaction commits.
COMMIT    
UPDATE TB1 SET COL3 ...   The UPDATE statement sees the DATA CAPTURE CHANGES attribute of TB1, and it writes out a full log record before the ALTER TABLE statement commits.
  COMMIT The catalog update for the ALTER statement is now visible to other threads.
UPDATE TB1 SET COL 4 ...   The UPDATE statement sees the DATA CAPTURE CHANGES attribute of TB1, and it writes out a full log record.
COMMIT    
End of change