How can I achieve a pessimistic lock in a DB2 sql stored procedure?
Can I start a transaction with serializable isolation level?
how?

Thank you,
Dan

If you want a pessimistic lock on data that you will eventually update, use the FOR UPDATE clause on the select.
I am not sure what you mean by serializable, but you can use the "WITH RR USE AND KEEP EXCLUSIVE LOCKS". For further info:

http://publib.boulder.ibm.com/infoce...n/r0000879.htm

Andy

Thanks Andy.
Just a question: how can I span the lock over multiple commands (for example in a stored proc that executes a few queries and calls some other stored procs) ?

Locks will stay until either a COMMIT or ROLLBACK.

Andy

Cheers mate!

can you help me with this one as well please:

what if instead of lock for update I want to lock the whole table for an insert?

Something like:

declare seqNo INT;
select sequence_number into seqNo
from myTable
order by sequence_number desc
fetch first 1 row only;
set seqNo = seqNo + 1;
...do more stuff here ...
insert into myTable (sequence_number) VALUES (seqNo);


but with a lock around it so that I don't get concurrency issues?

Try this:

select * from final table (insert into myTable (sequence_number) VALUES ((select max(sequence_number) + 1 from myTable));
commit;

This might work also:

declare cursor1 cursor with hold for select max(sequence_number) from myTable for update;
declare seqNo INT;

open cursor1;
fetch cursor1 into seqNo;

set seqNo = seqNo + 1;
...do more stuff here ...
insert into myTable (sequence_number) VALUES (seqNo);

close cursor1;

Andy

Thanks guys, will try