How to handle serach and insert a table at the same time?

olgc · Post by **olgc** » Tue Sep 21, 2004 1:53 pm

Hi there,

A job needs to insert a record into a table. before inserting, the table is searched to see if the record is already in the table. If it is, brings back its primary key. The Database is DB2 7.2.9. When the job is run, it hangs up. How can the transaction be set to allow the search and insert a table at the same time?

Thanks every body,

chulett · Post by **chulett** » Tue Sep 21, 2004 1:56 pm

How are you actually doing this "search"?

olgc · Post by **olgc** » Tue Sep 21, 2004 2:08 pm

The code is

SELECT * FROM IDBA.PLAYER WHERE PLAYER.FIRSTNAME=? AND PLAYER.LASTNAME=? AND PLAYER.POSTALCODE=?

ketfos · Post by **ketfos** » Tue Sep 21, 2004 2:14 pm

Hi,
You can have a lookup which does a lookup for the existing key.
In the tranfromer you can have two outputs.
1. Insert Record (constraint on transformer ISNull(key)
2. Update Record (constraint on tranformer Not(ISNull(key))

Ketfos

chulett · Post by **chulett** » Tue Sep 21, 2004 2:23 pm

Best Practice would have you create, in a "preprocessing" step or job, a hash file with all of the current business keys and their corresponding surrogate keys. Ideally, you would constrain that build to whatever records are currently staged for processing. Then your lookup can be done against the hash file and a decision made based on the success or failure of that lookup. As noted, you could also split out seperate links for your Update and your Insert actions.