Verifying That the DTL__ST Column Is in the Extraction Map

The CLOB column was
not
defined with the INLINE LENGTH(
bytes
) clause. All of the CLOB data is stored in the auxiliary table space.
The CLOB column was defined with the INLINE LENGTH(
bytes
) clause and the amount of CLOB data exceeds the inline length. In this case, the excess data is stored in the auxiliary table space.

Creating a PowerCenter CDC Workflow That Includes Expression and Lookup Transformations

In Source Analyzer, import the PowerExchange extraction map for the DB2 source to create the CDC source definition. You must select
CDC Datamaps
to list the extraction maps. This source definition will be used as the Source Qualifier in the mapping.
In Source Analyzer, import another DB2 source definition from PowerExchange but do not select
CDC Datamaps
. PowerExchange gets the source definition from the DB2 catalog for the DB2 subsystem ID that you specify. This source definition will be used as the lookup source.
In Target Designer, create the target definition.
In PowerCenter Designer, add the CDC source definition and target definition to a mapping.
From the
Transformation
menu, create the Expression transformation and unconnected Lookup transformation. Then add the transformations to the mapping. Connect the Source Qualifier output ports to the Expression input ports, and connect the Expression output ports to the Target Definition input ports.

Drag the required Source Qualifier columns to the Expression so that the ports are created with the correct attributes.

On the

Expression Ports

tab, perform the following actions:

Rename the CLOB column port ("LOBVALUE" in the example) to

column_name

_IN (LOBVALUE_IN). Then clear the

O

check box for this port. Retain the

I

check box selection.

Add a

column_name

_OUT port ("LOBVALUE_OUT" in the example). Then clear the

I

check box for this port. Retain the

O

check box selection.

In the

Expression

field for

column_name

_OUT port ("LOBVALUE_OUT"), enter an expression that calls the lookup LKP() function when the DTL__ST_

columnname

value is I (incomplete) and the SQL change is not a DELETE. For example:

IIF( (DTL__ST_LOBVALUE='I') AND (DTL__CAPXACTION != 'D'), 
:LKP.LKP_DSNB_Q1CLOB_INLINE(KEYCOL), LOBVALUE_IN))

If the source table contains multiple LOB columns, create a separate Lookup transformation for each one.
When you create the Lookup transformation, select
Source
in the
Location of the Look-up Table
field. Then make sure that you select the DB2 for z/OS lookup source, not the CDC source definition.
In the Lookup object, delete all columns except those that are used for input or returned values. The lookup function accepts only a singe return column for the CLOB.
Informatica recommends that you perform lookups with primary key columns instead of a ROWID column, unless you are sure that the ROWID column will not change after a DB2 REORG.
In the
Edit Transformations
dialog box for the Lookup transformation, perform the following actions on the
Transformation Ports
tab:
Create an input key column port ("KEYCOL_IN" in the example) that has the same attributes as the table key column. Select the
I
check box.
For the key column port ("KEYCOL"), select the
L
check box so that it is used in the SQL statements passed to the DB2 lookup. Clear the
O
check box.
For the CLOB column port ("LOBVALUE"), select the
O
,
L
, and
R
check boxes. The
L
setting causes the column to be used in the SQL passed to the DB2 lookup. The
O
setting causes column data to be returned from DB2. The
R
setting causes the column data that is returned from the lookup to be passed to the Expression transformation.
On the
Condition
tab, define a lookup condition that specifies the key column in the lookup table ("KEYCOL") equals the input key column port (KEYCOL_IN).