Side affect of stored procedure changes with version 8.2

[martynh]

I have just set up DB2 8.1 FP7 which is apparently the same as DB2 8.2. One of the changes is that you no longer require a C compiler to create stored procedures. There is no mention of functional changes, but we have a situation where an implicit change has occurred. The situation can be outlined as follows:

We have a number of tables with identity columns. Generally, these increment automatically, but occasionally values are inserted directly (during bulk load). We have a stored procedure that alters any given table to set the identity column to one greater than the currently highest value in the table. The logic in the procedure is:

declare cursor c1
c1 selects the highest value in the identity column
alter table to set identity column to next highest value+1
close cursor c1

Previously this worked fine (DB2 8.1.6). Now (DB2 8.1.7 aka 8.2) the alter statement fails with a -951 i.e. cannot alter a table which is locked. My stored procedure logic is of course slightly wrong - the error code is prefectly resonable. The logic should be:

declare cursor c1
c1 selects the highest value in the identity column
close cursor
alter table to set identity column to next highest value

I wonder if the C compiler optimised my code and avioded the error? However, I guess that there are other situations where the change to negate the need for a C compiler will affect people in similar subtle ways.

Martyn Hodgson
martyn.hodgson@iipay.com