DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
vimali balakrishnan
Participant



Joined: 27 Dec 2004
Posts: 60

Points: 643

Post Posted: Mon Dec 27, 2004 11:04 pm Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Unix
Hi,

I have to implement SCD type-II in DataStage.

I have a source table in which one column is considered as a key column which can be looked up.

For example,
consider the project master table, I have project_id,customer_id,customer5_id columns in my source.
my target table has project_id,customer_id,customer5_id,status columns.

I have to check if the project_id in the source is there in the target,if present then check if the other columns in the target is same as
the columns in the source i.e,if one record/row in the source is already present in the target.if same skip that record.

If the project_id is already present and if the other columns are not same as that of the source then insert this record into the target with status as 'A'
and update the status of the already existing record with the same project_id as 'I'.

Can u help me out with this????
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 52093
Location: Sydney, Australia
Points: 282768

Post Posted: Mon Dec 27, 2004 11:30 pm Reply with quote    Back to top    

Search the forum for SCD and "slowly changing dimension". It has been covered a number of times.
And it's straightforward to do.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
vimali balakrishnan
Participant



Joined: 27 Dec 2004
Posts: 60

Points: 643

Post Posted: Wed Dec 29, 2004 6:29 am Reply with quote    Back to top    

Assume my source table has data in the following format:

Source table:
1110 1111 22222
1111 2222 33333
1111 3333 44444

Target table:
1110 1111 22222 'Active' 10-01-2003 10-01-2004
1111 2222 33333 'Active' 11-01-2003 11-01-2004

Now my job should see if 1110 is present in the target and the other two columns are same,so it is the same in the target,so it should skip that record.

It starts with the next record,checks if 1111 is present in the target,
and the other records are same,so it is the same in the target,so it should skip that record.

It starts with the next record checks if 1111 is already present,so it is already present in the target but the other values are not same so
my job should update the status of the current record(1111) in the target to 'Inactive' and insert the new record from the source with status as 'Active' and the End date of the previous record as start_date of this new record.

The problem i face is how to identify the record to be updated and then insert the new record.

Can u explain me with the constraints that should be used.













vimali balakrishnan wrote:
Hi,

I have to implement SCD type-II in DataStage.

I have a source table in which one column is considered as a key column which can be looked up.

For example,
consider the project master table, I have project_id,customer_id,customer5_id columns in my source.
my target table has project_id,customer_id,customer5_id,status columns.

I have to check if the project_id in the source is there in the target,if present then check if the other columns in the target is same as
the columns in the source i.e,if one record/row in the source is already present in the target.if same skip that record.

If the project_id is already present and if the other columns are not same as that of the source then insert this record into the target with status as 'A'
and update the status of the already existing record with the same project_id as 'I'.

Can u help me out with this????
Rate this response:  
Not yet rated
prabu
Participant



Joined: 22 Oct 2004
Posts: 146

Points: 855

Post Posted: Wed Dec 29, 2004 12:00 pm Reply with quote    Back to top    

source Key-> lookup target Key


if targetkey is null (meaning this is a new record)
insert into target
else
-- target key is available in target
if NO ( checksummatch(all other columns other tahn status flag)) then
update target set status='Inactive'
insert new record with status as 'Active'
else
none
end if


please use this psuedocode to implement what you want
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 52093
Location: Sydney, Australia
Points: 282768

Post Posted: Wed Dec 29, 2004 3:10 pm Reply with quote    Back to top    

If you had searched the Forum, you would have determined that pre-loading a set of the current keys from the target table into a hashed file allows rapid and easy detection of existence, and can deliv ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours