pfa wrote:

> Perhaps there's an alternative way to achieve my end result. I'll try
> to explain better:
>
> We have a program which "programmatically" builds a selection of keys
> to be processed at a later date by multiple processes run in parallel.
> As this list is "potentially" too big to be used in a WHERE key IN
> (...) I figured a function table which extracts each key and returns it
> as a row would work better:
>
> e.g. SELECT key u, data_column t from udftable('listfile') u, table t
> WHERE u.key = t.key
>
> (btw 'listfile' is currently a file on the OS which the udftable reads
> in and scans through looking for delimiters end returning a null
> terminated char * as the key, keeping the position in the list for the
> next fetch)
>
> Each process run in parallel feeds of the above SELECT (now this is a
> work in progress project so may not be the best way to go...we are not
> DB2 experts) and processes the data_column contents in some way. So the
> catch is that each row in "table t" must only be processed once so a
> got the idea of using a NEXTVAL sequence from DB2 so that each process
> when issuing a FETCH would get a unique key u, data_column t result
> because the udftable's function would skip keys in the list until the
> NEXTVAL position has been reached.
>
> The only other way I could see to do this was having a separate process
> (MQ series perhaps? never used it) which has this cursor and the other
> processes would fetch from it ensuring each row is only processed once.
>
> Splitting the list up is not an option as the number of processes
> started is up to the customer and they can start more after the others
> are already running.

My first idea would have been to use a temp table. You populate the table
once and then the different processes could use sequences to coordinate
which process operates on which rows. However, that really depends on the
functionality of the table function and copying its results into a temp
table might not be preferred and Serge's suggestion the way to go.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena