473,378 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 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 5699
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: SQLServer007 | last post by:
25 more days until the "get it free" promotion runs out for xSQL Object (you can get it from http://www.x-sql.com) Here are just some of the great features packed in the product: - Compare SQL...
6
by: dwilliams | last post by:
Our organization has implemented an ASP.net application with an n-tiered architecture, made up of a business/data layer that calls T-SQL Stored procedures. We are attempting to better manage the...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
5
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored...
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
2
by: Praveen_db2 | last post by:
Hi All DB2 8.1.3, Windows Is there any method to find the routines dependant on any particular routine. Say, I have a stored procedure PROC1 which calls 2 stored procedures and is called by 3...
8
by: rick | last post by:
Hi I m trying to move only stored procedures from one database to another and also onto a database on another server, I tried db2 -x "select text from syscat.procedures where procschema =...
1
by: dherbstemail-ng | last post by:
I can get the SQL Server 2005 CommandNotification cache dependency to work with inline SQL. It works in a SqlDataSource as well as with the page OutputCache when making ADO.NET calls. Next I...
11
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.