Package associated with Table or Index

Do Park

Package associated with Table or Index
Hello

Is there anyone who has SQL statement to pick up package associated with
certain tables or indexes from DB2 catalog?

For example, I have table A. There are package associated with table A.
There is no link between them.


Thanks,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: Package associated with Table or Index
(in response to Do Park)
Do,
Actually, there is a link for the table to package information. This information is found in the SYSIBM>SYSPACKDEP.
SELECT DNAME, DCOLLID
FROM SYSIBM.SYSPACKDEP
WHERE BNAME = table
AND BQUALIFIER = tbcreator

The index information, you must have bound your package with EXPLAIN(YES), then you can query the plan_table for the ACCESSNAME(index name) you are looking for.
SELECT PROGNAME
FROM PLAN_TABLE
WHERE ACCESSNAME = index

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 1/5/04 4:20:25 PM >>>
Hello

Is there anyone who has SQL statement to pick up package associated with
certain tables or indexes from DB2 catalog?

For example, I have table A. There are package associated with table A.
There is no link between them.


Thanks,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Package associated with Table or Index
(in response to Dave Nance)
Do,

You should be able to see these links from
SYSIBM.SYSPACKDEP
Please advise if this is not what you are looking for.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Do Park <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/05/2004 04:20 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Package associated with Table or Index


Hello

Is there anyone who has SQL statement to pick up package associated with
certain tables or indexes from DB2 catalog?

For example, I have table A. There are package associated with table A.
There is no link between them.


Thanks,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of
the office, send the SET DB2-L NO MAIL command to
[login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Alice Frus

Re: Package associated with Table or Index
(in response to Philip Sevetson)
Do you have a third party vendor product like Candle ?



"Do Park"
<[login to unmask email] To:
[login to unmask email]
COM> cc:
Sent by: "DB2 Subject: Package associated
with Table or Index
Data Base
Discussion List"
<[login to unmask email]
ORG>
01/05/2004 03:20
PM
Please respond to
"DB2 Database
Discussion list
at IDUG"




Hello

Is there anyone who has SQL statement to pick up package associated with
certain tables or indexes from DB2 catalog?

For example, I have table A. There are package associated with table A.
There is no link between them.


Thanks,
Do.

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm





------------------------------------------------------------------------------
**********
The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of the
addressee. Unauthorized use, disclosure, distribution or copying is strictly
prohibited and may be unlawful. If you have received this communication in
error, please notify the sender immediately at (312)653-6000 in Illinois;
(972)766-6900 in Texas; or (800)835-8699 in New Mexico.
**********
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ford Wong

Re: Package associated with Table or Index
(in response to Alice Frus)
Do,

For static packages you should look at SYSIBM.SYSPACKDEP. Here is an SQL which
I use to build rebind statements for all packages which use BNAME=xxxx% and BQUALIFIER = (xxxxx,xxxxxx) :

SELECT 'REBIND PACKAGE (' ||
STRIP(SUBSTR(DCOLLID,1,8)) ||
'.' ||
STRIP(DNAME,T,' ') ||
CASE VERSION
WHEN ''
THEN ''
ELSE '.(' || VERSION || ')'
END
|| ')'
FROM SYSIBM.SYSPACKDEP,
SYSIBM.SYSPACKAGE
WHERE (
BNAME LIKE 'UG66%'
OR BNAME LIKE 'UG85%'
OR BNAME LIKE 'OR51%'
OR BNAME LIKE 'OR52%'
OR BNAME LIKE 'AD03%'
OR BNAME LIKE 'CL13%'
OR BNAME LIKE 'CL32%'
OR BNAME LIKE 'SA29%'
)
AND BQUALIFIER IN ('TC2028', 'TC202I')
AND DCOLLID = COLLID
AND DNAME = NAME
AND DCONTOKEN = CONTOKEN
GROUP BY DCOLLID, DNAME, VERSION
ORDER BY 1

You will have to modify as needed. Hope this helps.

Ford

----- Original Message -----
From: Do Park <[login to unmask email]>
Date: Monday, January 5, 2004 2:20 pm
Subject: Package associated with Table or Index

> Hello
>
> Is there anyone who has SQL statement to pick up package
> associated with
> certain tables or indexes from DB2 catalog?
>
> For example, I have table A. There are package associated with
> table A.
> There is no link between them.
>
>
> Thanks,
> Do.
>
> -------------------------------------------------------------------
> --------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html.
> From that page select "Join or Leave the list". If you will be out
> of the office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Do Park

Re: Package associated with Table or Index
(in response to Ford Wong)
Thank you so much David, Phil, and Ford.
We use DB2 tool that shows the package information. I was just wondering
how to use SQL to pick up.

Best Regards,
Do.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm