RE: DB2 File in use error

Renato,

I'm not sure why you're getting the lock, however there is a logic error
in your program. Even at an isolation level of Repeatable Read (RR),
which is the highest isolation level, there is _no_ guarantee that
another process can't read the same row you've just read. 

If your task reads the current id number and then gets swapped out,
another task can also read the current id number, then you and the other
task will both think you have the highest number and, in technical
terms, BOOM. You need to turn the read/update ordering around. Do the
update first, then read the row to find out what the new currentID is,
and return that. 

For example: 
BEGIN 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE libtest.idtable
        SET id = id + 1
        WHERE tbname = table_name;

SELECT id INTO next_id
        FROM libtest.idtable
        WHERE tbname = table_name;

COMMIT WORK;

END 

Oh, and I would point out the evils of hard coding library names into
procs. When you move from test to production you'll have to change this
code from libtest. To libprod. (or whatever) Hope you're not public --
that would make the auditors oh so happy. <G>

-Walden

------------
Walden H Leverich III
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)