MQSeries.net :: View topic

jainprav · Posted: Fri Dec 18, 2009 5:49 am Post subject:

Hi Everyone,

I am using JavaCompute Node in MB v6.1 to access a table in DB2.

Requirement:
We have to select those rows from the table that have the column (flag_value) value as 'x' and update those values to 'y'.

Also, we have same flow deployed in multiple Execution Groups of a Broker, so that we can expediate the completion of the task.

We want that if first 'z' rows are picked by 1st flow in one Execution Group then next 'z' rows (or remaining rows) should be picked by the second flow in another Execution Group.

What we want is, if we can obtain row level lock (and not table level) then if 1st flow picks 'z' number of rows then those rows will not be visible (i.e. no other query can select and update those selected records) to the 2nd flow and so the second flow will pick another set of rows with the value 'x'. The individual flows will update their set of records to value 'y'.

To achieve that we have used the below query:

SELECT * FROM table_name where flag_value = 'x'
FOR UPDATE OF flag_value
FETCH FIRST z ROWS ONLY with RR;

Another SELECT query to update the flag_value to 'y'.

As using first query, we are not able to obtain row level lock and both the flows pick the same set of records.

Can any one plz tell, have we written query correctly or is there a better way to write this query, or get a resolution to the requirement.

Any help/pointers shall be highly appreciated.
Thanks.