zOs/SQL/PLANCOUN
$#@ $*( show all plan_table in a dbSystem
including number of columns / number of rows
plus facultativ tables
$*)
call sqlConnect dbol
$<>
$<=[
select strip(value(p.creator, v.creator, d.creator, u.creator)) cr
, p.name pTb, p.colcount pCols, v.name vTb, d.name dTb
, u.name uTb
from (select * from sysibm.sysTables q
where q.type = 'T' and q.name = 'PLAN_TABLE') p
left outer join (select * from sysibm.sysTables q
where q.type = 'T' and q.name = 'DSN_VIRTUAL_INDEXES') v
on p.creator = v.creator
left outer join (select * from sysibm.sysTables q
where q.type = 'T' and q.name='DSN_STATEMENT_CACHE_TABLE') d
on p.creator = d.creator
left outer join (select * from sysibm.sysTables q
where q.type = 'T' and q.name='DSN_USERQUERY_TABLE') u
on p.creator = u.creator
order by 1
$] call sqlSel
$|
st = ''
$forWith i $@=[
$-[st$] select '$CR', $PCOLS pCols, count(*) planCnt
, max(date(explain_time)) planMax
$@[ if $VTB == m.sqlNull then $@=[
, cast( null as int) virtInd
$] else $@=[
, ( select count(*) from $CR.$VTB ) virtInd
$]
if $DTB == m.sqlNull then $@=[
, cast( null as int) dynCac
$] else $@=[
, ( select count(*) from $CR.$DTB ) dynCac
$]
if $UTB == m.sqlNull then $@=[
, cast( null as int) usQuer
$] else $@=[
, ( select count(*) from $CR.$UTB ) usQuer
$] $]
from $CR.PLAN_TABLE
$@ st = 'union all'
$] $|
call sqlStmts
$#out 20150223 08:12:23
COL1 PLANCNT VIRTIND USQUER
PCOLS PLANMAX DYNCAC
COL1 PC PLAN PLANMAX VIR DYN USQ
A540769 66 2701 09.12.2014 0 0 0
CMNBATCH 66 2701 09.12.2014 --- --- ---
DSN8BQRY 64 0 --- --- --- 0
S100006 66 0 --- --- --- ---
S100447 66 540 22.03.2012 --- --- ---
S103931 66 0 --- --- --- ---
SYSIBM 64 0 --- --- --- ---
7 rows fetched: select 'A540769', 66 pCols, count(*) planCnt , max(date(ex...
$#out 20150223 08:04:30
COL1 PLANCNT VIRTIND USQUER
PCOLS PLANMAX DYNCAC
COL1 PC PLAN PLANMAX VIR DYN USQ
DSN8BQRY 64 0 --- --- --- 0
A540769 64 0 --- --- --- ---
S100006 64 0 --- --- --- ---
S103931 64 0 --- --- --- ---
SYSIBM 64 0 --- --- --- ---
S100447 64 540 22.03.2012 --- --- ---
CMNBATCH 64 2701 09.12.2014 --- --- ---
7 rows fetched: select 'A540769', 64 pCols, count(*) planCnt , max(date(ex...
$#out 20150223 08:03:39
COL1 PLANCNT VIRTIND USQUER
PCOLS PLANMAX DYNCAC
COL1 PC PLAN PLANMAX VIR DYN USQ
A168658 66 0 --- --- --- ---
A422686 66 0 --- --- --- ---
A540769 66 1604 22.09.2014 0 0 0
A559315 66 0 --- --- --- ---
A662172 66 0 --- --- --- ---
A754048 66 0 --- --- --- ---
A790472 66 0 --- --- --- ---
CMNBATCH 66 1604 22.09.2014 --- --- ---
DSN8BQRY 64 0 --- --- --- 0
S100447 66 213 06.02.2015 --- --- ---
SYSIBM 64 0 --- --- --- ---
11 rows fetched: select 'A168658', 66 pCols, count(*) planCnt , max(date(e...
$#out 20150223 07:33:29
COL1 PLANCNT VIRTIND USQUER
PCOLS PLANMAX DYNCAC
COL1 PC PLAN PLANMAX VIR DYN USQ
A540769 66 0 --- 0 0 0
A754048 64 0 --- 0 0 ---
DSN8BQRY 64 0 --- --- --- 0
A662172 64 0 --- --- --- ---
SYSIBM 64 0 --- --- --- ---
A422686 64 4 20.07.2012 --- --- ---
A790472 64 33 02.12.2014 0 0 ---
A559315 64 51 02.09.2014 0 0 0
A168658 64 113 20.03.2014 0 0 0
S100447 64 213 06.02.2015 0 0 ---
CMNBATCH 64 1604 22.09.2014 --- --- ---
11 rows fetched: select 'A168658', 64 pCols, count(*) planCnt , max(date(e...
$#out 20150223 07:27:06
COL1 PLANCNT VIRTIND USQUER
PCOLS PLANMAX DYNCAC
COL1 PC PLAN PLANMAX VIR DYN USQ
A540768 66 0 --- 0 0 0
A754048 64 0 --- 0 0 ---
DSN8BQRY 64 0 --- --- --- 0
A540769B 66 0 --- --- --- ---
A662172 64 0 --- --- --- ---
A422686 64 4 20.07.2012 --- --- ---
A790472 64 33 02.12.2014 0 0 ---
A559315 64 51 02.09.2014 0 0 0
A168658 64 113 20.03.2014 0 0 0
S100447 64 213 06.02.2015 0 0 ---
A540769 66 1604 22.09.2014 0 0 0
CMNBATCH 64 1604 22.09.2014 --- --- ---
$#out 20150223 07:26:53
SYSIBM 64 0 --- --- --- ---
13 rows fetched: select 'A168658', 64 pCols, count(*) planCnt , max(date(e...
*** run error ***
SQLCODE = -924: DB2 CONNECTION INTERNAL ERROR, 00000002,
0000000C, 00F30006
sql = connect DPXG
$#out 20150216 15:46:30