Lennart wrote:
> Serge Rielau wrote:
>> Lennart wrote:
>>> Lennart wrote:
>>>> I see a bunch of packages where valid <> 'Y'. What I cant figure out
>>>> is how to relate the package to a procedure, function or whatever.
>>>> Does anyone have a reference to share on the relationship between,
>>>> say syscat.packages and syscat.routines. If anyone have sql for this
>>>> I can live with that too :-)
>>>>
>>>
>>> Ooops, forgot:
>>>
>>> [db2inst1@wb-05 ~/nya/bin]$ db2level
>>> DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
>>> "SQL08026"
>>> with level identifier "03070106".
>>> Informational tokens are "DB2 v8.1.2.120", "s060801", "MI00163", and
>>> FixPak
>>> "13".
>>> Product is installed at "/opt/IBM/db2/V8.1".
>>>
>>> [db2inst1@wb-05 ~/nya/bin]$ uname -a
>>> Linux wb-05 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:54:53 EST 2006 i686
>>> i686 i386 GNU/Linux
>>>
>>> /Lennart
>> For SQL Procedures take a look at SYSCAT.ROUTINEDEP. It should link
>> the proc to the package.
>> SQL Functions and Trigger shave no packages. Nothing to worry about.
>> External procs and functions... tricky.
>> It's an n-m relationship coming out of the binary. Unless you have
>> meaning full bind file names...
>>
>
> Thanx Serge. I think something along the lines of:
>
> select
> substr(z.ROUTINESCHEMA,1,40),
> substr(z.ROUTINENAME,1,40),
> substr(y.PKGSCHEMA,1,8),
> substr(y.PKGNAME,1,8)
> from syscat.packages y
> inner join syscat.routinedep x
> on (x.BSCHEMA, x.BNAME) = (y.PKGSCHEMA, y.PKGNAME)
> inner join syscat.routines z
> on (x.ROUTINENAME) = (z.SPECIFICNAME)
> where y.valid <> 'Y'
>
> is what I'm looking for. For the future, is there some documentation on
> how different views, tables, etc are related to each other? I have not
> found anyone so far, but I might be looking in the wrong places.
Quest has these pretty "System Catalog" Posters for download:
http://www.quest.com/db2/

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab