By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

Packagename of a routine

P: n/a
V8.1.6

In order to check if a routine is valid or not, SysCat.Packages must be
checked, since SysCat.Routines is nearly always Valid = 'Y'. I chalked
it up to DB2's idiosyncrasies (of which there seems to be many) and
moved on.

However, in order to check SysCat.Packages, the packagename is
required, and that is not in SysCat.Routines. So, i checked the
newsgroups and found out that the first eight characters of
IMPLEMENTATION are the packagename. It's quirky, but it works.

Today, while trying to find out package dependencies, i checked
SysCat.RoutineDep. That works fine for FUNCTIONs, but PROCEDUREs simply
refer to themselves via their packagename. So, it finally hit me, and i
made a guess.

The reason why SysCat.Routines's Valid COLUMN is so useless, is that it
refers to the declaration of the PROCEDURE, or for FUNCTIONs since they
are not compiled. Since PROCEDUREs are compiled, it is the file on disk
that has the dependency, and that is in SysCat.Packages. The
declaration of the PROCEDURE, however, is only dependant on the file,
and as long as that is there, VALID will always be 'Y'.

Therefore, in order to get the filename, IMPLEMENTATION is not the
correct column to go to, rather it is SysCat.RoutineDep.BName.

Or specifically, for invalid PROCEDUREs:

SELECT \
VARCHAR(Routines.RoutineName, 18) Name \
FROM \
Syscat.Routines Routines, \
Syscat.RoutineDep RoutineDep, \
Syscat.Packages Packages \
WHERE \
-- Get the routine we want. \
Routines.RoutineSchema = <Schema> \
AND Routines.Origin = 'E' \
-- Join RoutineDep to get the filename. \
AND RoutineDep.RoutineSchema = Routines.RoutineSchema \
AND RoutineDep.RoutineName = Routines.RoutineName \
AND RoutineDep.BType = 'K' \
-- Join Packages to check validity. \
AND Packages.PkgSchema = RoutineDep.BSchema \
AND Packages.PkgName = RoutineDep.BName \
AND Packages.Valid = 'X' \

Does that make any sense?

--

One problem i have with this is that a BType of 'K' is undocumented.
And on IBM's website for the documentation
<URL:http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html>
the version 8.1 doc is a filename of 80, and the 8.2 docs is a filename
of 81. Both files did not mention 'K'.

B.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Brian Tkatch" <Ma***********@ThePentagon.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
V8.1.6

In order to check if a routine is valid or not, SysCat.Packages must be
checked, since SysCat.Routines is nearly always Valid = 'Y'. I chalked
it up to DB2's idiosyncrasies (of which there seems to be many) and
moved on.

However, in order to check SysCat.Packages, the packagename is
required, and that is not in SysCat.Routines. So, i checked the
newsgroups and found out that the first eight characters of
IMPLEMENTATION are the packagename. It's quirky, but it works.

Today, while trying to find out package dependencies, i checked
SysCat.RoutineDep. That works fine for FUNCTIONs, but PROCEDUREs simply
refer to themselves via their packagename. So, it finally hit me, and i
made a guess.

The reason why SysCat.Routines's Valid COLUMN is so useless, is that it
refers to the declaration of the PROCEDURE, or for FUNCTIONs since they
are not compiled. Since PROCEDUREs are compiled, it is the file on disk
that has the dependency, and that is in SysCat.Packages. The
declaration of the PROCEDURE, however, is only dependant on the file,
and as long as that is there, VALID will always be 'Y'.

Therefore, in order to get the filename, IMPLEMENTATION is not the
correct column to go to, rather it is SysCat.RoutineDep.BName.

Or specifically, for invalid PROCEDUREs:

SELECT \
VARCHAR(Routines.RoutineName, 18) Name \
FROM \
Syscat.Routines Routines, \
Syscat.RoutineDep RoutineDep, \
Syscat.Packages Packages \
WHERE \
-- Get the routine we want. \
Routines.RoutineSchema = <Schema> \
AND Routines.Origin = 'E' \
-- Join RoutineDep to get the filename. \
AND RoutineDep.RoutineSchema = Routines.RoutineSchema \
AND RoutineDep.RoutineName = Routines.RoutineName \
AND RoutineDep.BType = 'K' \
-- Join Packages to check validity. \
AND Packages.PkgSchema = RoutineDep.BSchema \
AND Packages.PkgName = RoutineDep.BName \
AND Packages.Valid = 'X' \

Does that make any sense?
Yes. I often use a simpler chunk of DDL to map PROCEDURE names to PACKAGE
names but the underlying concept is the same.
One problem i have with this is that a BType of 'K' is undocumented.
And on IBM's website for the documentation
<URL:http://www-306.ibm.com/software/data/db2/udb/support/manualsv8.html>
the version 8.1 doc is a filename of 80, and the 8.2 docs is a filename
of 81. Both files did not mention 'K'.


Well, the catalog tables are somewhat "internal" in nature -- but a 'K'
designation appears to mean that UNIQUE_ID in syscat.routinedep is set.

--
Matt Emmerton
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.