I'm not a DB2 DBA, but am developing an application that runs off a DB2 database. I have several tables I need to update and insert, including a counters table. My SQL works fine when I run it in the development instance of the database, but when I tested in production I ran into a problem. There can be 300 to 400 end users updating this datbase at any time, so traffic on the counters table is fairly high.

In the split second it takes my code to get the next ID number from the counters table, assign it to my variable, then update the next ID number in the counters table, one of the end users had already taken the number that was assigned to my variable. I need to find a way to prevent this from happening, without locking out any of the end users. I was told it would be possible to do this within a transaction, but have been unsuccessful finding any information on this in any of my DB2 books. Can anyone help? Please???


I kayak N' board

Option Number 1:

EXEC SQL
DECLARE c1 CURSOR FOR
SELECT next_number
FROM table1
FOR UPDATE OF next_number;

EXEC SQL
OPEN C1;

EXEC SQL
FETCH C1 INTO :next_number;

EXEC SQL
UPDATE table1
SET next_number = next_number + 1
WHERE CURRENT OF C1;

[note: you will have to add 1 to the :next_number in your program to know what the next number is]

EXEC SQL
CLOSE C1;

EXEC SQL
COMMIT;

Option Number 2:

EXEC SQL
UPDATE table1
SET next_number = next_number + 1;

EXEC SQL
SELECT next_number from table1;

EXEC SQL
COMMIT;

If you delay the commit until you finish your other SQL in that transaction, it will lengthen the amount of time the lock is held, and may impact others in a high transaction workload. If you do the commit immediately after (as shown in above examples) you may have gaps in the range if the subsequent transaction fails is not completed for some reason.

Thank you for your response...

Right now I am looking at your option 2. I still have some questions, as I'm new to DB2 and programming. I can see that because you have both the update and select within the same transaction, the record will be locked until the transaction completes. After reading other posts on similar subjects, I have come to the conclusion that there is no way I can get around my problem without locking the record. I am curious what kind of impact that will have on a user who attempts to get the next number from that record. Would they get an error message, or would it just cause a slight delay in their transaction? This particular ID I need to get is for an audit table, so a new row is inserted each time any end user does anything in the database...so it is used very, very frequently. If users receied error messages our DBA would become very busy and very upset with me :-\

Another problem I beleive I will run into if I attempt to put the update and select within the same transaction, is I wrote my application in VB, and have the SQL coded as passthrough queries within the VB. I have to set an option for the query definition of returnsrecords = false for update and insert statements, and I then have to change returnsrecords = true for select statements. If I turn autocommit off within my ODBC connect string and use something similar to your examples, I cannot think of any way to keep an update and select statement within the same transaction, and have it return the number I need to my variable...because I can't set returnsrecords back to true for the select. Obviously if I run a select after updating the record, there is the possibility I will receive a bogus number, because it might change between my update and select.

Normally, the slight delay of the SQL I provided will not cause a locking problem for other programs if committed afterwards. Another transaction will just wait for the lock to be released.

But make sure you have your LOCKTIMEOUT database configuration parm set correctly.

-1....No timeout will occur
n.....number of seconds to wait before terminating
0.....terminate immediately if resource is locked

A LOCKTIMEOUT of -1 or at least 5 should be fine.

Regarding, your other question, I am not familiar with VB coding against DB2, so I can't help there. Sorry.

However, it might be useful to explain exactly what you are doing, and why. There might be other solutions for an audit table such as triggers that can automatically log updates to a table.