Ein Beitrag der IBM DB2 OS/390 Hotline - wir danken Herrn Loeben!

Many thanks to Mike Bracey from ISC London for making this REXX available to us. The REXX exe will calculate your page residency times since the last -DISPLAY BUFFERPOOL. Page residency times are a very usable indicator for sufficient bufferpool sizing, however, they will vary widely during a day. So it is advisable to run this REXX multiple times on various days before adjusting bufferpools.

/* REXX           DBPRES                           */
/*
  The purpose of this exec is to calculate the time that a page
  is resident in the DB2 Bufferpools.  The residency time is comparable
  across bufferpools and is more meaningful than the hit rate.
  The program executes a -DISPLAY BUFFERPOOL DETAIL command for the
  subsystem and is therefore reliant on the interval since a DISPLAY
  was last executed. This interval should be between 10 and 30 minutes

  The code is made available on an as-is basis with no implied
  liability;  Support is on a best efforts only basis.

  The exec:
  1) reads and validates the input parameters
  2) submits the DSN commands and captures the message output
  3) reformats the messages
  4) calculates the residency times
  5) outputs the results

  Expected residency times:
  Without Hiperpool: 300 to 600 seconds in Virtual Pool
  With Hiperpool: 30 to 60 seconds in Virtual Pool;
                  300 to 600 seconds in Virtual + Hiper Pool;

*/
Signal labelsyntaxstart
labelsyntaxstart: syntaxstart = sigl + 3
/*
  Syntax:
  DBPRES SYSTEM(subsystemid)

  where:
  subsystemid: is the 1 to 4 character subsystem name

  The program can be executed in foreground or background TSO/E.
  If executed by EXEC command then enclose parameter in quotes:
  EXEC userid.EXEC(DBPRES) 'SYSTEM(subsystemid)' EXEC

*/
Signal labelsyntaxend
labelsyntaxend: syntaxend = sigl - 2
/*
Author : Mike Bracey, IBM UK Ltd
Date   : December 2000
All rights reserved
Amendment history
01/12/2000 V1.0 Base version
20/01/2001 V1.1 Date errors corrected; output format changed.

*/

Trace 'o'
Signal On Error
Numeric Digits 15

/* Initialise variables */

version = "V1.1"       /* Code version */
exit_rc = 0            /* default exit return code */
k = 0                  /* message counter                     */
m. = ""                /* message stem variable          */
p = 0                  /* output file record counter - report */
o. = ""                /* output file stem variable - report */
r = 0                  /* output file record counter - spreadsheet */
s. = ""                /* output file stem variable - spreadsheet */
bpname = ""            /* set bufferpool name */
interval_w = 0         /* flag; set if interval outside bounds */
interval_min = 300     /* recommended minimum interval - seconds */
interval_max = 1800    /* recommended maximum interval - seconds */
interval_fail= 99999   /* absolute maximum interval - seconds */
tvpool = 0             /* total virtual buffer pool MB */
thpool = 0             /* total hyper pool MB */
subsystem = ""         /* subsystem id */
cmdout. = ""           /* result of display command */

/* defines months for translation from display result */
month.JAN = "01"
month.FEB = "02"
month.MAR = "03"
month.APR = "04"
month.MAY = "05"
month.JUN = "06"
month.JUL = "07"
month.AUG = "08"
month.SEP = "09"
month.OCT = "10"
month.NOV = "11"
month.DEC = "12"

/* define message parsing templates */
msgps. = "."
msgps.DSNB401I = ". . bpname ',' ."
msgps.DSNB402I = ". . . . . . . . vpsize ."
msgps.DSNB403I = ". . . . . . . . . . hpsize ."
msgps.DSNB409I = ". . . hh ':' mm ':' ss ' ' mon day ', ' year"
msgps.DSNB411I = ". . . rgetpage . . . . . syncread . . . sgetpage ."
msgps.DSNB412I = ". . . . . . . . . . . . . pgrdsqpf ."
msgps.DSNB413I = ". . . . . . . . . . . . . pgrdltpf ."
msgps.DSNB414I = ". . . . . . . . . . . . . pgrddypf ."
msgps.DSNB420I = ". . . . bufupd . . . . pgwrt ."
msgps.DSNB430I = ". . . . . . . . . . . . . synhprd . . . . synhpwrt" ,
                 ". . . . asynhprd . . . . asynhpwrt . . . hprdfail ."
