Procedure Dependencies and Temporary Tables

phil@querytool.com · *nix forums beginner Joined: 01 Mar 2005 Posts: 3

I have been developing some queries which will list the dependencies of
Stored Procedures (this is with DB2/UDB v Cool

. In doing so, I have
noticed that dependencies are ignored if they occur in an insert into a
temporary table. For instance, if you have the following procedure:
CREATE PROCEDURE DBA.TEST
BEGIN
INSERT INTO SESSION.TEMP1 SELECT COUNT(*) FROM DBA.DEPARTMENT;
END
Then DBA.DEPARTMENT will not appear in the list of dependent objects
for this procedure. If you instead have (say) "INSERT INTO sometable
SELECT COUNT(*) FROM DBA.DEPARTMENT;" then DBA.DEPARTMENT will appear
in the dependency list.

Note that to get the dependency list I access SYSCAT.ROUTINEDEP with
the specific-name of the procedure - this gives me a package name, with
which I look up SYSCAT.PACKAGEDEP.

Perhaps, because temporary tables are not held in catalog, the insert
into the temporary table is ignored when DB2 is compiling the list of
dependent objects. Does anyone know whether this is the case, and
whether there is any way to get the list of dependent objects for
Stored Procedures such as these?

Thanks!
Phil Castle

Serge Rielau · *nix forums Guru Joined: 29 Apr 2005 Posts: 1583

phil@querytool.com wrote:

Google