How do I get lookups to find updated records immediately?

[kiranh_1214]

Hi All,
I have a Scenario like, As soon as the records enter into my target table the data should reflect in lookup table.
Target table: DB2
Lookup table: DB2 ( target table)

Scenario:
==> A Token already exiting in table with status available (103)
SER_SEC_TOKN_NUM IP_ID_CUST IP_ID_USER SEC_TOKN_ACTV_IND SEC_TOKN_STAT_CDE IP_ID_USER_APVR
---------------------- -------------------- -------------------- ----------------- ----------------- ---------------
1790010276 1405 - N 103 --

===> There are 2 new input transactions . First one for TokenBind to update the status to 105 and second one is
TokenActivate to update 105 status record to SEC_TOKN_ACTV_IND = 'Y'

Processing sequence :
1. Look -up for SER_SEC_TOKN_NUM in the table for first transaction
2. Update the status from 103 to 105 along with ip_id_user and ip_id_user_apvr
3. Look-up for same SER_SEC_TOKN_NUM for second transaction ( it is expecting the token was updated in
step 2 but it reads the old record with status 103)
4. Update the flag to 'Y'

The expected result:

SER_SEC_TOKN_NUM IP_ID_CUST IP_ID_USER SEC_TOKN_ACTV_IND SEC_TOKN_STAT_CDE IP_ID_USER_APVR
---------------------- -------------------- -------------------- ----------------- ----------------- ---------------
1790010276 1405 999 Y 105 111

After running the job:

SER_SEC_TOKN_NUM IP_ID_CUST IP_ID_USER SEC_TOKN_ACTV_IND SEC_TOKN_STAT_CDE IP_ID_USER_APVR
---------------------- -------------------- -------------------- ----------------- ----------------- ---------------
1790010276 1405 -- Y 103 --

I have tried with SparseLookup, it's not working.

Please help me for the same!

[* Note - Title changed to be more descriptive - Andy *]

[ArndW]

Sparse lookups work correctly and are the way to solve this problem. Try running your job in just 1 node - the sparse lookup will read the new value as soon as it is written and committed to the database.

[asorrell]

To further expand on what Arnd said - most probably your updates aren't currently getting committed until the job completes. You'll need to change the stage to do a commit after each update. That will probably have a negative performance impact if you are updating a large number of records, but its the only way to get your job to work as it is designed.