Have you ever wondered what tables and indexes a DB2 stored proc are using? How about if the proc has been rebound since the stats were last updated? Are there even stats for the table you’re querying? Luckily I work with a very talented application DBA (Fred Johnson) who put together the following query to tell you all these sorts of things. It’s quite long, but all you need to do is set your proc in the “FILTER_PARMS” “VALUES” section at the top of the query.
-------------------------------------------------------------------------
-- DB2 for AIX QUERY
--PROCS WITH DEPENDENT TABLES INDEXES CALLED_PROCS AND OTHER OBJECTS
--FLAG column shows possible stats issues. See FOOTNOTES
-------------------------------------------------------------------------
-- Directions: Change FILTER_PARMS,Cut and paste, run, examine output
-- More than 1 proc can be reviewed by adding addition lines
-------------------------------------------------------------------------
WITH FILTER_PARMS (ROUTINESCHEMA,ROUTINENAME) AS
(VALUES
('XX','PROC1_P')
,('XX','PROC2_P')
)
,ROUTINEDEP (STOREDPROC,TEXTX,OBJECTNAME,CARD,BIND_STATS_CREATE_TIME,FLAG,SORTCOL) AS
(SELECT RTRIM(X.ROUTINESCHEMA)||'.'||X.ROUTINENAME AS STOREDPROC ,
'PACKAGE' AS TEXTX ,RTRIM(P1.PKGSCHEMA)||'.'||P1.PKGNAME AS
OBJECTNAME ,' ' AS CARD
, 'BIND: '||CHAR(DATE(P1.LAST_BIND_TIME))||'*'||CHAR(TIME(P1.LAST_BIND_TIME))
AS BIND_STATS_CREATE_TIME
,CASE
WHEN P1.LAST_BIND_TIME < CURRENT_TIMESTAMP - 9 DAYS
THEN '*STALE REBIND ' ELSE ' '
END
||
CASE
WHEN P1.VALID = 'N' THEN '*PACKAGE INVALID '
WHEN P1.VALID = 'X' THEN '*PACKAGE INOPERATIVE '
ELSE '' END
AS FLAG
,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'1' AS SORTCOL
FROM SYSCAT.PACKAGES P1
INNER JOIN SYSCAT.ROUTINEDEP R
ON (P1.PKGNAME = R.BNAME
AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES X
ON (R.ROUTINENAME = X.SPECIFICNAME
AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
INNER JOIN FILTER_PARMS FP
ON (FP.ROUTINESCHEMA = X.ROUTINESCHEMA
AND FP.ROUTINENAME = X.ROUTINENAME)
UNION
--TABLES
SELECT ' ' AS STOREDPROC , CASE
WHEN P.BTYPE = 'A' THEN 'ALIAS'
WHEN P.BTYPE = 'B' THEN 'TRIGGER'
WHEN P.BTYPE = 'D' THEN 'SERVER DEF'
WHEN P.BTYPE = 'F' THEN 'PROC/FUNC'
WHEN P.BTYPE = 'I' THEN 'INDEX'
WHEN P.BTYPE = 'M' THEN 'FUNCTION MAP'
WHEN TAB.TYPE = 'N' THEN 'NICKNAME'
WHEN P.BTYPE = 'O' THEN 'PRIVILEGE DEP'
WHEN P.BTYPE = 'P' THEN 'PAGE SIZE'
WHEN P.BTYPE = 'R' THEN 'STRUCT TYPE '
WHEN P.BTYPE = 'S' THEN 'MQTABLE'
WHEN P.BTYPE = 'T' THEN 'TABLE'
WHEN P.BTYPE = 'U' THEN 'TYPED TABLE'
WHEN P.BTYPE = 'V' THEN 'VIEW'
WHEN P.BTYPE = 'W' THEN 'TYPED VIEW'
ELSE ' ?' END AS TEXTX ,RTRIM(P.BSCHEMA)||'.'||
P.BNAME AS OBJECTNAME ,RTRIM(CHAR(TAB.CARD)) AS CARD
,'STATS:'||COALESCE(CHAR(DATE(TAB.STATS_TIME))||'*'||CHAR(TIME(TAB.STATS_TIME)) ,'NO STATS')
AS BIND_STATS_CREATE_TIME
,CASE
WHEN TAB.CARD = -1
THEN '*NO TABLESTATS'
WHEN TAB.STATS_TIME > P1.LAST_BIND_TIME
THEN '*PROC BIND < STATS'
ELSE ' '
END
||
CASE
WHEN TAB.STATUS <> 'N'
THEN '*STATUS '||RTRIM(TAB.STATUS)
ELSE ''
END
AS FLAG
,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'2'||RTRIM(
P.BSCHEMA)||'.'||P.BNAME||'2' AS SORTCOL
FROM SYSCAT.PACKAGES P1
INNER JOIN SYSCAT.ROUTINEDEP R
ON (P1.PKGNAME = R.BNAME
AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES X
ON (R.ROUTINENAME = X.SPECIFICNAME
AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
INNER JOIN FILTER_PARMS FP
ON (FP.ROUTINESCHEMA = X.ROUTINESCHEMA
AND FP.ROUTINENAME = X.ROUTINENAME)
INNER JOIN SYSCAT.PACKAGEDEP P
ON (P1.UNIQUE_ID = P.UNIQUE_ID
AND P.PKGNAME = R.BNAME
AND P.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.TABLES TAB
ON (P.BSCHEMA = TAB.TABSCHEMA
AND P.BNAME = TAB.TABNAME
AND P.BTYPE IN ( 'T','N','S','U','W'))
UNION
--INDEXES
SELECT ' ' AS STOREDPROC , ' INDEX' AS TEXTX ,RTRIM(P.BSCHEMA)
||'.'||P.BNAME AS OBJECTNAME ,RTRIM(CHAR(IND.FULLKEYCARD)) AS CARD
,'STATS:'||COALESCE(CHAR(DATE(IND.STATS_TIME))||'*'||CHAR(TIME(IND.STATS_TIME)),'NO STATS')
AS BIND_STATS_CREATE_TIME
,CASE
WHEN IND.FULLKEYCARD = -1
THEN '*NO INDEXSTATS'
WHEN IND.STATS_TIME > P1.LAST_BIND_TIME THEN '*PROC BIND < STATS '
ELSE ''
END
||CASE
WHEN IND.STATS_TIME < TAB.STATS_TIME - 10 SECONDS
THEN '** IND.STATS_TIME < TAB.STATS_TIME'
ELSE '' END AS FLAG
,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'2'||RTRIM(IND.TABSCHEMA)||'.'||IND.TABNAME||'3'
AS SORTCOL
FROM SYSCAT.PACKAGES P1
INNER JOIN SYSCAT.ROUTINEDEP R
ON (P1.PKGNAME = R.BNAME
AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES X
ON (R.ROUTINENAME = X.SPECIFICNAME
AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
INNER JOIN FILTER_PARMS FP
ON (FP.ROUTINESCHEMA = X.ROUTINESCHEMA
AND FP.ROUTINENAME = X.ROUTINENAME)
INNER JOIN SYSCAT.PACKAGEDEP P
ON (P1.UNIQUE_ID = P.UNIQUE_ID
AND P.PKGNAME = R.BNAME
AND P.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.INDEXES IND
ON (P.BSCHEMA = IND.INDSCHEMA
AND P.BNAME = IND.INDNAME
AND P.BTYPE = 'I')
INNER JOIN SYSCAT.TABLES TAB
ON (IND.TABSCHEMA = TAB.TABSCHEMA
AND IND.TABNAME = TAB.TABNAME)
UNION
SELECT ' ' AS STOREDPROC , ' OTHER '||
CASE
WHEN XDEP.ROUTINETYPE = 'F' THEN 'FUNCTION '
WHEN XDEP.ROUTINETYPE = 'M' THEN 'METHOD'
WHEN XDEP.ROUTINETYPE = 'P' THEN 'STORED PROC'
ELSE ' ?' END AS TEXTX
,RTRIM(XDEP.ROUTINESCHEMA)||'.'||XDEP.ROUTINENAME AS OBJECTNAME
,' ' AS CARD
,'CREATED:'||CHAR(DATE(XDEP.CREATE_TIME))
AS BIND_STATS_CREATE_TIME
,CASE
WHEN XDEP.ROUTINETYPE = 'P'
THEN CASE
WHEN NOT EXISTS
(SELECT 1
FROM FILTER_PARMS FP
WHERE FP.ROUTINESCHEMA = XDEP.ROUTINESCHEMA
AND FP.ROUTINENAME = XDEP.ROUTINENAME)
THEN ','||'('||''''||RTRIM(XDEP.ROUTINESCHEMA)
||''''||','||''''||RTRIM(XDEP.ROUTINENAME)
||''''||')'
ELSE ' ' END
ELSE ' ' END AS FLAG --('CH','GET_NEXTID_P')
,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'3' AS SORTCOL
FROM SYSCAT.PACKAGES P1
INNER JOIN SYSCAT.ROUTINEDEP R
ON (P1.PKGNAME = R.BNAME
AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES X
ON (R.ROUTINENAME = X.SPECIFICNAME
AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
INNER JOIN FILTER_PARMS FP
ON (FP.ROUTINESCHEMA = X.ROUTINESCHEMA
AND FP.ROUTINENAME = X.ROUTINENAME)
INNER JOIN SYSCAT.PACKAGEDEP P
ON (P1.UNIQUE_ID = P.UNIQUE_ID
AND P.PKGNAME = R.BNAME
AND P.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES XDEP
ON (P.BNAME = XDEP.SPECIFICNAME
AND P.BSCHEMA = XDEP.ROUTINESCHEMA)
UNION
--OTHER
SELECT ' ' AS STOREDPROC , ' OTHER '||
CASE
WHEN P.BTYPE = 'A' THEN 'ALIAS'
WHEN P.BTYPE = 'B' THEN 'TRIGGER'
WHEN P.BTYPE = 'D' THEN 'SERVER DEF'
WHEN P.BTYPE = 'F' THEN 'PROC/FUNC'
WHEN P.BTYPE = 'I' THEN 'INDEX'
WHEN P.BTYPE = 'M' THEN 'FUNCTION MAP'
WHEN P.BTYPE = 'N' THEN 'NICKNAME'
WHEN P.BTYPE = 'O' THEN 'PRIVILEGE DEP'
WHEN P.BTYPE = 'P' THEN 'PAGE SIZE'
WHEN P.BTYPE = 'R' THEN 'STRUCT TYPE '
WHEN P.BTYPE = 'S' THEN 'MQTABLE'
WHEN P.BTYPE = 'T' THEN 'TABLE'
WHEN P.BTYPE = 'U' THEN 'TYPED TABLE'
WHEN P.BTYPE = 'V' THEN 'VIEW'
WHEN P.BTYPE = 'W' THEN 'TYPED VIEW'
WHEN P.BTYPE = 'Q' THEN 'Sequence object'
WHEN P.BTYPE = 'G' THEN 'Global temporary table'
ELSE ' ?'||RTRIM(P.BTYPE) END AS TEXTX
,RTRIM(P.BSCHEMA)||'.'||P.BNAME AS OBJECTNAME
,' ' AS CARD
, '' AS BIND_STATS_CREATE_TIME ,' '
AS FLAG ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'4' AS SORTCOL
FROM SYSCAT.PACKAGES P1
INNER JOIN SYSCAT.ROUTINEDEP R
ON (P1.PKGNAME = R.BNAME
AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
INNER JOIN SYSCAT.ROUTINES X
ON (R.ROUTINENAME = X.SPECIFICNAME
AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
INNER JOIN FILTER_PARMS FP
ON (FP.ROUTINESCHEMA = X.ROUTINESCHEMA
AND FP.ROUTINENAME = X.ROUTINENAME)
INNER JOIN SYSCAT.PACKAGEDEP P
ON (P1.UNIQUE_ID = P.UNIQUE_ID
AND P.PKGNAME = R.BNAME
AND P.PKGSCHEMA = R.ROUTINESCHEMA)
WHERE P.BTYPE NOT IN ( 'I','F','T','S','N','U','W')
)
SELECT STOREDPROC,TEXTX,OBJECTNAME,CARD,BIND_STATS_CREATE_TIME,FLAG
FROM ROUTINEDEP
ORDER BY SORTCOL
WITH UR;
-----------------------------------------------------------------------
-- FLAG column show possible stats mismatch where a
-- procs bind time < stats time for Tables/Indexes
--
-- Also review CARD, which shows row counts for tables based on stats
-- is that what you expect?
--
-- Flag column also generates values clause that you can cut and paste
-- into the values clause to review called procs using this query
------------------------------------------------------------------------
And a few things Fred suggested to be mindful of... "It should work with DB2luw and has been tested in AIX v9.5-10.2. There is one major caveat. The query assumes one proc has only one specific name i.e., it only works for non-overloaded procs. It also uses at least one deprecated column, ROUTINENAME in ROUTINEDEP. Changing the proc to use specific names wouldn’t be a big deal."
Have fun, and if you use this or have any suggested enhancements, please leave a comment below.
Please remember to subscribe to the newsletter to stay up to date!
You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...