SCD-Wrong Date Value
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 am
SCD-Wrong Date Value
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
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
-
- Participant
- Posts: 111
- Joined: Thu Jun 01, 2006 5:12 am
- Location: Detroit
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.
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.
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 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.
Log is showing the warning message "No row was found for UPDATE."
Actual outcome:
Expected outcome:
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
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)
Actual outcome:
Code: Select all
Indicator EFFECTIVE_DATE EXPIRATION_DATE
N 09/30/2011 12/31/9999
Y 10/11/2011 12/31/9999
Code: Select all
Indicator EFFECTIVE_DATE EXPIRATION_DATE
N 09/30/2011 10/01/2011
Y 10/11/2011 12/31/9999
Thanks a lot & Kind Regards
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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?
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 am
Kryt0n and vmcburney thanks a lot for your replies.
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.
I tried as per your suggestion and found that I can see only new records and the old records are missing.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'm not using SCD stage. Instead I'm using CDC stage. My logic in brief is below: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?
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
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.
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 am
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.
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.
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)
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)
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 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
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
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
Last edited by DS_SUPPORT on Fri May 01, 2009 1:24 am, edited 1 time in total.
-
- Participant
- Posts: 75
- Joined: Thu Nov 27, 2008 10:12 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
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.
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
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.