SEO

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 :-)


/Lennart

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

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...

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

On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
<erik.lennart.jonsson@gmail.com> 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 :-)
>
>
>/Lennart

SUBSTR(Routines.Implementation, 1, 8) = Packages.PkgName

Or:

SELECT
CASE Routines.RoutineType
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
END,
VARCHAR(Routines.RoutineSchema, 18) Schema,
VARCHAR(Routines.RoutineName, 18) Name
FROM
SYSCAT.Routines Routines,
SYSCAT.Packages Packages
WHERE
Routines.RoutineSchema = Packages.PkgSchema
AND Routines.Origin = 'E'
AND Packages.PkgName = SUBSTR(Routines.Implementation, 1, 8)
AND Packages.Valid = 'X'

B.

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.


/Lennart

Brian Tkatch wrote:
> On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
> <erik.lennart.jonsson@gmail.com> 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 :-)
>>
>>
>>/Lennart
>
> SUBSTR(Routines.Implementation, 1, 8) = Packages.PkgName
>

Thanx Brian. However, this does not seem to work in my case. If look at
the routines.implementation:

[db2inst1@wb-03 ~/lelle/invalid_packages]$ db2 "select
SUBSTR(Implementation, 1, 8), count(1) from syscat.routines group by
SUBSTR(Implementation, 1, 8)"

1 2
-------- -----------
db2admcm 1
db2admin 1
db2am!am 8
db2clifn 131
db2dbapp 48
db2dbrou 18
db2dspro 17
db2envin 3
db2jarsp 8
db2lkfun 2
db2load! 1
db2msg!s 3
db2polic 4
db2psmdr 10
db2pvm!g 1
db2pvm!p 77
db2pvm!s 1
db2schem 12
db2stmg! 4
db2udp!g 2
db2udp!p 2
db2udp!r 1
SYSIBM.T 1
- 40

The packages.pkgname on the other hand all begin with a capital letter:

[db2inst1@wb-03 ~/lelle/invalid_packages]$ db2 "select
substr(pkgname,1,1), count(1) from syscat.packages group by
substr(pkgname,1,1)"

1 2
- -----------
D 4
P 77
S 111

I'm only interested i SQL procedures, so I think the hint by Serge will
work in my case.

Thanks anyway


/Lennart

On Fri, 13 Apr 2007 20:54:35 +0200, Lennart
<erik.lennart.jonsson@gmail.com> wrote:

>Brian Tkatch wrote:
>> On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
>> <erik.lennart.jonsson@gmail.com> 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 :-)
>>>
>>>
>>>/Lennart
>>
>> SUBSTR(Routines.Implementation, 1, 8) = Packages.PkgName
>>
>
>Thanx Brian. However, this does not seem to work in my case. If look at
>the routines.implementation:
>
>[db2inst1@wb-03 ~/lelle/invalid_packages]$ db2 "select
>SUBSTR(Implementation, 1, 8), count(1) from syscat.routines group by
>SUBSTR(Implementation, 1, 8)"
>
>1 2
>-------- -----------
>db2admcm 1
>db2admin 1
>db2am!am 8
>db2clifn 131
>db2dbapp 48
>db2dbrou 18
>db2dspro 17
>db2envin 3
>db2jarsp 8
>db2lkfun 2
>db2load! 1
>db2msg!s 3
>db2polic 4
>db2psmdr 10
>db2pvm!g 1
>db2pvm!p 77
>db2pvm!s 1
>db2schem 12
>db2stmg! 4
>db2udp!g 2
>db2udp!p 2
>db2udp!r 1
>SYSIBM.T 1
>- 40
>
>The packages.pkgname on the other hand all begin with a capital letter:
>
>[db2inst1@wb-03 ~/lelle/invalid_packages]$ db2 "select
>substr(pkgname,1,1), count(1) from syscat.packages group by
>substr(pkgname,1,1)"
>
>1 2
>- -----------
>D 4
>P 77
>S 111
>
>I'm only interested i SQL procedures, so I think the hint by Serge will
>work in my case.
>
>Thanks anyway
>
>
>/Lennart

Sorry i couldn't help. The query i sent works for me though.

B.

Brian Tkatch wrote:
[...]
> Sorry i couldn't help. The query i sent works for me though.
>

No problem, hopefully someone can point out some references on this subject.


/Lennart

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

Serge Rielau wrote:

> Quest has these pretty "System Catalog" Posters for download:
> http://www.quest.com/db2/

Well - maybe not (or maybe so, are they that smart ?)

We're Sorry! What You're Looking For Isn't Here.
Some people call it a "Page Not Found" error. Others refer to it as a
404 error. Whatever you call it, we know it can be frustrating.

Unfortunately, the page you were looking for
(http://www.quest.com/quest_central/s...db2sc_luw9.zip)
doesn't appear to be here. We're sorry for the trouble.

>
> Cheers
> Serge