msgps.DSNB431I = ". . . . . . . . . . . asynadmfhprds" ,
                 ". . . asynadmfhpwrts . . . asynadmfhprdf ."

today = Date('B')
timenow = Time()
Parse Var timenow hh':'mm':'ss
timenowsec = hh*3600 + mm*60 + ss

/* Read in the input arguments  */

Parse Arg args

/* Extract each parameter and assign its value */

Do While args <> ""
  Parse Var args parm "(" value ")" args
  Parse Upper Var parm parm .
  Parse Upper Var value value .
  Select
    When parm == "SYSTEM" Then subsystem = value
    Otherwise nop
  End /* Select .. */
End  /* Do While.. */

/* Check that each parameter is valid  */

subsystem_len = Length(subsystem)
If subsystem_len > 4 Then Do
  Say "Error: subsystem length" subsystem_len "; Max is 4;"
  exit_rc = 8
End

If subsystem_len < 1 Then Do
  Say "Error: subsystem length" subsystem_len "; Min is 1;"
  exit_rc = 8
End

/* Exit if any errors found so far */
If exit_rc <> 0 Then Do
  Do i = syntaxstart to syntaxend
    Say Sourceline(i)
  End
  Exit exit_rc
End


/* Now execute DB2 command to display the stats */
/* get count of currently stacked commands */
q = queued()
Push "END"
Push "-DISPLAY BUFFERPOOL DETAIL"
x = OUTTRAP("cmdout.",'*',"CONCAT")
Signal Off Error
Address TSO "DSN SYSTEM("subsystem")"
exit_rc = rc
/* remove all remaining stacked DB2 commands */
Do While queued() - q \= 0;  Pull ; End
x = OUTTRAP("OFF")
If exit_rc <> 0 Then Do
  Say "Error: Execution of DSN SYSTEM("subsystem") failed; rc="exit_rc
  Do i=1 to cmdout.0; Say cmdout.i; End
  Exit exit_rc
End
Signal On Error

/* comment in for testing by reading a file of input data */
/*
fileindsn = "'BRACEYM.DBPRES.OUTPUT'"
/* now check it exists and if so read it in */
If Sysdsn(fileindsn) = "OK" Then Do
  /* Allocate file for read and read it all in */
  Address TSO "ALLOC F(FILEIN) DA("fileindsn") SHR"
  Address TSO "EXECIO * DISKR FILEIN (STEM cmdout. FINIS"
End
*/

/* Initialise report heading */

p=p+1;o.p="DBPRES" version"; " ,
          "Bufferpool Residency Times for DB2 subsystem:" subsystem
p=p+1;o.p="Run on" Date() "at " Time() "by User" Userid()
p=p+1;o.p="Expected residency times:"
p=p+1;o.p="Without Hiperpool: 300 to 600 seconds in Virtual Pool;"
p=p+1;o.p="With Hiperpool: 30 to 60 seconds in Virtual Pool;"
p=p+1;o.p="                300 to 600 seconds in Virtual + Hiper Pool;"
p=p+1;o.p=Left("BUFFER",6) Right("VPOOL",7) ,
          Right("HPOOL",7) Right("INTVAL",6) Right("GETPAGE",7) ,
          Right("VP RES",6) Right("VH RES",6) Right("HPREAD",6)

p=p+1;o.p=Left("POOL",6) Right("PAGES",7) ,
          Right("PAGES",7) Right("SECS",6) Right("PER SEC",7) ,
          Right("SECS",6) Right("SECS",6) Right("FAIL %",6)
/* heading for optional spreadsheet data */
r=r+1; s.r = "This section can be imported to a spreadsheet;"

/* Analyse captured output */

/* unravel messages */

Do j = 1 to cmdout.0
  If Left(cmdout.j,3) = "DSN" Then Do
    k=k+1; m.k = cmdout.j
  End
  Else m.k = m.k cmdout.j
End
m.0 = k

/* analyse each message in turn */
Do j = 1 to m.0
  Parse Var m.j msgno . message
  If msgno = "DSNB401I" & bpname <> "" Then Call CalculateResidency
  Interpret "Parse Var message" msgps.msgno
End
If bpname <> "" Then Call CalculateResidency

/* display the total bufferpool size in MB */
p=p+1;o.p="Total virtual pool:" Format(tvpool,8,0) "MB"
p=p+1;o.p="Total hyper   pool:" Format(thpool,8,0) "MB"
/* check if interval warning flag set */
If interval_w Then Do
  p=p+1;o.p="Warning: At least one interval outside recommended limits;"
  p=p+1;o.p="         Min:" interval_min "; Max:" interval_max
End
p=p+1; o.p = "DBPRES" version"; End of report;"

/* commented out - simple way to write to a file
outdsn = "'"Userid()".DBPRES.OUTPUT'"
If Sysdsn(outdsn) = "OK" Then Do
   Address TSO "ALLOC F(OUTDSN) DA("outdsn") SHR"
   Address TSO "EXECIO * DISKW OUTDSN ( STEM o."
   Address TSO "EXECIO * DISKW OUTDSN (FINIS STEM s."
   Address TSO "FREE F(OUTDSN)"
End
*/
/* write out report to screen */
Do i = 1 to p
  Say o.i
End
/* commented out - formatted for moving to a spreadsheet
Do i = 1 to r
  Say s.i
End
*/
/* commented out - display result of DISPLAY BUFFERPOOL DETAIL
Do j = 1 to cmdout.0
  Say cmdout.j
End
*/

Exit exit_rc

/* Function to calculate the residency times from the bufferpool stats*/

CalculateResidency:

day = Right('0'day,2)
sincedate = (year)(month.mon)(day)
sinceday = Date('B',sincedate,'S')
interval = (today - sinceday) * 86400 ,
           + timenowsec - hh*3600 - mm*60 - ss

/* Check if interval within recommended bounds */
If \(interval>interval_min & interval<interval_max) Then interval_w=1

/* check if interval greater than sensible calculation limits */
If interval > interval_fail Then Do
  p=p+1
  o.p = Left(bpname,6) ,
        "Calculations not performed for this pool;" ,
        "Interval of" interval ">" interval_fail ";"
  Return
End

tgetpage = rgetpage + sgetpage

/* data for spreadsheet */
r=r+1
s.r = subsys Date('E') Time() interval bpname ,
      vpsize hpsize tgetpage syncread pgrdsqpf,
      pgrdltpf pgrddypf bufupd pgwrt synhprd asynhprd ,
      asynadmfhprds synhpwrt asynhpwrt asynadmfhpwrts ,
      hprdfail asynadmfhprdf
/* calculate rates per second */
pgrddasd = (syncread + pgrdsqpf + pgrdltpf + pgrddypf)/interval
hprd = (synhprd + asynhprd + asynadmfhprds)/interval
hpwrt = (synhpwrt + asynhpwrt + asynadmfhpwrts)/interval
hprdf = (hprdfail + asynadmfhprdf)/interval
/* calculate getpages per second */
getpage = tgetpage/interval

/* calculate and accumulate the bufferpool sizes */
tvpool = tvpool + vpsize/256
thpool = thpool + hpsize/256
tpsize = vpsize + hpsize

/* Calculate VP residency time in seconds */
If hpsize > 0 Then Do
  If hpwrt=0 Then vpres =interval; Else vpres = vpsize/hpwrt
End
Else Do
  If pgrddasd=0 Then vpres = interval
  Else vpres = vpsize/pgrddasd
End
vpres = Min(interval,vpres)

/* Calculate VP+HP residency time in seconds */
If pgrddasd=0 Then tpres=interval; Else tpres = tpsize/pgrddasd
tpres = Min(interval,tpres)

/* Calculate HP read failure percentage      */
If hprd=0 Then hprdfp=0; Else hprdfp = 100*hprdf/hprd
/* format output data */
p=p+1
o.p = Left(bpname,6) Right(vpsize,7) ,
      Right(hpsize,7) Format(interval,6,0) Format(getpage,7,0) ,
      Format(vpres,6,0) Format(tpres,6,0) Format(hprdfp,6,0)
Return

Error:
exit_rc = rc
Say "Error message:"
Say "Non zero return code from external call"
Say "Return code: "exit_rc
Say "Call was in line "sigl
Say "Source line is: "
Say Sourceline(sigl)
Exit exit_rc