Hi,
I have a record in tgt table
If a record with same id comes to tgt then i want to update the old record and insert the new record at the same time .
How can i do this ??
I have managed to insert the record but then i also want to change the data of previous record with same id !
Please help !
I like what you suggested. Make sense to me.
And the "Insert or Update" for tOuputxxx component is so slow it's unbelivable.
I removed all of them and split my jobs in 2 parts : All the updates and the all the inserts. Less clean but this way batch processing is working.
10 time faster.
Regards
M.
I had tried "Insert or Update" for a job but that gives less control and it just somehow does not work as i was expecting.
I guess one can also use the bulk insert for the completely new records.
That could enhance the performance more.
The above suggested way does not work everytime.
i mean i m trying to maintain a history.
But the updating of the previous row does not occur everytime .And i m not able to trace the reason!!!!
Please let me know if any1 could figure it out !
What i would like to know is if im having 3 ops from a single component can i control their execution i.e can i execute 3 then 2 then 1 or 1,2,3 or any other combination ?
Key is on id and st_date so when a new entry of same id comes it has a different st_date.
But the problem i m facing is :
tMap : Inner Join is on ID and Match Model : UNIQUE MATCH
suppose Table A :
id;VAL1;VAL_ST
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
This is empty in the beginning
On my first run according to the job
Data is new so it is simply inserted.
And tables look like :
id;VAL1;VAL_ST;
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
1;A;1;null
Now i make changes to Table A changing its VAL_ST and VAL1
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;null
1 ; B ; 2 ; 1
Now when i run the job again
The same is getting added again when i m expecting a no change in the target!!
The table looks like :
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;null
1 ; B ; 2 ; 1
1 ; B ; 2 ;null
How do i avoid this ?
Doesn't look right. After your first change you should have id;VAL1;VAL_ST;VAL_END 1 ; A ; 1 ;2 1 ; B ; 2 ; null Can you post a picture of your job and the tMap.
i m so sorry for the confusion :
i will rewrite :
Key is on id and st_date so when a new entry of same id comes it has a different st_date.
But the problem i m facing is :
tMap : Inner Join is on ID and Match Model : UNIQUE MATCH
suppose Table A :
id;VAL1;VAL_ST
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
This is empty in the beginning
On my first run according to the job
Data is new so it is simply inserted.
And tables look like :
id;VAL1;VAL_ST;
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
1;A;1;null
Now i make changes to Table A changing its VAL_ST and VAL1
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;2
1 ; B ; 2 ; null
Now when i run the job again
The same is getting added again when i m expecting a no change in the target!!
The table looks like :
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;2
1 ; B ; 2 ; null
1 ; B ; 2 ;null
How do i avoid this ?
You need to check in your output insert if the new values are the same as the old values eg ! row1.VAL1.equals(row2.VAL1) & ! row1.VAL_ST.equals(row2.VAL_ST)
For that kind of operation, I would use 2 steps, linked with a 'onSubjobOk'.
First update, second insert.
I can't see how, with the existing components, you can easily perform the two actions at the same time.
This solution is pretty clear to implement and read later.
i will try the OnSubJobOk. My solution i have explained in the above posts. And when i test it ; it works fine for if i maintain a history upto 3 changes. But then after that it starts behaving unexpectedly. I am not able to figure out the reason for it yet!! @bcastell : i would like to know y is it not possible to maintain SCD type 2 with the above solution ?
Hi, Thanx for the help and suggestions. My problem has got solved. I think we can have SCD type 2 with the above job mentioned. If there is any other way(which i can bet there is) please do let me know. I think ppl here are a bit shy when it comes to SCD !! Please do let me know how else can an SCD 2 be implemented w/o using SCD component.
