DB2 Lookups on a Load Job

[JezT]

I have a load job that is adding a field to a Customer DB2 table. In my source file that I am loading up, I have 18 rows (2 different rows for each ID of 1-9).

If I do a lookup on the table and then add the data directly to the DB2 table (so that the lookup and target table are linked to the same transformer), it only brings back 9 rows (i.e. one for each ID). However, if I add a temp file in between the transformer and the target DB2 table, the lookup brings back 18 rows.

Any reason why by going straight from transformer to DB2 table, half of the data is lost and yet if I add a file in between, it brings back all 18 rows ?

Is it because the same transformer is performing a lookup and a load on the same DB2 table ?

It's just that by using the file in between, the efficiency of the job is effected and when this is running on our live system, there will be up to 8 million rows running.

[ogmios]

Probably transaction problems. Try 2 things:
- Try putting the transaction size of the loading stage to 1.
- Put the transaction isolation to uncomitted read.

Reading and writing from the same table in a job is known to cause problems in DataStage using DB2. The problem is that each database stage opens it own connection to the database, so they run using different connections and in a different transaction.

Ogmios

[ray.wurlod]

Is your load rule "insert or update" and does the target table have a primary key defined? Chances are that the first 9 rows are inserted, then the next 9 rows "update" (by overwriting). After all, you only have nine key values.