SCD-Wrong Date Value

ds_search2008 · Post by **ds_search2008** » Wed Apr 29, 2009 8:26 am

Hello all,

In one of the SCD jobs, I'm facing a new issue. I'm inserting the new version of rows and updating the previous version with expiration date value. Everything seems to be fine, except that the effective date column value is getting wrongly updated in database.

Effective date column is directly getting populated from source and there are no transformations involved.

I don't understand how this column is getting updated with new value. As per my job design I'm doing update first then insert at the end. Kindly suggest me your opinion in this regard.

Many thanks & Regards

bollinenik · Post by **bollinenik** » Wed Apr 29, 2009 9:37 am

your question is not clear to me.
But if you are expecting to update effective date column with value from Source and it's not doing, even you are doing direct mapping.
then, try with 2 records u can easily understand where it's going wrong, it might be because of Default values specified for that column, it's taking from there.
other you want to populate with sysdate then you can just alter Insert/update query for that column =sysdate, then it will populate with sysdate/currentdate.

ds_search2008 · Post by **ds_search2008** » Wed Apr 29, 2009 11:56 am

Bollinenik, Thanks a lot for your time and reply.

Now my effective date column is loaded with correct data. However, my update DB2 stage is failing to update the Expiration date. I'm able to see the old and new data in the target table.

However, the Expiration date is not getting updated as per the transformation below.

Code: Select all

DateFromDaysSince(-10, inputlink.EFFECTIVE_DATE)

Log is showing the warning message "No row was found for UPDATE."

Actual outcome:

Code: Select all

Indicator   EFFECTIVE_DATE    EXPIRATION_DATE

N               09/30/2011           12/31/9999
Y               10/11/2011           12/31/9999

Expected outcome:

Code: Select all

Indicator   EFFECTIVE_DATE    EXPIRATION_DATE

N               09/30/2011           10/01/2011
Y               10/11/2011           12/31/9999

I searched this forum for this warning message and found 3 matches. But, I couldn't find a solution for this issue. Could you help me in this regard.

Thanks a lot & Kind Regards

Kryt0n · Post by **Kryt0n** » Wed Apr 29, 2009 5:20 pm

Would imply no records are meeting your update constraint. Try writing your update stream to a sequential file and see what values it has. Then run your own check to see if a record exists with such critiera

vmcburney · Post by **vmcburney** » Wed Apr 29, 2009 7:03 pm

Is your transformation statement before, during or after the SCD stage? Doesn't the SCD stage set the EXPIRATION_DATE automatically after you set it to be an EXPIRY date? Is it overwriting your date?

ds_search2008 · Post by **ds_search2008** » Thu Apr 30, 2009 5:42 am

Kryt0n and vmcburney thanks a lot for your replies.

Try writing your update stream to a sequential file and see what values it has. Then run your own check to see if a record exists with such ...

I tried as per your suggestion and found that I can see only new records and the old records are missing.

Is your transformation statement before, during or after the SCD stage? Doesn't the SCD stage set the EXPIRATION_DATE automatically after you set it to be an EXPIRY date?

I'm not using SCD stage. Instead I'm using CDC stage. My logic in brief is below:

Code: Select all

                 (beforerun-Target table)
                       |
                       |
                     sort stage       Join-------(Target table)
                       |             |  |     
                       |  (old rows) |  |    
after run ---->sort---CDC---transformer1---funnel---transfomer2--update old
(source table)                          (New rows)
                                                      |
                                                      |
                                                 Insert New

From transformer1 there are two links (1 to pass new rows) and another to get old rows.

Join type: Left outer
Funnel: continuous

Should I change the join type or funnel type to get both old and new records. Kindly suggest me your valuable ideas.

Thank a lot.

ds_search2008 · Post by **ds_search2008** » Thu Apr 30, 2009 1:48 pm

Dear experts,

Now I understood, the real issue is with the join stage or funnel stage only. I tried different combinations of Join and funnel type.

Join Type: (Inner, Left Outer, Right Outer and Full outer)
Funnel Type: continuous, sequence and sort.

I removed the join stage and funnel stage and tried using a lookup stage instead to get the effective date. By doing so I'm able to update the existing rows (old rows) only. Log is showing warnings and the new rows are not getting inserted.

I am trying to get mainly the effective date (both old and new) to update and insert the expiration dates respectively. Kindly could you please kindly guide me and help me in this regard.

Many Thanks & Kind Regards.

Kryt0n · Post by **Kryt0n** » Thu Apr 30, 2009 5:04 pm

By CDC stage do you mean Change Capture?

Once you have split the old rows from the new rows, why are you joining them back together? Leave them in separate streams, run the updates first, then the inserts.

Would also advise you extract your "beforerun" data once and split (via copy stage) with one stream going to your CDC and the other to your join, the stream going to the join only needs your key and the effective_date.

Finally, you will need to do your inserts in a separate job as you won't be able to update and insert at the same time (and shouldn't try)

ds_search2008 · Post by **ds_search2008** » Fri May 01, 2009 12:17 am

Kryt0n thanks a lot for your suggestion. However, I'm not getting the desired output.

I changed one column in the target table. Also for the corresponding column in the source I have included new value in the source table. One more change I made to input table is the effective date. I tried to checking my CDC (Change Capture Stage) output.

For the inserted value (source table) record is proper and the change_code is 1. This seems to be fine.
For the edit value (change I did in target table) the output data is correct. However, the change_code value is 2.

The change_code for edited record should be 3 right? Please correct me if I'm wrong.

- Many Thanks & Kind Regards

Kryt0n · Post by **Kryt0n** » Fri May 01, 2009 12:48 am

Have you changed one of your change capture key fields? The fact it is determining a delete would imply so

DS_SUPPORT · Post by **DS_SUPPORT** » Fri May 01, 2009 1:10 am

Kryt0n · Post by **Kryt0n** » Fri May 01, 2009 1:17 am

Think about what makes a key field and then why it is considered to be a deletion may become clearer

chulett · Post by **chulett** » Fri May 01, 2009 6:53 am

Since we're somehow resolved now, can you please post the resolution in the thread as well? It will help future searchers who come here with the same problem. Thanks.

ds_search2008 · Post by **ds_search2008** » Mon May 04, 2009 3:15 am

Hello Craig,

Thanks a lot for your time.

Actually the problem is not resolved yet. Still I face the same issue. Sorry I'm helpless. Not sure what to do. That's why I closed this post. I apologize if I made any mistake.

I checked the PX manual also. As per the developer's guide document

Code: Select all

Before data

bcol0  bcol1  bcol2   bcol3   bcol4
1	7	1	1	b
4	5	4	4	e
5 	2	5	5	5


after data

bcol0  bcol1  bcol2   bcol3   bcol4
1	1	1	1	b
4	4	4	4	e
5 	5	5	5	5

Here the output is change_code : 3 (for edit), as the bcol1 in after data value got changed/edited.

My scenario is very much similar to this. However, I'm getting the change_code as 2. I have defined the change column as key column in sort stages (both under sorting keys and also under Hash partition type) and also in CDC. I'm not sure why CDC is giving me change_code =2 instead of change_code =3.

Could you please share your idea on this Craig.

Many Thanks and Kind Regards.