473,327 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

syscat.package.valid <> 'Y', how to figure out which object thatis related to package?


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
Apr 13 '07 #1
10 4950
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
Apr 13 '07 #2
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
Apr 13 '07 #3
On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
<er******************@gmail.comwrote:
>
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.
Apr 13 '07 #4
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

Apr 13 '07 #5
Brian Tkatch wrote:
On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
<er******************@gmail.comwrote:
>>
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
Apr 13 '07 #6
On Fri, 13 Apr 2007 20:54:35 +0200, Lennart
<er******************@gmail.comwrote:
>Brian Tkatch wrote:
>On Fri, 13 Apr 2007 16:16:51 +0200, Lennart
<er******************@gmail.comwrote:
>>>
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.
Apr 13 '07 #7
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

Apr 13 '07 #8
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
Apr 13 '07 #9
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
Apr 14 '07 #10
Thanks for pointing it out. I've fired of a note to them.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 14 '07 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Eshrath | last post by:
Hi, What I am trying to do: ======================= I need to form a table in html using the xsl but the table that is formed is quite long and cannot be viewed in our application. So we are...
2
by: Donald Firesmith | last post by:
I am having trouble having Google Adsense code stored in XSL converted properly into HTML. The <> unfortunately become &lt; and &gt; and then no longer work. XSL code is: <script...
0
by: Wolfgang Schwanke | last post by:
Dear usenet, I'm having the following small problem. I've been ask to add some Quicktime panoramas to a website. The author of the panoramas has made two versions of each: One in MOV format,...
11
by: Scott Brady Drummonds | last post by:
Hi, everyone, I've checked a couple of on-line resources and am unable to determine how reinterpret_cast<> is different from static_cast<>. They both seem to perform a compile-time casting of...
3
by: | last post by:
I have been researching articles on google on how to create a simple RSS feed that sucks <title><blurb><link><date> out of a sql server 2000 database via an aspx page. I know it has to be pushed...
9
by: Simple Simon | last post by:
Java longs are 8 bytes. I have a Java long that is coming in from the network, and that represents milliseconds since Epoch (Jan 1 1970 00:00:00). I'm having trouble understanding how to get it...
2
by: brzozo2 | last post by:
Hello, this program might look abit long, but it's pretty simple and easy to follow. What it does is read from a file, outputs the contents to screen, and then writes them to a different file. It...
3
by: ajay2552 | last post by:
Hi, I have a query. All html tags start with < and end with >. Suppose i want to display either '<' or '>' or say some text like '<Company>' in html how do i do it? One method is to use &lt,...
44
by: Zytan | last post by:
The docs for List say "The List class is the generic equivalent of the ArrayList class." Since List<is strongly typed, and ArrayList has no type (is that called weakly typed?), I would assume...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.