Date
Name
Plattform
Language
Kurzbeschreibung
Owner
Link
11.11.2001
DB2BP.REXX
OS/390
REXX
DB2 Bufferpool Analysen
Gernot Ruban
DB2 ist ein Produkt der
IBM Corporation. Bitte Copyright- und Trademark-Hinweise beachten!
/*REXX****************************************************************/
/* DB2BP......: Ermittelt und komprimiert Bufferpool Informationen. */
/*-------------------------------------------------------------------*/
/* CALLED BY..: IKJEFT01 - TSO */
/* ARGUMENTS..: SSID I - DB2 Subsystem ID */
/* OPTION I - ' ' nur BP-Infos */
/* I - 'DS' mit Dataset-Infos */
/* I - 'STATS' diverse Statistiken */
/* I - 'TH' Threshold Hits */
/* I - 'ALL' alles, was angeboten wird */
/* RANGE I - '' seit letztem START DB2 */
/* I - 'INTERVAL' seit letztdem -DISPLAY */
/* CALLS......: n/a M - */
/* FILES......: SYSTSPRT O - Infos aus dieser Prozedur */
/*-------------------------------------------------------------------*/
/* FUNCTION...: Ermittelt aus Messages des DB2 Command Processor */
/* Informationen bezügl. Bufferpools. Je nach Option */
/* variieren die Ausgaben. */
/* Die kumulierten Werte werden bei DB2 START mit 0 */
/* initialisiert. */
/*-------------------------------------------------------------------*/
/* HISTORY....: 07.03.97/GR - V1R0 Gernot Ruban */
/* 17.03.97/GR - V1R1 Weitere Kennzahlen, Interval */
/*********************************************************************/
x = MSG("OFF")
/********************************************************************/
/* Argumente einlesen (Wert nicht zwingend) */
/********************************************************************/
parse upper arg ssid "," option "," range "," debug
if ssid = "",
! ( option^="STATS" & option^="" ,
& option^="DS" & option^="ALL" & option ^= "TH" ),
! ( range ^="" & range ^="INTERVAL" )
then do
say "DB2BP: Fehler bei der Angabe von Argumenten:"
say " TSO DB2BP ssid[,option][,range]"
say " ssid....: SUBSYSTEM - je nach Umgebung, kein DRDA möglich"
say " option..: '' - (dflt) BP Konfiguration"
say " DS - Dataset Usage"
say " TH - Threshold Hits"
say " STATS - BP Statistics"
say " ALL - alle verfgbaren Infos"
say " range...: '' - (dflt) Auswertung seit START DB2"
say " INTERVAL - Auswertung seit letztem -DISPLAY"
signal error
end
if debug = "DEBUG"
then trace ?I
/********************************************************************/
/* Header ausgeben */
/********************************************************************/
say copies("#",79)
say "# DB2BP - DB2 Bufferpool Statistics "left(ssid,4),
" "date("O")" "time()" #";
say copies("#",79)
/********************************************************************/
/* DB2 Command Processor starten, DB2 Command ausfhren */
/********************************************************************/
push "END"
if option = ""
then cmd = "-DIS BUFFERPOOL(ACTIVE)"
else cmd = "-DIS BUFFERPOOL(ACTIVE) LIST(ACTIVE) LSTATS"
push cmd "DETAIL(*)"
address TSO "DSN SYSTEM("ssid")"
/* Zweifache Ausfhrung wg. Message Limit */
x = MSG("ON")
x = outtrap("OUT.","*")
push "END"
if range = "INTERVAL"
then cmd = cmd "DETAIL(INTERVAL)"
else cmd = cmd "DETAIL(*)"
push cmd
address TSO "DSN SYSTEM("ssid")"
x = MSG("OFF")
x = outtrap()
if rc <> 0
then do
say "DB2BP: Fehler bei der Ausfuehrung des DB2 Command Processors."
say " DSN Return Code = "rc
signal error
end
/********************************************************************/
/* Compress der Zeilen auf Message-Basis */
/********************************************************************/
ci = 0
do i=1 to out.0
msgid = word(out.i,1)
out.i = space(translate(out.i,"",",="))
if pos("DSNB",msgid) > 0
then do
ci = ci + 1
msg.ci = out.i
msgid.ci = msgid
end
else do
msg.ci = msg.ci" "out.i
end
end
msg.0 = ci
drop out.
/********************************************************************/
/* Messages verarbeiten */
/********************************************************************/
bc = 0 /* buffer pool count */
do i=1 to msg.0
select
when msgid.i = "DSNB401I" then
do
if bc > 0
then dsname.bc.0 = dsc /* DS-Count vorhergeh. BP */
bc = bc + 1 /* buffer pool count */
dsc = 0 /* dataset per BP */
bp.bc = word(msg.i,wordpos("NAME",msg.i)+1)
bp.bc = substr(bp.bc,3)
id.bc = word(msg.i,wordpos("ID",msg.i)+1)
use.bc = word(msg.i,wordpos("COUNT",msg.i)+1)
end
when msgid.i = "DSNB402I" then
do
vpsize.bc = word(msg.i,wordpos("SIZE",msg.i)+1)
vpalloc.bc = word(msg.i,wordpos("ALLOCATED",msg.i)+1)
vptodel.bc = word(msg.i,wordpos("DELETED",msg.i)+1)
vpinuse.bc = word(msg.i,wordpos("IN-USE/UPDATED",msg.i)+1)
end
when msgid.i = "DSNB403I" then
do
hpsize.bc = word(msg.i,wordpos("SIZE",msg.i)+1)
hpalloc.bc = word(msg.i,wordpos("ALLOCATED",msg.i)+1)
hptodel.bc = word(msg.i,wordpos("DELETED",msg.i)+1)
hpes.bc = word(msg.i,wordpos("ES",msg.i)+1)
hpcast.bc = word(msg.i,wordpos("CASTOUT",msg.i)+1)
end
when msgid.i = "DSNB404I" then
do
vpseqt.bc = word(msg.i,wordpos("VP SEQUENTIAL",msg.i)+2)
hpseqt.bc = word(msg.i,wordpos("HP SEQUENTIAL",msg.i)+2)
dwqt.bc = word(msg.i,wordpos("WRITE",msg.i)+1)
vdwqt.bc = word(msg.i,wordpos("WRT",msg.i)+1)
vppseqt.bc = word(msg.i,wordpos("PARALLEL SEQUENTIAL",msg.i)+2)
end
when msgid.i = "DSNB409I" then
do
bpdate = substr(msg.i,wordindex(msg.i,5))
if date_done <> "YES"
then do
date_done = "YES"
say "#"center(substr(msg.i,wordindex(msg.i,3)),77)"#"
say copies("#",79)
end
end
when msgid.i = "DSNB410I" then
do
bpdate = substr(msg.i,wordindex(msg.i,5))
end
when msgid.i = "DSNB411I" then
do
rgetpage.bc = word(msg.i,wordpos("RANDOM GETPAGE",msg.i)+2)
sgetpage.bc = word(msg.i,wordpos("SEQ. GETPAGE",msg.i)+2)
rsyncio.bc = word(msg.i,wordpos("(R)",msg.i)+1)
ssyncio.bc = word(msg.i,wordpos("(S)",msg.i)+1)
dmthhit.bc = word(msg.i,wordpos("HIT",msg.i)+1)
end
when msgid.i = "DSNB412I" then
do
spfreq.bc = word(msg.i,wordpos("REQUESTS",msg.i)+1)
spfio.bc = word(msg.i,wordpos("I/O",msg.i)+1)
spfread.bc = word(msg.i,wordpos("READ",msg.i)+1)
end
when msgid.i = "DSNB413I" then
do
lpfreq.bc = word(msg.i,wordpos("REQUESTS",msg.i)+1)
lpfio.bc = word(msg.i,wordpos("I/O",msg.i)+1)
lpfread.bc = word(msg.i,wordpos("READ",msg.i)+1)
end
when msgid.i = "DSNB414I" then
do
dpfreq.bc = word(msg.i,wordpos("REQUESTS",msg.i)+1)
dpfio.bc = word(msg.i,wordpos("I/O",msg.i)+1)
dpfread.bc = word(msg.i,wordpos("READ",msg.i)+1)
end
when msgid.i = "DSNB415I" then
do
pfdisnb.bc = word(msg.i,wordpos("BUFFER",msg.i)+1)
pfdisnr.bc = word(msg.i,wordpos("ENGINE",msg.i)+1)
end
when msgid.i = "DSNB420I" then
do
wupdates.bc = word(msg.i,wordpos("UPDATES",msg.i)+1)
wwritten.bc = word(msg.i,wordpos("WRITTEN",msg.i)+1)
wasync.bc = word(msg.i,wordpos("ASYNC WRITE I/O",msg.i)+3)
wsync.bc = word(msg.i,wordpos("SYNC WRITE I/O",msg.i)+3)
end
when msgid.i = "DSNB421I" then
do
thrdwqt.bc = word(msg.i,wordpos("DWT HIT",msg.i)+2)
thrvdwqt.bc = word(msg.i,wordpos("VERTICAL DWT HIT",msg.i)+3)
thrnwe.bc = word(msg.i,wordpos("ENGINE",msg.i)+1)
end
when msgid.i = "DSNB430I" then
do
/* Hiperpool Activity */
end
when msgid.i = "DSNB431I" then
do
/* Hiperpool Activity */
end
when msgid.i = "DSNB440I" then
do
parareq.bc = word(msg.i,wordpos("REQUEST",msg.i)+1)
paradeg.bc = word(msg.i,wordpos("DEGRADED PARALLEL",msg.i)+2)
end
when msgid.i = "DSNB450I" then
do
if option <> ""
then do
dsc = dsc + 1
dsname.bc.dsc = word(msg.i,wordpos("TABLESPACE",msg.i)+1)
usecnt.bc.dsc = word(msg.i,wordpos("USE COUNT",msg.i)+2)
part.bc.dsc = 1
current.bc.dsc = 0
max.bc.dsc = 0
sdlavg.bc.dsc = 0
sdlmax.bc.dsc = 0
sdlpage.bc.dsc = 0
adlavg.bc.dsc = 0
adlmax.bc.dsc = 0
adlpage.bc.dsc = 0
end
end
when msgid.i = "DSNB451I" then
do
if option <> ""
then do
dsc = dsc + 1
dsname.bc.dsc = word(msg.i,wordpos("INDEXSPACE",msg.i)+1)
usecnt.bc.dsc = word(msg.i,wordpos("USE COUNT",msg.i)+2)
part.bc.dsc = 1
current.bc.dsc = 0
max.bc.dsc = 0
sdlavg.bc.dsc = 0
sdlmax.bc.dsc = 0
sdlpage.bc.dsc = 0
adlavg.bc.dsc = 0
adlmax.bc.dsc = 0
adlpage.bc.dsc = 0
end
end
when msgid.i = "DSNB452I" then
do
if option <> ""
then do
part.bc.dsc = word(msg.i,wordpos("DATASET",msg.i)+1)
end
end
when msgid.i = "DSNB453I" then
do
if option <> ""
then do
current.bc.dsc = word(msg.i,wordpos("CURRENT",msg.i)+1)
max.bc.dsc = word(msg.i,wordpos("MAX",msg.i)+1)
end
end
when msgid.i = "DSNB455I" then
do
if option <> ""
then do
sdlavg.bc.dsc = word(msg.i,wordpos("AVERAGE DELAY",msg.i)+2)
sdlmax.bc.dsc = word(msg.i,wordpos("MAXIMUM DELAY",msg.i)+2)
sdlpage.bc.dsc = word(msg.i,wordpos("PAGES",msg.i)+1)
end
end
when msgid.i = "DSNB456I" then
do
if option <> ""
then do
adlavg.bc.dsc = word(msg.i,wordpos("AVERAGE DELAY",msg.i)+2)
adlmax.bc.dsc = word(msg.i,wordpos("MAXIMUM DELAY",msg.i)+2)
adlpage.bc.dsc = word(msg.i,wordpos("PAGES",msg.i)+1)
adlioc.bc.dsc = word(msg.i,wordpos("COUNT",msg.i)+1)
end
end
otherwise do
say "DB2BP: Message-ID "msgid.i" nicht registriert!"
say msg.i
end
end
end
/********************************************************************/
/* Infos extrahiert? */
/********************************************************************/
if bc = 0
then do
say ""
say "DB2BP: Fehler - keine Bufferpool-Informationen extrahiert!"
do i=1 to msg.0
sayy msg.i
end
signal error
end
bp.0 = bc
dsname.bc.0 = dsc /* Stand des letzten BP/DS Counts */
drop msgid.
drop msg.
/********************************************************************/
/* Option ''/DS/STATS/ALL */
/********************************************************************/
if option = "" ! option = "ALL"
then do
If Sysvar(SYSISPF) <> "NOT ACTIVE"
then "CLRSCRN"
say " VP HP VV PP VP VP TO IN " !!,
"HP HP TO EXP. CST PARAL"
say "BP SQ SQ DW DW SQ SIZE ALLOC DEL USE " !!,
"SIZE ALLOC DEL STG. OUT REQ."
say "----+--+--+--+--+--+-----+-----+---+----+" !!,
"-----+-----+---+----+---+-----"
sum_vpalloc = 0
sum_hpalloc = 0
sum_parareq = 0
do i=1 to bp.0
say left(bp.i,4),
right(vpseqt.i,2),
right(hpseqt.i,2),
right(dwqt.i,2),
right(vdwqt.i,2),
right(vppseqt.i,2),
right(vpsize.i,5),
right(vpalloc.i,5) right(vptodel.i,3),
right(vpinuse.i,4),
right(hpsize.i,5),
right(hpalloc.i,5) right(hptodel.i,3),
right(hpes.i,4) hpcast.i,
right(parareq.i,5)
sum_vpalloc = sum_vpalloc + vpalloc.i
sum_hpalloc = sum_hpalloc + hpalloc.i
sum_parareq = sum_parareq + parareq.i
end
say "----+--+--+--+--+--+-----+=====+---+----+" !!,
"-----+=====+---+----+---+====="
say copies(" ",25),
right(sum_vpalloc,5),
copies(" ",14),
right(sum_hpalloc,5),
copies(" ",12),
right(sum_parareq,5)
say "Press <ENTER>";pull x
end
/********************************************************************/
/* Option TH/ALL */
/********************************************************************/
if option = "TH" ! option = "ALL"
then do
/*************************Read/Write Engines********************/
If Sysvar(SYSISPF) <> "NOT ACTIVE"
then "CLRSCRN"
say " ! DM!-I/O DELAY-+-PREFETCH--+" !!,
"-------BUFFER WRITE OPERATIONS-----+"
say " ! USE! TH! AVERAGE ! DISABLED !" !!,
" THRESHOLD HITS !UPDAT ASYNC WRITT"
say "BP ! CNT!HIT!SYNC. ASYNC!NOBUF NOENG!" !!,
" DWT VDWT NOENG!LATEN RATE PG/IO"
say "----+----+---+-----+-----+-----+-----+" !!,
"-----+-----+-----+-----+-----+-----"
sum_pfdisnb = 0
sum_pfdisnr = 0
sum_thrnwe = 0
avg_rateasync = 0
do i=1 to bp.0
if wwritten.i > 0
then uplatency = wupdates.i/wwritten.i
else uplatency = 0
if wasync.i+wsync.i > 0
then do
rateasync = (wasync.i/(wsync.i+wasync.i))*100
pgsio = (wwritten.i/(wsync.i+wasync.i))
end
else do
rateasync = 0
pgsio = 0
end
say left(bp.i,4) !! "!" !!,
right(use.i,4) !! "!" !!,
right(dmthhit.i,3) !! "!" !!,
right(SDELAY(bp.i),5),
right(ADELAY(bp.i),5) !! "!" !!,
right(pfdisnb.i,5),
right(pfdisnr.i,5) !! "!" !!,
right(thrdwqt.i,5),
right(thrvdwqt.i,5),
right(thrnwe.i,5) !! "!" !!,
right(trunc(uplatency,2),5),
right(trunc(rateasync,2),5),
right(trunc(pgsio,2),5)
sum_pfdisnb = sum_pfdisnb + pfdisnb.i
sum_pfdisnr = sum_pfdisnr + pfdisnr.i
sum_thrnwe = sum_thrnwe + thrnwe.i
avg_rateasync = avg_rateasync + rateasync
end
say "----+----+---+-----+-----+=====+=====+" !!,
"-----+-----+=====+-----+=====+-----"
avg_rateasync = avg_rateasync / bp.0
say copies(" ",25),
right(sum_pfdisnb,5),
right(sum_pfdisnr,5),
copies(" ",11),
right(sum_thrnwe,5),
copies(" ",5),
right(trunc(avg_rateasync,2),5)
say "Press <ENTER>";pull x
end
/********************************************************************/
/* Option STATS/ALL */
/********************************************************************/
if option = "ALL" ! option = "STATS"
then do
If Sysvar(SYSISPF) <> "NOT ACTIVE"
then "CLRSCRN"
say "+--VIRT. POOLS--+---BP GP REQ. TO I/O RATES----+" !!,
"-----PREFETCH OPERATIONS ----"
say " VP CURR.!GETPAG SEQRQ SYNIO SQSYN BPHIT!" !!,
"PFTCH SEQTL DYNAM LIST IO/REQ"
say "BP ALLOC ACTIV!REQ.*K RATE RATE RATE RATIO!" !!,
" HIT PG/IO PG/IO PG/IO RATE "
say "----+-----+-----+------+-----+-----+-----+-----+" !!,
"-----+-----+-----+-----+------"
sum_vpalloc = 0
sum_hpalloc = 0
sum_active = 0
avg_srrate = 0
avg_siorate = 0
avg_ssrate = 0
avg_bphit = 0
avg_pfhit = 0
avg_pfiorq = 0
do i=1 to bp.0
/************************** BP Rates ************************/
getpage = rgetpage.i + sgetpage.i
active = TOTALS(bp.i)
syncr = rsyncio.i + ssyncio.i
asyncr = spfio.i + lpfio.i + dpfio.i
if syncr+asyncr > 0
then do
syncsqrate = (ssyncio.i/(syncr+asyncr))*100
synciorate = (syncr/(syncr+asyncr))*100
end
else do
syncsqrate = 0
synciorate = 0
end
if getpage > 0
then do
seqreqrate = (sgetpage.i/getpage)*100
/*********** Hit Ratios ********************************/
bphit = ((getpage-syncr-asyncr)/getpage)*100
pfhit = ((getpage-(syncr-asyncr+,
spfread.i+lpfread.i+dpfread.i)),
/getpage)*100
end
else do
seqreqrate = 0
bphit = 0
pfhit = 0
end
/************************** Prefetch Activity ***************/
if spfio.i > 0
then spfqu = spfread.i/spfio.i
else spfqu = 0
if dpfio.i > 0
then dpfqu = dpfread.i/dpfio.i
else dpfqu = 0
if lpfio.i > 0
then lpfqu = lpfread.i/lpfio.i
else lpfqu = 0
if spfreq.i+lpfreq.i+dpfreq.i > 0
then pfiorq = (asyncr/(spfreq.i+lpfreq.i+dpfreq.i))*100
else pfiorq = 0
/************************** Statistics Line *****************/
say left(bp.i,4),
right(vpalloc.i,5),
right(active,5) !! "!" !!,
right(trunc(getpage/1000),6),
right(trunc(seqreqrate,2),5),
right(trunc(synciorate,2),5),
right(trunc(syncsqrate,2),5),
right(trunc(bphit,2),5) !! "!" !!,
right(trunc(pfhit,2),5),
right(trunc(spfqu,2),5),
right(trunc(dpfqu,2),5),
right(trunc(lpfqu,2),5),
right(trunc(pfiorq,2),6)
sum_vpalloc = sum_vpalloc + vpalloc.i
sum_hpalloc = sum_hpalloc + hpalloc.i
sum_active = sum_active + active
avg_srrate = avg_srrate + seqreqrate
avg_siorate = avg_siorate + synciorate
avg_ssrate = avg_ssrate + syncsqrate
avg_bphit = avg_bphit + bphit
avg_pfhit = avg_pfhit + pfhit
avg_pfiorq = avg_pfiorq + pfiorq
end
say "====+=====+=====+------+=====+=====+=====+=====+" !!,
"=====+-----+-----+-----+======"
avg_srrate = avg_srrate/bc
avg_ssrate = avg_ssrate/bc
avg_siorate = avg_siorate/bc
avg_bphit = avg_bphit/bc
avg_pfhit = avg_pfhit/bc
avg_pfiorq = avg_pfiorq/bc
say right(bc,4),
right(sum_vpalloc,5),
right(sum_active,5),
" ",
right(trunc(avg_srrate,2),5),
right(trunc(avg_siorate,2),5),
right(trunc(avg_ssrate,2),5),
right(trunc(avg_bphit,2),5),
right(trunc(avg_pfhit,2),5),
" ",
right(trunc(avg_pfiorq,2),6)
say "Press <ENTER>";pull x
end
/********************************************************************/
/* Dataset-Detailauflistung */
/********************************************************************/
if option = "DS" ! option = "ALL"
then do
If Sysvar(SYSISPF) <> "NOT ACTIVE"
then "CLRSCRN"
SAY " " !!,
" USE CURR. MAX.+-I/O DELAY-"
SAY "BP DATASET CURRENTLY BUFFERED " !!,
"P# CNT USED USED!SYNC. ASYNC"
say "----+-------------------------------------------+" !!,
"--+---+-----+-----+-----+-----"
do i=1 to bp.0
do j=1 to dsname.i.0
say left(bp.i,4),
left(dsname.i.j,43),
right(part.i.j,2,"0"),
right(usecnt.i.j,3),
right(current.i.j,5),
right(max.i.j,5) !! "!" !!,
right(sdlavg.i.j,5),
right(adlavg.i.j,5)
end
end
say "Press <ENTER>";pull x
end
exit 0
/********************************************************************/
/* TOTALS: Usage der einzelnen BP's kummuliert */
/********************************************************************/
TOTALS:
arg bp
total = 0
do xi=1 to bp.0
if bp.xi = bp
then ,
do xj=1 to dsname.xi.0
total = total + current.xi.xj
end
end
return total
/********************************************************************/
/* SDELAY: Summe der Sync I/O Delays AVG je BP */
/********************************************************************/
SDELAY:
arg bp
total = 0
do xi=1 to bp.0
if bp.xi = bp
then ,
do xj=1 to dsname.xi.0
total = total + sdlavg.xi.xj
end
end
return total
/********************************************************************/
/* ADELAY: Summe der ASync I/O Delays AVG je BP */
/********************************************************************/
ADELAY:
arg bp
total = 0
do xi=1 to bp.0
if bp.xi = bp
then ,
do xj=1 to dsname.xi.0
total = total + adlavg.xi.xj
end
end
return total
/********************************************************************/
/* Fehlerbehandlung */
/********************************************************************/
SYNTAX:
say "Syntax-Fehler."
ERROR:
say ""
say "Abbruch der REXX Prozedur DB2BP mit RC=20."
exit 20
© Gernot Ruban