Question & Answer
Question
How do you determine what database objects a specific SQL or embedded C routine is dependent on?
Answer
Embedded C
$ db2 "select pkgname from syscat.packages where pkgschema='MAJASON'"
PKGNAME
--------------------------------------------------------------------------------------------------------------------------------
P1504399997
P836291499
SP_DEL
SQL
SQL routines have dependencies on table and other objects they access. A SQL routine may also be dependent on another routine called within it (referred to as a nested call). When a SQL routine is created a package is created for the routine and the routine is dependent on this package. DB2 has a view which lists the routine dependencies called SYSCAT.ROUTINEDEP. This view will return one row for every dependency for a routine identified by its SPECIFCNAME. The BTYPE column returns the type of object the routine is dependent on. The BNAME column returns the name of the dependent object. If the dependency is a routine, the BNAME will contain the SPECIFICNAME of the routine and the BTYPE will be a “F”.
The SPECIFICNAME can be mapped to the routine name (which is used to call the routine) by querying the SYSCAT.ROUTINES view. The following query will return the routine name, routine schema, specificname, dependency type and dependency name for all routines which have dependencies.
select substr(r.routinename,1,20) as routinename, substr(d.routineschema,1,20) as routineSchema, substr(d.specificname,1,20) as specificname, btype, substr(bname,1,20) as bname from syscat.routinedep d, syscat.routines r where r.specificname=d.specificname
If you wish to get information on a specific routine you can use the following query to locate the dependencies by routinename.
select substr(r.routinename,1,20) as routinename, substr(d.routineschema,1,20) as routineschema, substr(d.specificname,1,20) as specificname, btype, substr(bname,1,20) as bname from syscat.routinedep d, syscat.routines r where r.specificname=d.specificname and r.routinename='<Routine Name>'
The following is the output from the above query for the sample routine OUT_AVERAGE
ROUTINENAME ROUTINESCHEMA SPECIFICNAME BTYPE BNAME
------------------ ----------------- ------------------ ----- ------------------
OUT_AVERAGE DB2USER SQL131211105943400 K P1855237359
OUT_AVERAGE DB2USER SQL131211105943400 T STAFF
OUT_AVERAGE DB2USER SQL131211105943400 F SQL131211105943200
The Object type identified by the BTYPE column is documented in the SYSCAT.ROUTINEDEP Infocenter Page. Below is the information from the 10.1 page.
A = Table alias
B = Trigger
C = Column
F = Routine
G = Global temporary table
H = Hierachy table
K = Package
L = Detached table
N = Nickname
O = Privilege dependency on all subtables or subviews in a table or view hierarchy
Q = Sequence
R = User-defined data type
S = Materialized query table
T = Table (not typed)
U = Typed table
V = View (not typed)
W = Typed view
X = Index extension
Z = XSR object
Start of change m = Module End of change
q = Sequence alias
u = Module alias
v = Global variable
* = Anchored to the row of a base table
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21659814