CDC Guide for z/OS

10.5.1
- 10.5.8
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5
- 10.4.1
- 10.4.0 HotFix 1
- 10.4.0

Back Next

Changing the Schema of DB2 Source Tables

If you need to change the schema of a DB2 source table that is registered for change data capture, follow this procedure to ensure that you can restart extraction processing without change data loss or corruption.

Schema changes include the following types of column changes:

ALTER TABLE table_name ADD COLUMN column_name  << Operations on source tables that you registered with the Select all and notify changes option
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE
ALTER TABLE table_name ALTER COLUMN column_name SET column_alteration
ALTER TABLE table_name RENAME COLUMN column_name
ALTER TABLE table_name DROP COLUMN column_name

Stop SQL changes from being written to the table.

You can set read-only access on the table.

Verify that the DB2 ECCR captured all changes up to the point at which change activity was stopped.

If you use PowerExchange Condense, ensure that PowerExchange Condense has processed all of the captured changes. Then, shut down PowerExchange Condense.

Extract all of the captured changes to the target.

If you set the DB2 subsystem parameter RESTRICT_ALT_COL_FOR_DCC to YES and plan to alter columns from which the ECCR captures change data, disable DATA CAPTURE CHANGES on the table. Also prevent change activity on the table while DATA CAPTURE CHANGES is disabled.

If you do not disable DATA CAPTURE CHANGES, DB2 will issue SQLCODE -148 error code. If you allow SQL changes on the table while DATA CAPTURE CHANGES is disabled, the ECCR will not capture the changes, which will require you to rematerialize the table.

If you set the DB2 parameter RESTRICT_ALT_COL_FOR_DCC to NO, DB2 allows altering columns in tables that are defined with the DATA CAPTURE CHANGES option.

Alter the DB2 table schema. Reorganize the table space, if necessary.

Delete the existing capture registration for the table and the associated extraction map.

Create a new capture registration for the table using the new schema.

Issue the DB2 ECCR REFRESH command so that the ECCR can use the new registration.

Allow change activity to resume on the table.

Restart any extraction processes and, if applicable, PowerExchange Condense.

Managing DB2 Schema Changes

Download Guide

Watch

Comments

Communities

Knowledge Base