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.