Nested SQL allowing recursive access to same table

Jim Hunt

Nested SQL allowing recursive access to same table
We are DB2 V7.1 running on z/OS 1.4.

According to the Application Programming and SQL Guide:

------------------------------------------------------------------
“Restrictions for INSERT, UPDATE, and DELETE Statements:

When you execute an INSERT, DELETE, or UPDATE statement on a table, you
cannot access that table from a user-defined function or stored procedure
that is at a lower level of nesting.

For example, suppose that you execute this SQL statement at level 1 of
nesting:

DELETE FROM T1 WHERE UDF3(T1.C1) = 3;

You cannot execute this SELECT statement at a lower level of nesting:

SELECT * FROM T1;”
------------------------------------------------------------------

However -- on our system at least -- DB2 does not appear to be enforcing
this rule. We have just tested a batch COBOL program that does an UPDATE
to a table, which fires an “after update” trigger, which in turn calls a
COBOL stored procedure that does both a SELECT and an UPDATE against the
very same table, successfully and without receiving any SQLSTATE errors.

My question – is this a feature or a bug? I notice that the wording in
the V8 version of this manual hasn’t changed, so I suspect that DB2 simply
isn’t enforcing a rule that it is supposed to, and therefore we should
expect this behavior to change (i.e. be “fixed”) at some future date
even though we ourselves would prefer that it be “legal” to do what this
transaction is doing.

Has anyone else had any experience with this issue?

Thanks,

Jim Hunt
Manager, Database Services
Generali USA Life Reassurance Co.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm