We've just migrated, in our development environment, an 8.2 instance
to 9.7. The developers started to test their apps against the 9.7
database, and a proc that worked in 8.2 isn't working in 9.7. Here's
the proc.

CREATE PROCEDURE DB2ADMIN.SP_TIER_POINT_SNAPSHOT()
SPECIFIC DB2ADMIN.SQL081217070527800
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN

if exists (select * from syscat.tables where tabname =
'TEMP_PATRON_ACCOUNT') then
drop table db2admin.temp_patron_account;
end if;

create table db2admin.temp_patron_account(
acct_id integer not null,
YTD_TIER_LEVEL_POINTS integer not null,
YTD_NON_BONUSABLE_POINTS integer not null);

insert into db2admin.temp_patron_account
select acct_id, YTD_TIER_LEVEL_POINTS, YTD_NON_BONUSABLE_POINTS
from db2admin.patron_account a, db2admin.patron b
where a.ptrn_id=b.ptrn_id
and b.PTRN_STAT_CODE <> 'ARCHIVED';
end
;

When run it returns a SQL0910N error.

Does the proc violate some rule that was introduced between 8.2 and
9.7?

What would be necessary to fix it?

Thanks.

"Richard" <rmcgorman (AT) gmail (DOT) com> wrote

Do you expect us to memorize all the DB2 error codes? It would be useful if
you could post what the DB2 doc says about this error.

I can do that. :-) The message exists in the 8.2 documentation, so
the rules for raising it
must have changed in a later release.

Here's what the 9.7 manual says. I only left the parts that seem
relevant to me. In case I'm wrong,
here's a link to the manual.

http://public.dhe.ibm.com/ps/product...-db2m2e970.pdf


SQL0910N The SQL statement cannot access an object on which a
modification is pending.

Explanation: The application program attempted to access an object
within the same unit of
work in which either:

The application program issued a DROP against the object or a
related
object (for example, an index for a table)

The application program issued a statement against the object that
added or
dropped a constraint.

The application program issued a DROP TRIGGER or CREATE TRIGGER
statement
that affected the object, either directly or indirectly.

The application program issued a ROLLBACK TO SAVEPOINT statement,
which
placed the object in the modification pending state.

The application program issued a statement that caused all rows of
a NOT LOGGED
created temporary table or declared temporary table to be deleted.

<snip>

User response:

Modify the application program so there is no attempt to access an
object within the same
unit of work in which the modification was made. In general, it is
better to isolate Data
Definition Language (DDL) statements in a different unit of work than
Data Manipulation
Language (DML) statements that access the same objects.

The unit of work must be committed or rolled back before the statement
that failed can
be processed successfully. If the committed modification caused an
object to be dropped,
then the object may have to be recreated in order for the failing SQL
statement to be
processed successfully.

If the object is modified within a SAVEPOINT, modify the application
program so that it
does not attempt to access the object after the application issues a
ROLLBACK TO
SAVEPOINT statement. Any cursor that accesses the modified object and
is open at
the time of the ROLLBACK TO SAVEPOINT will not be accessible. Modify
the
application so that it closes the cursor.

If an insert, delete or update statement involving a NOT LOGGED
created temporary
table or declared temporary table fails, then all rows in that table
are deleted. Any
cursor that is open against that table at the time of the failure will
not be accessible
and should be closed by the application.

<snip>


We tried a few different things, both of which fixed the problem, but
it's my confusion on
units of work that has me looking for answers.

For our first attempt we removed the "drop table" and put the "create
table" inside an
"if not exists" statment. After the "end if" we put a "delete from
db2admin.temp_patron_account".

That made it work.

We also tried just putting a savepoint after the "create table"
statement.

That made it work. It's not clear to me why that is. Savepoints
don't end
a unit of work, only a commit or rollback?

I didn't want to put a commit after the create table because the
caller may have their
own transaction going that I don't want to affect.

Is putting the savepoint an appropriate response? There may be a
better solution if
we we're writing this app from scratch, but we're just trying to get
it to play nice with
9.7. This particular way of working with tables "drop and recreate"
may be wide-
spread in this app, and others.

Thanks.

We tried recompiling the proc, even though I don't see a step
recommending that in the
V9.7 upgrade roadmap, and it compiles and runs fine. Hmmmmmm.