DSXchange

I have a requirement to do simultaneous read and write to a DB2 table.

I need to lookup a DB2 table, if a value is not found in the lookup, compute a new value and update the lookup table so that all subsequent input rows with same key will use the same value from lookup table.

The steps I am doing is:

Read Input
For every input record
Lookup DB2 Table for the value
If Value is found
Write to Output
Else
Compute the random value
Write to Db2 Lookup Table
Write to Output
end If
end For


Probably in Server job we can do this using Hash files, but in Parallel job I am not able to implement this using a DB2 Stage. Please Help.

mouni wrote: Read Input
For every input record
Lookup DB2 Table for the value
If Value is found
Write to Output
Else
Compute the random value
Write to Db2 Lookup Table
Write to Output
end If
end For

Probably in Server job we can do this using Hash files, but in Parallel job I am not able to implement this using a DB2 Stage. Please Help.

DB2 stage is not an equivalence to Hash files

I believe you are missing a step here:-

1)
If your reference[dimesnion] and the target[fact] , are not the same
1)You should load the input data into your reference table first, then load your target(dimenion load).

i beleieve you are trying to load your dimesnion and the fact table(s) in the same job.

2) Also i undersand , your input key can appear multiple times.
say like

Product_id price
P001 100
P001 120

In this scenario you should want either the first record or the last record, use remove duplicates to keep either the first or the last.

3)Avoid using table lookups as much as possible , use a lookup fileset instead

regards,
Prabu

I am not able to implement this using a DB2 Stage

What is the problem you faced while implementing it?
Is it you have row level commit?