MVSFORUMS.com

Bithead

We have a stored procedure that allocates a control number based on current date and time (down to hundredths of a second). To verify that the number is not duplicated, it inserts this number into a DB2 table which has a unique index on this number. If a duplicate is detected (SQLCode = -803), the procedure will add 1 to the number and try again. It will continue this until it gets a +0 SQLCode (or it gets some other code or it times out).

We have a WebSphere application that invokes this routine to get a control number. When it is configured for multi-thread processing, it appears to be getting more than one response with the same control number.

Does anyone know what might be causing this?

kolusu · Posted: Thu Oct 20, 2005 11:27 am Post subject:

Bithead · Posted: Thu Oct 20, 2005 11:36 am Post subject:

It should wait. I have to determine if the problem is in the WebSphere application or the Stored Procedure. I have no access to the Java code at this time and so I must start by investigating the procedure. The problem occurs when multiple threads hit at the same time. Either the stored procedure is still in memory and other threads are picking up the results or the threads are picking up a dirty buffer somewhere.

I may add displays to the procedure to display the code added.

a_seshu · Posted: Fri Oct 21, 2005 3:56 pm Post subject:

Hi Bithead,

I feel waiting in a loop for 0 sql code when processing in multi threaded evironment with high volumes of concurrency is not a good approach of programming. Having said that, I have a suggestion for you. you can lock the table in your program before you want to update or insert into the table and then when you are done, you can unlock it. So that any parellel process which is trying to lock the table should have to wait until the previous lock is released.

EXEC SQL LOCK TABLE <TABLENAME> IN EXCLUSIVE MODE END-EXEC would work to lock it so that only one process at a time can have an exclusive control on the table and when you are done, you can issue a commit in your program. But then i think in your bind pamaeters you should have RELEASE(COMMIT).

Thanks,
Seshu.
_________________
I dont think I would ever stop learning. - Seshu.