471,595 Members | 1,097 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,595 software developers and data experts.

stored procedures dependencies

Hi all,

I'm running db2 9 on windows.
I'm looking for a query to find dependencies between stored
procedures.
For instance if I have a procedure P that calls p1 and p2, I'd like to
know that.
I was looking at syscat.routinedep and syscat.packagedep but they
don't seem to do the job.
Even if P needs p1 and p2 to compile, it is still listed only once in
the syscat.routindep catalog. Apparently this view is designed to keep
the dependencies between routines and packages rather that
dependencies between routines
Also, the syscat.packagedep show only dependencies to views, tables,
etc, but not to other packages.
Is there any way I can query this? Thanks!
Oct 28 '08 #1
2 5587
On Oct 28, 11:23*pm, "Jon.Hakki...@gmail.com" <Jon.Hakki...@gmail.com>
wrote:
Hi all,

I'm running db2 9 on windows.
I'm looking for a query to find dependencies between stored
procedures.
For instance if I have a procedure P that calls p1 and p2, I'd like to
know that.
I was looking at syscat.routinedep and syscat.packagedep but they
don't seem to do the job.
Even if P needs p1 and p2 to compile, it is still listed only once in
the syscat.routindep catalog. Apparently this view is designed to keep
the dependencies between routines and packages rather that
dependencies between routines
Also, the syscat.packagedep show only dependencies to views, tables,
etc, but not to other packages.
Is there any way I can query this? Thanks!
Hi Jon,

Package for routine P will depend on routines P1 and P2.
So you have to find that package and look at all routine instances
which this package depends on:

SELECT C.ROUTINESCHEMA, C.ROUTINENAME
FROM SYSCAT.ROUTINES R
JOIN SYSCAT.ROUTINEDEP D ON R.ROUTINESCHEMA=D.ROUTINESCHEMA AND
R.SPECIFICNAME=D.SPECIFICNAME
JOIN SYSCAT.PACKAGEDEP P ON P.PKGSCHEMA=D.BSCHEMA AND
P.PKGNAME=D.BNAME
JOIN SYSCAT.ROUTINES C ON P.BSCHEMA=C.ROUTINESCHEMA AND
P.BNAME=C.SPECIFICNAME
WHERE R.ROUTINESCHEMA='your_schema' and R.ROUTINENAME='P' AND
D.BTYPE='K'
AND P.BTYPE='F'

Hope this help.

Sincerely,
Mark B.
Oct 29 '08 #2
On Oct 29, 6:09*am, 4.s...@mail.ru wrote:
On Oct 28, 11:23*pm, "Jon.Hakki...@gmail.com" <Jon.Hakki...@gmail.com>
wrote:
Hi all,
I'm running db2 9 on windows.
I'm looking for a query to find dependencies between stored
procedures.
For instance if I have a procedure P that calls p1 and p2, I'd like to
know that.
I was looking at syscat.routinedep and syscat.packagedep but they
don't seem to do the job.
Even if P needs p1 and p2 to compile, it is still listed only once in
the syscat.routindep catalog. Apparently this view is designed to keep
the dependencies between routines and packages rather that
dependencies between routines
Also, the syscat.packagedep show only dependencies to views, tables,
etc, but not to other packages.
Is there any way I can query this? Thanks!

Hi Jon,

Package for routine P will depend on routines P1 and P2.
So you have to find that package and look at all routine instances
which this package depends on:

SELECT C.ROUTINESCHEMA, C.ROUTINENAME
FROM SYSCAT.ROUTINES R
JOIN SYSCAT.ROUTINEDEP D ON R.ROUTINESCHEMA=D.ROUTINESCHEMA AND
R.SPECIFICNAME=D.SPECIFICNAME
JOIN SYSCAT.PACKAGEDEP P ON P.PKGSCHEMA=D.BSCHEMA AND
P.PKGNAME=D.BNAME
JOIN SYSCAT.ROUTINES C ON P.BSCHEMA=C.ROUTINESCHEMA AND
P.BNAME=C.SPECIFICNAME
WHERE R.ROUTINESCHEMA='your_schema' and R.ROUTINENAME='P' AND
D.BTYPE='K'
AND P.BTYPE='F'

Hope this help.

Sincerely,
Mark B.
Great, it works like a charm, thanks a lot!!
Oct 29 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
5 posts views Thread by Tim Marshall | last post: by
2 posts views Thread by Praveen_db2 | last post: by
8 posts views Thread by rick | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.