MVSFORUMS.com

mahesh_chv · Beginner Joined: 04 Aug 2005 Posts: 41 Topics: 16

Hi ,

Is there any way where in we can find out the name of the program which is accessing a given DB2 table by quering the DB2 Catalog tables.

Thanks,
Mahesh

bauer · Posted: Mon Jun 16, 2008 2:19 am Post subject:

Hi mahesh_chv,

you can find out the name of packages / plans which are accessing a given table.

regards,
bauer

mahesh_chv · Beginner Joined: 04 Aug 2005 Posts: 41 Topics: 16

Hi Bauer,

when I queried SYSIBM.SYSPLAN table I got only the plan name ( nothing but the program name as per the convention at our shop) but I havent got the corresponding DB2 table.

Is there any Catalog tables which gives me both the plan ( or program ) and the corresponding DB2 table ?

Thanks,
Mahesh

Nic Clouston · Posted: Mon Jun 16, 2008 3:19 am Post subject:

To find out what uses which entity (table, view, alias, synonym, table space or index):

SELECT * FROM SYSIBM.SYSPLANDEP WHERE BNAME='entity name';
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

bauer · Posted: Mon Jun 16, 2008 4:20 am Post subject:

mahesh_chv,

there is no table which gives planname (or packagename) and coressponding program name.

bauer

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

query SYSIBM.SYSPACKDEP (If the DBRM bound to PLAN directly, query SYSPLANDEP)

SELECT DCOLLID, DNAME, DCONTOKEN
FROM SYSIBM.SYSPACKDEP
WHERE BNAME = your tb name
AND BQUALIFIER = your tb owner

Eric.C.Bakke · Posted: Mon Jul 14, 2008 12:47 pm Post subject:

SYSPLANDEP and SYSPACKDEP only records static SQL dependancies. So if a program has dynamic SQL accessing the table, you'll not find it in these catalog tables unless it also has static sql for table name being queried. If a table has strictly dynamic queries being run and they are remote, you need a 3rd party monitor tool to capture this sql as it occurs to determine what process is accessing your table.

It is a "best practice" for the DBRM name to match the program name and if your shop follows this as a standard, then you can query the SYSIBM.SYSSTMT (for plans with embedded DBRMs). If your shop uses packages, then query SYSIBM.SYSPACKSTMT to locate all sql that has your table name. The catch is, sql stmts are often broken into multiple (SEQNO rows) in these tables so piecing together a sql stmt in a searchable line requires you write a program or use a 3rd-party utility to query the table and export the SQL statments into a file with a single record per statment that can then be searched for the table name being researched.

If you are running DB2 V8 or higher in NFM, the STMT column is in UNICODE (if the program was pre-compiled in NFM mode) so keep that in mind when querying these tables.
_________________
Eric C. Bakke
Senior DB2 DBA

Terry_Heinze · Posted: Mon Jul 14, 2008 1:26 pm Post subject:

SYSTABAUTH will tell you the users and types of access for tables and views.
_________________
....Terry

Eric.C.Bakke · Posted: Mon Jul 14, 2008 5:34 pm Post subject:

SYSTABAUTH will tell you plans and packages that have been bound to a table using static SQL. I don't believe an entry will be made for dynamic SQL but I could be wrong. Plus, a table that is accessed online via remote connections and dynamic sql wouldn't have a plan or package. So, determining a complete picture of what processes access a given table is not possible looking strickly at the DB2 Catalog.
_________________
Eric C. Bakke
Senior DB2 DBA