Select & Update in same job

[bakul]

I have a job in which I perform a lookup using some value and then update the looked up values in the next transformer.
Now the problem is that if the key for lookup repeats then the values have already been updated so the lookup should return updated values. But the Lookup stage still returns the same un-updated rows.
I have used an ODBC stage for Lookup and a DB2 stage for update.
I have tried setting the isolation levels to 'Read committed' and 'Read Uncommited' in ODBC stage. I have configured DB2 stage with array size and transaction size both 1. But it still does not work.
Is there anything else I can do to ensure that the lookup returns the updated values?

Code: Select all

Input -------> Tfm ------------> Tfm -----------> Output
                |                |
                |                |
                |                | 
          ODBC Stage    DB2 stage(update)
          <lookup>

[changming]

load the table into a hash file then read and write the same hash file by using load into cache and lock when update option. it will update the hash file then refresh the hash file which will will looked up. after the hash file, there is odbc stage.

the problem is how you update when the table is a 2nd type slowly changing dimention, i have no idea.
so, if the table is the first slowly changing dimention, it will be no problem.

[bakul]

That would have been the perfect solution in ordinary circumstances. But the job is RTI enabled so I can't use any passive stage with both input and output links. Is there any other way to accomplish it ?

[gotosarath]

Try using Isolation level of 'Auto-Commit' in Transaction handling tab while updation. This can be of help

Thanks,
Sarath