zOs/SQL/PLANCOMP
$#s DBOL
-- compare all plan_tables in a db2 Subsystem to a model set
with m as -- select model table set
(
select t.creator mCr, t.name mTb
, t.encoding_scheme mEnc
, t.recLength mRecL, t.colCount mCols, c.name mCol
from sysibm.sysTables t
left join sysibm.sysColumns c
on t.creator = c.tbCreator and t.name = c.tbName
and t.colCount = c.colNo
where type = 'T'
and creator = 'A540769' and dbName = 'DB2PLAN'
)
, c as -- select all other creators of plan_tables
(
select creator cCr
from sysibm.systables t
join m
on t.name = m.mTb and t.creator <> m.mCr and t.type = 'T'
group by creator
)
, j1 as -- join creators (to avoid sql error)
(
select c.cCr, m.*
from m, c
)
, j2 as -- left join infos of other tables
(
select t.name tb
, t.encoding_scheme enc
, t.recLength recL, t.colCount cols , k.name col
, j1.*
from j1
left join sysibm.sysTables t
on t.name = mTb and t.creator = cCr and t.type = 'T'
left join sysibm.sysColumns k
on t.creator = k.tbCreator and t.name = k.tbName
and t.colCount = k.colNo
)
, j3 as -- select changed or missing tables only
(
select * from j2
where (tb is null and mtb not in ( 'DSN_USERQUERY_TABLE'
, 'DSN_STATEMENT_CACHE_TABLE'
, 'DSN_VIRTUAL_INDEXES'
, 'DSN_VIRTUAL_KEYTARGETS'
, 'DSN_PREDICATE_SELECTIVITY'
, 'F2PLAN_TABLE'
) )
or mRecL <> recL or mCols <> cols or mCol <> col or mEnc <>enc
) -- join unchanged creators
select value(c.cCr, j3.cCr) cr, mTb tb
, enc, recL, cols, col, mRecL, mEnc, mCols, mCol, mCr
from c left join j3 on c.cCr = j3.cCr
order by 1, tb
$#out 20150223 08:12:37
CR ENC COLS MRECL MCOLS
TB RECL COL MENC MCOL
CR TB ENC RECL COL COL MREC MEN MCO MCOL
CMNBATCH --- --- --- --- --- --- --- --- ---
DSN8BQRY DSN_COLDIST_TABLE --- --- --- --- 7345 U 21 EXPANSION_
DSN8BQRY DSN_DETCOST_TABLE --- --- --- --- 1007 U 136 EXPANSION_
DSN8BQRY DSN_FILTER_TABLE --- --- --- --- 493 U 18 EXPANSION_
DSN8BQRY DSN_FUNCTION_TABLE --- --- --- --- 4797 U 20 EXPANSION_
DSN8BQRY DSN_KEYTGTDIST_TABLE --- --- --- --- 7345 U 21 EXPANSION_
DSN8BQRY DSN_PGRANGE_TABLE --- --- --- --- 476 U 15 EXPANSION_
DSN8BQRY DSN_PGROUP_TABLE --- --- --- --- 739 U 42 EXPANSION_
DSN8BQRY DSN_PREDICAT_TABLE --- --- --- --- 2930 U 42 EXPANSION_
DSN8BQRY DSN_PTASK_TABLE --- --- --- --- 595 U 25 EXPANSION_
DSN8BQRY DSN_QUERYINFO_TABLE --- --- --- --- 771 U 18 EXPANSION_
DSN8BQRY DSN_QUERY_TABLE --- --- --- --- 514 U 16 EXPANSION_
DSN8BQRY DSN_SORTKEY_TABLE --- --- --- --- 639 U 21 EXPANSION_
DSN8BQRY DSN_SORT_TABLE --- --- --- --- 486 U 16 EXPANSION_
DSN8BQRY DSN_STATEMNT_TABLE --- --- --- --- 744 U 16 EXPANSION_
DSN8BQRY DSN_STAT_FEEDBACK --- --- --- --- 1687 U 20 REMARKS
DSN8BQRY DSN_STRUCT_TABLE --- --- --- --- 510 U 20 EXPANSION_
DSN8BQRY DSN_USERQUERY_TABLE U 846 17 OTHER_PARMS 849 U 20 OPTION_OVE
DSN8BQRY DSN_VIEWREF_TABLE --- --- --- --- 727 U 13 EXPANSION_
DSN8BQRY PLAN_TABLE U 2745 64 MERGN 2749 U 66 EXPANSION_
S100006 --- --- --- --- --- --- --- --- ---
S100447 --- --- --- --- --- --- --- --- ---
S103931 --- --- --- --- --- --- --- --- ---
SYSIBM DSN_COLDIST_TABLE U 7343 20 HIGHVALUE 7345 U 21 EXPANSION_
SYSIBM DSN_DETCOST_TABLE U 1002 132 CRED 1007 U 136 EXPANSION_
SYSIBM DSN_FILTER_TABLE U 491 17 PUSHDOWN 493 U 18 EXPANSION_
SYSIBM DSN_FUNCTION_TABLE U 4795 19 VERSION 4797 U 20 EXPANSION_
SYSIBM DSN_KEYTGTDIST_TABLE U 7343 20 HIGHVALUE 7345 U 21 EXPANSION_
SYSIBM DSN_PGRANGE_TABLE U 474 14 VERSION 476 U 15 EXPANSION_
SYSIBM DSN_PGROUP_TABLE U 737 41 STRAW_MODEL 739 U 42 EXPANSION_
SYSIBM DSN_PREDICAT_TABLE U 2928 41 VERSION 2930 U 42 EXPANSION_
SYSIBM DSN_PTASK_TABLE U 593 24 VERSION 595 U 25 EXPANSION_
SYSIBM DSN_QUERYINFO_TABLE U 769 17 QB_INFO_ROWID 771 U 18 EXPANSION_
SYSIBM DSN_QUERY_TABLE U 512 15 VERSION 514 U 16 EXPANSION_
SYSIBM DSN_SORTKEY_TABLE U 637 20 VERSION 639 U 21 EXPANSION_
SYSIBM DSN_SORT_TABLE U 484 15 VERSION 486 U 16 EXPANSION_
SYSIBM DSN_STATEMNT_TABLE U 742 15 VERSION 744 U 16 EXPANSION_
SYSIBM DSN_STAT_FEEDBACK --- --- --- --- 1687 U 20 REMARKS
SYSIBM DSN_STRUCT_TABLE U 508 19 VERSION 510 U 20 EXPANSION_
SYSIBM DSN_VIEWREF_TABLE U 725 12 SECTNOI 727 U 13 EXPANSION_
SYSIBM PLAN_TABLE U 2745 64 MERGN 2749 U 66 EXPANSION_
41 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding...
$#out 20150223 08:03:59
CR ENC COLS MRECL MCOLS
TB RECL COL MENC MCOL
CR TB ENC RECL COL COL MREC MEN MCO MCOL
A168658 --- --- --- --- --- --- --- --- ---
A422686 --- --- --- --- --- --- --- --- ---
A559315 --- --- --- --- --- --- --- --- ---
A662172 --- --- --- --- --- --- --- --- ---
A754048 --- --- --- --- --- --- --- --- ---
A790472 --- --- --- --- --- --- --- --- ---
CMNBATCH --- --- --- --- --- --- --- --- ---
DSN8BQRY DSN_COLDIST_TABLE --- --- --- --- 7345 U 21 EXPANSION_
DSN8BQRY DSN_DETCOST_TABLE --- --- --- --- 1007 U 136 EXPANSION_
DSN8BQRY DSN_FILTER_TABLE --- --- --- --- 493 U 18 EXPANSION_
DSN8BQRY DSN_FUNCTION_TABLE --- --- --- --- 4797 U 20 EXPANSION_
DSN8BQRY DSN_KEYTGTDIST_TABLE --- --- --- --- 7345 U 21 EXPANSION_
DSN8BQRY DSN_PGRANGE_TABLE --- --- --- --- 476 U 15 EXPANSION_
DSN8BQRY DSN_PGROUP_TABLE --- --- --- --- 739 U 42 EXPANSION_
DSN8BQRY DSN_PREDICAT_TABLE --- --- --- --- 2930 U 42 EXPANSION_
DSN8BQRY DSN_PTASK_TABLE --- --- --- --- 595 U 25 EXPANSION_
DSN8BQRY DSN_QUERYINFO_TABLE --- --- --- --- 771 U 18 EXPANSION_
DSN8BQRY DSN_QUERY_TABLE --- --- --- --- 514 U 16 EXPANSION_
DSN8BQRY DSN_SORTKEY_TABLE --- --- --- --- 639 U 21 EXPANSION_
DSN8BQRY DSN_SORT_TABLE --- --- --- --- 486 U 16 EXPANSION_
DSN8BQRY DSN_STATEMNT_TABLE --- --- --- --- 744 U 16 EXPANSION_
DSN8BQRY DSN_STAT_FEEDBACK --- --- --- --- 1687 U 20 REMARKS
DSN8BQRY DSN_STRUCT_TABLE --- --- --- --- 510 U 20 EXPANSION_
DSN8BQRY DSN_USERQUERY_TABLE U 846 17 OTHER_PARMS 849 U 20 OPTION_OVE
DSN8BQRY DSN_VIEWREF_TABLE --- --- --- --- 727 U 13 EXPANSION_
DSN8BQRY PLAN_TABLE U 2745 64 MERGN 2749 U 66 EXPANSION_
S100447 --- --- --- --- --- --- --- --- ---
SYSIBM DSN_COLDIST_TABLE U 7343 20 HIGHVALUE 7345 U 21 EXPANSION_
SYSIBM DSN_DETCOST_TABLE U 1002 132 CRED 1007 U 136 EXPANSION_
SYSIBM DSN_FILTER_TABLE U 491 17 PUSHDOWN 493 U 18 EXPANSION_
SYSIBM DSN_FUNCTION_TABLE U 4795 19 VERSION 4797 U 20 EXPANSION_
SYSIBM DSN_KEYTGTDIST_TABLE U 7343 20 HIGHVALUE 7345 U 21 EXPANSION_
SYSIBM DSN_PGRANGE_TABLE U 474 14 VERSION 476 U 15 EXPANSION_
SYSIBM DSN_PGROUP_TABLE U 737 41 STRAW_MODEL 739 U 42 EXPANSION_
SYSIBM DSN_PREDICAT_TABLE U 2928 41 VERSION 2930 U 42 EXPANSION_
SYSIBM DSN_PTASK_TABLE U 593 24 VERSION 595 U 25 EXPANSION_
SYSIBM DSN_QUERYINFO_TABLE U 769 17 QB_INFO_ROWID 771 U 18 EXPANSION_
SYSIBM DSN_QUERY_TABLE U 512 15 VERSION 514 U 16 EXPANSION_
SYSIBM DSN_SORTKEY_TABLE U 637 20 VERSION 639 U 21 EXPANSION_
SYSIBM DSN_SORT_TABLE U 484 15 VERSION 486 U 16 EXPANSION_
SYSIBM DSN_STATEMNT_TABLE U 742 15 VERSION 744 U 16 EXPANSION_
SYSIBM DSN_STAT_FEEDBACK --- --- --- --- 1687 U 20 REMARKS
SYSIBM DSN_STRUCT_TABLE U 508 19 VERSION 510 U 20 EXPANSION_
SYSIBM DSN_VIEWREF_TABLE U 725 12 SECTNOI 727 U 13 EXPANSION_
SYSIBM PLAN_TABLE U 2745 64 MERGN 2749 U 66 EXPANSION_
45 rows fetched: with m as ( select t.creator mCr, t.name mTb , t.encoding...
$#out 20150216 15:54:05