CDC Guide for i5/OS

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

Uses of BI and CI Fields in Extraction Maps

PowerExchange captures both before images and after images of data for all SQL UPDATE operations on source columns. To access before image data to process change data in some way during CDC sessions, add before image (BI) and change indicator (CI) fields to extraction maps.

For example, you can use the BI and CI fields for the following purposes:

To filter captured data for extraction and apply processing.

To update primary keys on the target based on whether primary keys on the source changed.

Case 1. Filtering Change Data for Extraction and Apply Processing

If you add CI fields for one or more data columns in an extraction map, PowerExchange compares before and after images of the data captured for these columns. If an UPDATE occurred, PowerExchange sets the generated DTL__CI_column_name value to Y.

You can use a DTL__CI_column_name in WHERE clause filters for CDC sessions to filter the change stream during extraction processing. In PowerCenter, define the filters in the

Filter Override

attribute of the session properties. By using these filters, you can reduce the amount of data that PowerCenter processes.

During extraction processing, PWXPC creates SQL SELECT statements that include the WHERE clause filters. PWXPC passes these statements to PowerExchange. PowerExchange selects and returns the data that matches the WHERE conditions. PWXPC then makes this data available to the CDC sessions. Additional manipulation of the data might occur in PowerCenter, based on how you define the mappings.

To filter change data for extraction and apply processing:

In the PowerExchange Navigator, edit the extraction map that you plan to import as the source definition for the CDC session. For each column that you want to filter on, add a CI field.

PowerExchange generates CI fields that have names in the format DTL__CI_column_name.

For more information about adding CI fields to extraction maps, see the

PowerExchange Navigator User Guide

In PowerCenter, define WHERE clause filters in the

Filter Override

attribute of the CDC session properties.

For the filters, enter DTL__CI_column_name conditions. For example, enter

DTL__CI_ACCOUNT='Y'

, where 'Y' indicates an Update occurred.

For more information about filter overrides on CDC sessions, see

PowerExchange Interfaces for PowerCenter

When the CDC session runs, PWXPC provides only the change data that matches the WHERE filter to PowerCenter for extraction and apply processing.

Using many filters with CI fields might noticeably increase CPU overhead.

Case 2. Updating Primary Key Fields on the Target

If the target primary key does not match the source primary key, or if the source database allows updates to primary key fields, CDC sessions cannot apply updates to target keys based on after image data only.

To prevent this problem, you can select the

option for the

Image Type

attribute on PWX CDC application connections. This option causes PWXPC to generate two transactions for each source UPDATE: a DELETE followed by an INSERT. The DELETE deletes the old row based on the before image. The INSERT inserts a row based on the after image.

Alternatively, to avoid the overhead of generating two transactions for every source UPDATE, select the

option for the

Image Type

attribute. Also use CI and BI columns in combination with a PowerCenter Flexible Target Key Custom transformation. With this configuration, PowerCenter generates an INSERT or UPDATE transaction only when a source UPDATE results in changes to primary key fields on the target. Complete the following steps to implement this solution.

To update primary key fields on the target using BI and CI fields:

In the PowerExchange Navigator, edit the extraction map that you plan to import as the source definition for the CDC session. Add both BI and CI fields for one or more of the primary key columns on the source.

Verify that the

Image Type

attribute on the PWX CDC application connection for the CDC session is

This setting causes PWXPC to pass Updates to the CDC session as Updates. Because you added BI and CI fields for key columns in the extraction map, Update rows for these columns include both before and after images.

In PowerCenter, define a Flexible Target Key Custom transformation.

The transformation uses the DTL__CI indicator for the source key columns to detect when Updates to primary key columns on the target are needed.

Add the transformation to the mapping for the CDC session.

For more information about Flexible Target Key Custom transformations, see

PowerExchange Interfaces for PowerCenter

Introduction to Change Data Extraction

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal