To get different fields

[ds_search2008]

Hello all,

In one of my jobs the source table is defined with 27 columns and the target is defined with 25 columns. Both source and target metadata data exactly matches, except that the 2 columns (CUST_NBR,TYP_CODE) that are defined in source table are not there in the target.

I'm doing a comparison between the source and target. The CDC expects the same metadata in the source and target.

Code: Select all

                     Target table (25 rows - trying to get CUST_NBR &TYP_CODE)    |
                       |
                      Sort
                        |
Source table -->Sort-->CDC--> other stages-----target table
(27 rows)

Just to satisfy the CDC, I have defined these two columns in the columns tab of target DB2 stage I tried writing the below custom SQL, but unable to bring the two new fields in target DB2 stage.

Target DB2 custom SQL

Code: Select all

SELECT CUST_NBR, TYP_CODE FROM SCHEMA.TARGET_TABLE 
WHERE CUST_NBR = (SELECT CUST_NBR FROM SCHEMA.SOURCE_TABLE)
AND TYP_CODE = (SELECT TYP_CODE FROM SCHEMA.SOURCE_TABLE)

Is there a way to bring these two new fields in target DB2 stage just to satisfy CDC stage? I tried different ways but couldn't get these 2 columns in target table. Could you please share your valuable suggestions with me.

Many thanks & Kind Regards

[ShaneMuir]

If the 2 extra rows are not required - can you not just drop them from the input link?

Also can you read from and write to the same table?

[ds_search2008]

ShaneMuir, thanks a lot for your time and reply.

Actually I require these two source columns (CUST_NBR,TYP_CODE) in the middle (Links next to CDC stage) for other transformations. Therefore I'm trying to bring these two fields in the target DB2 stage (before path of CDC).

Could anybody kindly correct my Target DB2 custom SQL so that I can get two new fields to make CDC work.

Many Thanks & Kind Regards

[sureshreddy2009]

Hi
You just add column generator to target database and add these two columns and give some meaning full data for columns and proceed for further process

[ds_search2008]

Hello sureshreddy2009,

Thanks a lot for your great suggestion. My job is working fine. :D Thanks again for your kind help.

I wish to share one more point in connection with this soultion below:

• 1. I have deifined the required field in the Column Generator Stage.
  2. In the OutPut page of this stage ---> 'Edit Column Meta Data Box' by right clicking the new column and selecting 'Edit Row' option.
  3. Now in the 'Properties' option select 'Generator' box.
  4. Selected Algorithm option .Set 'Type=Cycle', Initial Value=1,Increament =0. (This is for integer column.)

Similarly for char and other data types we can define the required data.

This may be helpful for anybody who requires similar solution.

Many thanks great experts!!!!