Partitioning DB2 data manually - Solving Lock Issues

[debrujr]

Datastage 8.1.0.0 - Not native to the DB server
DB2 9.5 - 4 Node 16 Partitions AIX
Parallel Job

DB2_Connector -> DB2_Connector

The first connector is a simple sql statement selecting key1, key2 from my table where value <> what it needs to be.
Partition read method - DB2
Record Count 100000
Array Size 100000
Read Uncommitted
Autocommit Off
Preserve Partitioning : Default (Propogate)

The output connector is setting a column = to a hard coded value based off of the keys I selected in the first stage. update table set x = 1 where key1 = key1x and key2 = key2x
Record Count 100000
Array Size 100000
Read Uncommitted
Partitioning (Auto)
Buffer(Default)

The requirement of this is to merely update a table setting all values in a single column to a hard coded value. No external anything. I know that I can do this much faster by just issuing a simple update statement but it is what it is. When I let it issue the update statement(as-is) it will open all logical nodes and make connections to the DB and then start pumping data, within a few seconds the table locks up and eventually times out. The table is set at row level locking.

I have tried adjusting different variables, setting the partitioning methods different, sorting, etc.... to no avail. If I run it sequentially it runs fine, but takes an eternity.

In dabbling with nodenumber I found that if I create a new column in my input stage storing the nodenumber and then use that column in my where clause on the output, it works correctly. Basicly INPUT: nodenumber(key) as noder and then OUTPUT: where nodenumber(key) = noder. It works as expected in that each pipeline updates each node individually with the correct number of records.

Should datastage not be able to do this in the background by maintaining the same partitioning instead of me having to work around it and "partition" it myself? It also seems to take quite a while....

I have looked everywhere for best practices as far as datastage using db2 but cannot find anything. I would like to better understand how I can tune my db to help ds or vice versa..... The install guide and support on the site is either lacking or I am unable to uncover it. Thank you in advance for your replies! Cheers!


EDIT: I forgot to mention that it seems to run fine if I have the input stage "Generate" the sql and just give it a table name but then I lose the ability to issue a predicate with it.

[debrujr]

Moderator: Please move this to the parallel or appropriate category. My apologies.

[chulett]

Done.