Problem while updating oracle table through datastage

[snt_ds]

Hi,

Arrangement_id and Recency_id are there in target table. Arrangement_Id is key column to that table and is populating from source table. Recency_Id is Populating with 0 by hardcoding it in transformer stage.Now Recency_Id is updating with (recency_Id+1) where Arrangement_id=0.

Two server jobs are existing to meet this requirement.

First job: soure-->Transformer--->TargetTable
!---------->hashfile(arrangement_id=0)

Second Job: hashfile---->TargetTable(update TargetTable set Recency_Id=Recency_Id+1 where arrangement_id=0)


Now i want to develop a single parallel job to meet abouve requirement.

I have designed job like:

TargetTable(Lkp)
!
!
Source----->Lkpstage----->Transformer------>TargetTable

In Lkpstage: doing lookup based on Arrangement_id and extracting Recency_id.

In transformer stage: Taken stagevarible with derivation of if Arrangement_id=0 then Recency_id+1 else 0.

and passing this stagevariable to Recency_Id field.

Whenever update happens and Arrangement_Id=0, Recency_id has to increment by 1. But it is incrementing to 1 and 2 only.

Its not incrimenting to 3 or above.


Anybody kindly help me to resolve this issue.

[Jasti]

Hi,
I am assuming that there are some more fields in the target table for inserts and you need to update Recency_Id only everytime u get Arrangement_Id=0.

While creating the table itself in the DDL give like
create table XXX
(----- -----,
Recency_Id -- default 0,
---- ---);

and change ur job design to:

TargetTable(Lkp)
|
| link1
Source----->Lkpstage----->Transformer------------->TargetTable(Insert)
|
| link2
|
TargetTable(update)

Now there are two o/p links from transformer link1 & link2.
The constraint on the link1 is Arrangementid<>0
do inserts in the stage Target table(Insert)

And on link 2 the constraint is Arrangementid = 0
and the derivation for Recency_Id is 1+Recency_Id

do updates in the table for Recency_Id column in the stage TargetTable(Update)
Please let me know if there is some change in the requirement.

[snt_ds]

I have tried the way which you have suggested.

In first link, i have taken update action is: LOAD . Records are not loading into the target table for longtime(still running). almost 40 mins for 4000 records.

Same records are loading in a minute time if i give update action like:UPSERT.

What might be the cause?

[Jasti]

Hi,
I don't know the problem with LOAD,but you can use Write Method Upsert in both the target table instances.
In TargetTable(Insert):Use insert then update &
in TargetTable(Update): Use Update only..
And change the Constraints for output links in the transformer stage to suite your requirement..