zOs/SQL/GBGRSPC
$#:
$*( -------------------------------------------------------------------
spaceQuery wsh : dynamische Abfragen auf GbGrIxStats & GbGrTsStats
: per Stichdatum oder Zeitreihe
: verschieden Gruppierungen und zwischentotalbe
--------------------------------------------------------------------$*)
*--- Function -----------------
fun = o $** b=batchjob, s=sql anzeigen, o=online sql
*--- Timeseries ---------------
intCnt = 10 $** Anzahl Intervale, 0 = ein einzige Stichdatum
intLen = 1 $** Länge eines intervalls, numerisch
$=intTyp = day $** Intervall-"Typ" day, month, year
$=startDate = % $** Startdatum (von da rückwärts). %=heute
*--- Selection Criteria -------
$=inRZ = RZX $** RZ. Auch % erlaubt
$=inSys = DEVG $** Subsystem. Auch % erlaubt
$=inDB = DB2PLAN $** Datenbank. % erlaubt
$=inTS = CMNBA% $** Tablespace. % erlaubt
inPart = % $** Partition. Numerisch oder % für alle
rows = 20000 $** Rowlimit
*--- Detail/Group Level von "r" bis "rsdtip"
*--- definiert die "Gruppierungsstufe"
rol = rsdt $** R RZ - obligatorisch
$** rS DB-Subsystem
$** rsD Datenbank
$** rsdT Tablespace
$** rsdtI Indexspaces
$** rsdtP Partitionslevel TS
$** rsdtiP Partitionslevel TS+IX
*-- order ---------------------
order = A
$** order by : A 'alphabtical' : with total on top'
$** T 'Totals first : Total, Subtotals, Detailrows
$** S 'Space' : total first, then ordered by space
$*(----------------------------------------------
mainFunction: fun
o (online) führt SQL aus
b (batch) erzeugt batchjob mit sql
s (sql) zeigt aufgelöstes SQL
------------------------------------------------$*)
$@/mainFun/
if $fun = 'o' then $@[
$@work
call sqlConnect
$| call sqlStmts
$>. fEdit('::v')
$] else if $fun = 'b' then $@=[
$>. fEdit()
//$-[userid()$]W JOB (SMM27862,0240,,3612),'DSNTEP2',
// MSGCLASS=T,TIME=1440,SCHENV=DB2ALL,
// NOTIFY=&SYSUID,CLASS=M1
//*--------------------------------------------------
//* dsnTep2 fuer gbgrSpace query
//*--------------------------------------------------
//RUNSQL EXEC PGM=IKJEFT01,PARM='%WSH s #'
//OUT DD SYSOUT=*,DCB=(LRECL=32756,BLKSIZE=32760,RECFM=VBA)
//SYSPROC DD DISP=SHR,DSN=DSN.DB2.EXEC
//SYSTSIN DD DUMMY
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//WSH DD *
$@prParm
$@work
$] else if $fun = 's' then $@=[
$>. fEdit('::v')
$@prParm
$@work
$] else
call err 'please implement fun='$fun
$/mainFun/
$@proc $@=/prParm/ $** write parameters to batch/sql
-- SQL Generated using the following paramaters
-- RZ / SubSystem : $inRZ / $inSys
-- Database : $inDB
-- Tablespace : $inTS
-- Partition : $inPart
-- Grouping Level : $rol
-- Sort mode : $order
-- $intCnt intervals of $intLen $intTyp
$/prParm/
$** ----------------------------------------------
$** MAIN PRODECURE "/WORK/"
$** ----------------------------------------------
$@proc $@=/work/
$= rol =- ut2lc($rol)
$@ cols = ''
$= timevar = $''
$do rx=1 to length($rol) $@[
q = substr($rol, rx, 1)
if q == 'r' then
cols = 'rz'
else if q == 's' then
cols = cols', dbsys'
else if q == 'd' then
cols = cols', dbName'
else if q == 't' then
cols = cols', ts'
else if q == 'i' then
cols = cols', indexSpace'
else if q == 'p' then
cols = cols', partition'
else
call err 'bad col' q rx 'in $rol ' $rol
$]
$= rolCol =- substr(cols, 3)
$= rolCol =- cols
$** BEGIN OF SQL PART --------------------------------------
set current path oa1p;
set current application compatibility 'V11R1';
with d (d,l) as
(
$@ if pos('%',$startDate) > 0 then $@=[
select timestamp(current date,'12:00:00'),0
$] $@ else $@=[
select timestamp($startDate,'12:00:00'),0
$]
from sysibm.sysDummy1
union all select d - $intLen $intTyp,l+1
from d
where l < $intCnt
),
tsi as
(
select rz, dbSys, dbName, ts, indexSpace
, partition
, d
, instance
, cast(null as real) tsUsed
, cast(null as bigInt) tsRows
, 0 tsParts
, tsTy tsType
, substr(tbname, 1, 30) table
, real(nActive) * ixPgSz * 1024 iXused
, totalEntries iXEntries
, loadTS loadTS
, updateStatstime updateStatstime
, 1 ixParts
, strip(tbCreator) || '.' || tbName tb
from oa1p.tqz007GbGrIxSTats is join d
on d >= validbegin
and d < validend
where rz <> '?' and dbsys <> '?'
and rz $-^[eqLike $inRZ $]
and dbSys $-^[eqLike $inSys $]
and dbName $-^[eqLike $inDB $]
and ts $-^[eqLike $inTS $]
$@ if $inPart /= '%' then $@=[
and partition = $inPart
$]
union all
select rz, dbSys, dbName, name ts, '-table--' as indexSpace
, partition
, d
, instance
, real(nActive) * pgSize * 1024 tsUsed
, totalRows tsRows
, 1 tsParts
, tsTy tsType
, substr(tb, 1, 30) table
, cast(0 as real) ixUsed
, cast(0 as bigInt) ixEntries
, loadTs loadTS
, updateStatsTime updateStatsTime
, 0 ixParts
, strip(tbCr) || '.' || tb tb
from oa1p.tqz006GbGrTsSTats st join d
on d >= validbegin
and d < validend
where rz <> '?' and dbsys <> '?'
and rz $-^[eqLike $inRZ $]
and dbSys $-^[eqLike $inSys $]
and dbName $-^[eqLike $inDB $]
and name $-^[eqLike $inTS $]
$@ if $inPart /= '%' then $@=[
and partition = $inPart
$]
)
, roll as (
select $rolCol, d
, case when min(table) = max(table)
then min(table)
else null
end table
, sum(tsUsed) tsUsed
, sum(tsRows) tsRows
, sum (tsParts) tsParts
, sum (iXused) ixused
, value (sum(ixUsed), 0)
+ value(sum(tsUSed), 0) totused
, sum (iXEntries) ixEntries
, sum (iXParts) ixParts
, case when max(tsType) = min(tsType)
then max(tsType)
else 'div'
end tsType
, max(loadTs) loadTsMax
, max(updateStatsTime) statsTimeMax
, min(loadTs) loadTsMin
, min(updateStatsTime) statsTimeMin
, count(distinct rz) cRZ
, count(distinct rz || '/' ||
dbSys) cSys
, count(distinct rz || '/' ||
dbSys || ':' || strip(dbName)) cDB
, count(distinct rz || '/' ||
dbSys || ':' || strip(dbName) ||
'.' || strip(ts) ) cTS
, count(distinct rz || '/' ||
dbSys || ':' || strip(dbName) ||
'.' || strip(ts) || '#' || partition) cTP
, count(distinct rz || '/' || dbSys ||
':' || strip(dbName)|| '.' ||strip(ts)
|| '#'||strip(indexSpace)) cIS
, count(distinct rz || '/' || dbSys ||
':'||strip(dbName)||'.'||strip(ts)
|| '#' || strip(indexSpace)
|| '#' || partition) cIP
, case when max(tb) = min(tb)
then max(tb) else '#tb=' || count(distinct tb) end tb
from tsi
group by
$@ if $rolCol \== '' then
rollup($rolCol)
, d
)
, tot as (
select max(totUsed) totMax from roll
)
select
rz "RZ"
$@ if pos('s', $rol) > 0 then
, dbSys "dbSys"
, date(d) "Date"
$@ if pos('d', $rol) > 0 then
, value(dbName, '#' || cDb) "dB"
$@ else
, '#' || cDb "dB"
$@ if pos('t', $rol) > 0 then
,value(ts , '#' || cTs) "tableSpace"
$@ else
, '#' || cTs "tableSpace"
$@ if pos('i', $rol) > 0 then
, value(indexSpace , '#' || cIs) "indexSpace"
$@ else
, '#' || cIs "indexSpace"
$@ if pos('p', $rol) > 0 then $@=[
, case when partition is null
then '#'||cast(cTP as varchar(4))
else cast(partition as char(4))
end "part" $]
, '!'||repeat('*',round(20 * roll.totused /
(select max(totMax) from tot x))) "spaceDistribution"
, round(100*roll.totused /
(select max(totMax) from tot x),1) ofTotSpace
,fqzfmtbin7(totused) "totalUsed"
,fqzfmtbin7(tsUsed) "tsUsed"
,translate( varchar_format
(tsRows,'999,999,999,999,999')
, '''' ,',') "tsRows"
,tsParts "tsParts"
,fqzfmtbin7(ixUsed) "ixUsed"
,translate( varchar_format
(ixEntries,'999,999,999,999,999')
, '''' ,',') "ixEntries"
,ixParts "ixParts"
,tsTYpe "tsType"
,tb
,table
,case
when rz is null then 0
$@ if pos('s', $rol) > 0 then
when dbSys is null then 1
$@ if pos('d', $rol) > 0 then
when dbname is null then 2
$@ if pos('t', $rol) > 0 then
when ts is null then 3
$@ if pos('i', $rol) > 0 then
when indexSpace is null then 4
$@ if pos('p', $rol) > 0 then
when partition is null then 5
else 99
end rollupLevel
from roll
, tot
$@ if $order = S then $@=[
$** zuerst alle rollups (nach Level) Details am Schluss
order by rollupLevel,
d desc, ofTotSpace desc
$] $@ else if $order = T then $@=[
$** zuerst alle rollups (nach Level) Details am Schluss
order by rollupLevel
,rz, dbSys, d desc
$] $@ else $@=[
$** hierarchische Reihenfolge und redundante rollups enfernt
where 0 = case
$@ if pos('r', $rol) > 0 then
when rz is null and cRZ <= 1 then 1
$@ if pos('s', $rol) > 0 then
when rz is not null and dbSys is null and cSys <= 1 then 1
$@ if pos('d', $rol) > 0 then
when dbSys is not null and dbname is null and cDb <= 1 then 1
$@ if pos('d', $rol) > 0 then
when dbName is null and (cDb <= 1 and cRZ <= 1) then 1
$@ if pos('dt', $rol) > 0 then
when dbName is not null and ts is null and cTs <= 1 then 1
$@ if pos('ti', $rol) > 0 then
when ts is not null and indexSpace is null and cIs <= 1 then 1
$@ if pos('tp', $rol) > 0 then
when ts is not null and partition is null and cTP <= 1 then 1
$@ if pos('ip', $rol) > 0 then
when indexSpace is not null and partition is null and cIP<=1 then 1
else 0 end
order by value(rz,'')
$]
$@ if pos('s', $rol) > 0 then
, value(dbsys, '')
$@ if pos('d', $rol) > 0 then
, value(dbName, '')
$@ if pos('t', $rol) > 0 then
, value(ts, '')
$@ if pos('i', $rol) > 0 then
, value(indexSpace, '')
$@ if pos('p', $rol) > 0 then
, value(partition, -999)
, d desc
fetch first 20000 rows only;
$/work/
$proc $@/eqLike/
parse arg , v
if verify(v, '_%', 'm') < 1 then
return '=' quote(v, "'")
else
return 'like' quote(v, "'")
$/eqLike/
$*****************************************************************************