Navigator User Guide

10.5.1
- 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

Adding or Removing Change Indicator and Before Image Columns for a Data Column

You can edit an extraction map for a source table to add or remove change-indicator (CI) and before-image (BI) columns for one or more data columns.

In an extraction map, the CI column name is DTL__CI_data_column_name and the BI column name is DTL__BI_data_column_name.

A DTL__CI column indicates whether an UPDATE operation changed data in the associated column. You can use a CI column in an SQL WHERE clause filter for a CDC session to filter the change records that PWXPC passes to PowerCenter for processing. The DTL__CI column contains one of the following values:

. An UPDATE changed the column data.

. An UPDATE did not change the column data.

Null

. An INSERT or DELETE operation on the column occurred.

A DTL__BI column contains the BI for an UPDATE operation on the associated column. If the source database allows changes to primary keys, you can use the BI fields with a PowerCenter Flexible Target Key Custom transformation to trigger updates to primary keys on the target when source keys change.

If you use the PowerExchange Logger for Linux, UNIX, and Windows and specify AI for the CAPT_IMAGE parameter in the pwxccl.cfg file, do not add change-indicator or before-image columns. The AI setting causes the PowerExchange Logger to store only after images. Consequently, PowerExchange cannot provide BIs to PWXPC for downstream processing in PowerCenter. Also, if you enter BA for the CAPT_IMAGE parameter, the PowerExchange Logger stores both before images and after images for UPDATEs. Any INSERT or DELETE operations on the source result in null values in the change-indicator columns.

Open the extraction group.

Right-click the extraction map and click

Amend Change Indicator/Before Image Extensions

The

Extract Definition - Amend Columns

dialog box appears.

On the

Change Indicators

tab, select the columns for which to add DTL__CI columns. In the

Available Columns

list, select one or more columns and click

Add

The selected columns appear in the

Selected Columns

list.

On the

Before Images

tab, select the columns for which to add DTL__BI columns. In the

Available Columns

list, select one or more columns and click

Add

The selected columns appear in the

Selected Columns

list.

If the selected columns are user-defined fields with expressions that invoke PowerExchange functions, ensure that the functions support BI buffering. For more information about functions, see Appendix A, Appendix A: PowerExchange Functions for User-Defined Fields.

To remove DTL__CI or DTL__BI columns, in the

Selected Columns

list, select the columns and click

Remove

The selected columns move back to the

Available Columns

list.

Click

If you added DTL__CI or DTL__BI columns, the columns appear on both the

Map

and

Extensions