CDC Guide for Linux, UNIX, and Windows

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

Back Next

Changing a SQL Server Source Table Definition

If you change the definition of a SQL Server source table that is registered for change data capture, use this procedure to enable PowerExchange to use the updated table definition and preserve access to data captured previously. Table definition changes include adding, altering, or dropping columns.

If you no longer need to capture change data from a column in a table, you can remove the column from the extraction map without changing the capture registration. Change data for that column is still captured but is not extracted.

To change a SQL Server source table definition:

Stop data change activity (inserts, updates, and deletes) on the table.

Verify that any change data that was captured under the current table definition has completed extraction processing. Then stop all PowerCenter workflows that extract change data for the table.

If you use the PowerExchange Logger for Linux, UNIX, and Windows, shut down the Logger.

Delete the capture registration and extraction map.

Use DDL to change the table definition in SQL Server.

Create a new capture registration that reflects the metadata changes. Set the Condense option to Part
and Status to Active
. If you are using PowerExchange Navigator to create the capture registration, PowerExchange creates a corresponding extraction map.

In the PowerExchange Navigator, create a new capture registration that reflects the metadata changes. Set the

Condense

option to

Part

, and set the registration status to

Active

The PowerExchange Navigator creates a corresponding extraction map.

The newly activated capture registration becomes eligible for change data capture.

Warm start the PowerExchange Logger.

The PowerExchange Logger begins capturing changes based on the new capture registration.

If necessary, change the target table definition to reflect the source table metadata changes.

In the PowerCenter Designer, import the altered source and target definitions. Edit the mapping if necessary.

If necessary, re-materialize the target table. After materialization completes, create the new restart tokens for the target table.

Create new restart tokens for the altered table.

Re-enable DELETE, INSERT, and UPDATE activity against the table.

Cold start the extraction workflows.

Managing SQL Server CDC

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

CDC Guide for Linux, UNIX, and Windows

CDC Guide for Linux, UNIX, and Windows

Changing a SQL Server Source Table Definition

Changing a SQL Server Source Table Definition