Nested SQL not working the way the manuals say it should

Jim Hunt

Nested SQL not working the way the manuals say it should
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


This e-mail message (including any attachments) is intended for the exclusive use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary, confidential, and exempt from disclosure. If you are not the intended recipient, be aware that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and erase this e-mail immediately.

---------------------------------------------------------------------------------
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