Buffer pool tuning in its simplest form is quite easy to explain : group objects that have a similar profile together .... Like in the farm , you won't put the wolf and a sheep together ...
So we start be putting catalog objects in BP0, In BP1 tablespaces and BP2 indexes ...
Then we can zoom in BP1 can be divided in 2 BP : Objects with random profile and Objects with Sequential profiles ....etc...
The question is , how can i get the informations. Their cost (overhead) is important. And your program must be very quick to catch all the IOs in your system (ideally written in Assembler with the higher run priority than DB2 itself ) ... all the things difficult to have. BufferPool Tool from Joel Goldstein is based on this principle. And more then 10 years i tried to catch and decode IFCID 6, 7 ...
I had fun writing these programs to catch the IFCID in C language , than to process it using Cobol ...
But IBM then provides this precious data in DIS BP LSTATS command, it costs nothing, and you have what you need with these commands.
If you submit this command for all the objects of your DB2 subsystem , each x minutes , than you have the I/O stats for your entire system without any noticeable overhead as these data are already "inside" your Db2.
So the steps are :
1/ Generate the display commands for your system. You can easily do this with DSNTEP2, and using this SQL :
SELECT
'-DIS BUFFERPOOL(' !! RTRIM(A.BPOOL) !! ') LSTATS(*)'
!! ' DBN('
!! RTRIM(A.DBNAME) !! ') SP('
!! A.PAGESET !! ')' AS CMD
FROM(
SELECT DISTINCT BPOOL, DBNAME, NAME AS PAGESET,
PARTITIONS AS PARTS
FROM SYSIBM.SYSTABLESPACE
UNION
-- THE MAX(PARTITION) IS USED TO RETURN ONE ROW ONLY FOR EACH INDEX
SELECT DISTINCT BPOOL, DBNAME, INDEXSPACE AS PAGESET
, MAX(PARTITION) AS PARTS
FROM SYSIBM.SYSINDEXES X, SYSIBM.SYSINDEXPART P
WHERE X.NAME=P.IXNAME
AND X.CREATOR=P.IXCREATOR
GROUP BY BPOOL, DBNAME, INDEXSPACE
) A
You can customize the SQL to get only the objects you want to (Example : Check I/O stats for all objects in BP32K)
2/ Prepare a JCL which submits these commands and store the output in a sequential file with DISP=MOD settings (So create the output file with RECFM=FB, LRECL=133)
//jobcard
//DISDB EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD DSN=SYSTMP.OUTPUT.DISBP,DISP=MOD //SYSTSIN DD * DSN SYSTEM(DBPX) -DIS BUFFERPOOL(BP32K) LSTATS(*) DBN(BA2PENB1) SP(NYSAI85 )
(...)
3/ Submit this REXX : this rexx submits the JCL 'hlv(ZZ)' previously prepared every x minutes (5 in this sample)
I use the REXXWAIT assembler provided in CBTTAPE , but the call to SLEEP is OK (uncomment the appropriate code section, using SLEEP , duree is number of seconds , duree=300 for 5 minutes)
/*REXX*/
filen='SYSTMP.OUTPUT.DISBP'
duree='00050000' /*hhmmsscc*/
DSNA="'HLV(ZZ)'"
i=0
do 72 /* boucle */
i=i+1
say time() 'submit' DSNA 'iteration' i
call logw
ADDRESS TSO "SUBMIT " DSNA
call gosleep
end
exit
LOGW:
Say 'Output to ' oufw
oufw = "'" !! filen !! "'"
Say 'Output to' oufw
"ALLOC FI(OUFw) DA("oufw") MOD CATALOG REUSE" ,
"LRECL(133) RECFM(F B) TRACKS SPACE(50,50) RELEASE"
rec.0=1
rec.1=date() time()
say 'record:' rec.1
"EXECIO 1 DISKW OUFw (STEM rec. "
"EXECIO 0 DISKW OUFw (FINIS"
"FREE DD(OUFW)"
return
Gosleep:
/* need UNIX Services active
Call SYSCALLS 'ON'
Address SYSCALL 'SLEEP' duree
say 'Sleep RC=' RC
Call SYSCALLS 'OFF' */
rc=rexxwait(duree)
if rc>0 then do; say 'error calling REXXWAIT' rc; exit 8; end
return
4/ When the JCL is ended, submit this Rexx to decode the output , and produce a CSV dataset that you will export to Excel
/*REXX*/ 00160003/*cette version est une version manuelle du display BP */ 00160003/*on lance le jcl display toutes les x minutes, il alimente un */ 00160003/*fichier , et ce rexx traite ce fichier */ 00160003/*La version Instream s'appelle REXBP2 */ 00160003TRACE O 00170003ARG NOMINP DEL 00190003"FREE FI(INP)" 00200003"ALLOC FI(INP) DSNAME('"NOMINP"') SHR" 00210003"FREE FI(OUT)" 00220003 00250003month = 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec' 00250003NOMOUTP = NOMINP !! '.OUT' 00260003If DEL = 'D' then 00260003 "DELETE '"NOMOUTP"'" 00260003"ALLOC F(OUT) DA('"NOMOUTP"') LRECL(150) SPACE(10,5), 00270003 CYL BLKSIZE(15000) RECFM(F,B) NEW" 00280003if rc > 0 then do; say "File allocation error" ; exit 8; end 00280003ENDF=0 00280003FINDK=0 00280003"execio 0 diskw out (OPEN" /*Open */ 680003"execio 0 diskr INP (OPEN" /*Open */ 680003/*ecrire 1er record for CSV = header */rec1= 'Date,Time,Hour,ObjType,DbName,ObjName,DsNum,bpname,' , 'vpcur,vpmax,' , 'vpccur,vpcmax,' , 'avgwtsync,maxwtsync,' , 'syncio,' , 'avgwtasync,maxwtasync,' , 'asynpg,' , 'asynio'"execio 1 diskw out (stem rec )"i=0 003220Tem467I=0 003220Tem453I=0 003220Tem455I=0 003220Tem456I=0 003220DO UNTIL ENDF=1 "EXECIO 1 DISKR INP (STEM TRC. )" 002900 IF TRC.0 < 1 THEN ENDF=1 003100 i=i+1 Msgid = word(TRC.1,1) /* Date value */ if words(TRC.1) = 4 & pos(word(trc.1,2),month) > 0 then do datex = word(trc.1,1) word(trc.1,2) word(trc.1,3) timex = word(trc.1,4) hourx = substr(word(trc.1,4),1,2) iterate end if Msgid = 'DSNB401I' then do BpnameO=BpnameN /* Old / New */ BpnameN=strip(word(trc.1,5),,',') iterate end if Msgid = 'DSNB499I' then do say '!!! DSNB499I Missing data !!!' rec1='!!! DSNB499I Missing data !!!' "execio 1 diskw out (stem rec )" 680003 iterate end if Msgid = 'DSNB467I' then do /* write previous DSNB467I record*/ call procrec Tem467I=1 vpcur=0 vpmax=0 vpccur=0 vpcmax=0 syncio=0 avgwtasync=0;maxwtasync=0 avgwtsync=0;maxwtsync=0 asynpg=0 asynio=0 /* try to ensure that we are well aligned */ y= pos('FOR',trc.1) if y = 30 then y = 34 else y = 33 Libel =substr(trc.1,y,35) parse var Libel Dum1 ObjType Dum2 DBName '.' ObjName say Libel say Objtype If Objtype = 'TABLE' then Objtype= 'T' else Objtype= 'I' /* on avance de 2 lignes */ 002900 "EXECIO 2 DISKR INP (STEM TRC. )" 002900 x=pos(':',trc.2,1) if x=0 then do;say "error : nfd";exit 8;end x=x+1 dsnum=substr(trc.2,x,6) end /* tant qu'on a pas trouve dsndb467i on cherche */ if Tem467I = 0 then iterate /*ici on a trouvé Tem467I dans ce record ou dans les precedents */ /* on s'attend a trouver soit 453I, soit 455I, soit 456I*/ if Msgid = 'DSNB453I' then do Tem453I=1 "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 vpcur=substr(trc.1,x,9) x=pos('=',trc.1,x) if x=0 then do;say "error = nfd";exit 8;end x=x+1 vpmax=substr(trc.1,x,9) "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 vpccur=substr(trc.1,x,9) x=pos('=',trc.1,x) if x=0 then do;say "error = nfd";exit 8;end x=x+1 vpcmax=substr(trc.1,x,9) iterate end if Msgid = 'DSNB455I' then do Tem455I=1 "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 avgwtsync=substr(trc.1,x,9) x=pos('=',trc.1,x) if x=0 then do;say "error = nfd";exit 8;end x=x+1 maxwtsync=substr(trc.1,x,9) "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 syncio=substr(trc.1,x,9) iterate end if Msgid = 'DSNB456I' then do Tem456I=1 "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 avgwtasync=substr(trc.1,x,9) x=pos('=',trc.1,x) if x=0 then do;say "error = nfd";exit 8;end x=x+1 maxwtasync=substr(trc.1,x,9) "EXECIO 1 DISKR INP (STEM TRC. )" 002900 x=pos('=',trc.1,1) if x=0 then do;say "error = nfd";exit 8;end x=x+1 asynpg=substr(trc.1,x,9) x=pos('=',trc.1,x) if x=0 then do;say "error = nfd";exit 8;end x=x+1 asynio=substr(trc.1,x,9) iterate endEND /* end do until *//* flush du record encours*/call procrec"execio 0 diskw out (FINIS" /*close file */"EXECIO 0 diskr inp (FINIS""free fi(out)""free fi(inp)"say 'fin du programme'exit 00718608procrec: 00718608 if (tem453i = 1 ! tem 455i = 1 ! tem456i=1) then do /*ecrire le record */
rec1= Datex !! ',' !! Timex !! ',', !! hourx !! ',' , !! ObjType !! ',' !! DbName!! ',' !! ObjName !! ',' , !! Dsnum !! ',' !! BpnameO !! ',' , !! vpcur !! ',' !! vpmax !! ',' , !! vpccur !! ',' !! vpcmax !! ',' , !! avgwtsync !! ',' !! maxwtsync !! ',' , !! syncio !! ',' , !! avgwtasync !! ',' !! maxwtasync !! ',' , !! asynpg !! ',' , !! asynio /* say 'ecrire' */ 680003 "execio 1 diskw out (stem rec )" 680003 tem453i=0 680003 tem455i=0 680003 tem456i=0 680003 endreturn
The jcl which submits this Rexx is :
//* 1ST PARAM : THE RESULT OF DISPLAY BP LSTATS //* 2E PARAM : IF D = DELETE .OUT DATASET BEFORE PROCESSING //REXBPF EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M,COND=(4,LT) //SYSEXEC DD DISP=SHR,DSN=Your REXX PDS //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSTSIN DD * REXBPF SYSTMP.OUTPUT.DISBP D /*
The Rexx provides an SYSTMP.OUTPUT.DISBP.OUT which looks like this
Date,Time,Hour,ObjType,DbName,ObjName,DsNum,bpname, vpcur,vpmax, vpccur,vpcmax, avgwtsync,maxwtsync, syncio, avgwtasync,maxwtasync, asynpg, asynio19 Dec 2017,07:16:45,07,T,BA2PENB1,NYSAI85 , 1 ,BP32K, 568 , 2056 , 0 , 2 , 1 , 6 , 2099 , 0 , 2 ,19 Dec 2017,07:16:45,07,T,BA2PENB1,NYSAI96 , 1 ,BP32K, 699 , 699 , 0 , 0 , 1 , 5 , 196 , 0 , 3 ,19 Dec 2017,07:16:45,07,T,BA2PENB1,NYSAI97 , 1 ,BP32K, 5 , 3094 , 0 , 0 , 2 , 11 , 439 , 0 , 2 ,19 Dec 2017,07:16:45,07,T,BA2PENB1,NYSAJTS , 1 ,BP32K, 7 , 7 , 0 , 4 , 1 , 1 , 1 ,0,0,0,019 Dec 2017,07:16:45,07,T,BA2PENB1,NYSAR73 , 1 ,BP32K, 0 , 434 , 0 , 0 , 1 , 5 , 236 , 0 , 2 ,19 Dec 2017,07:16:45,07,T,BA2PENB1,NYTSAI0B , 1 ,BP32K, 4 , 2544 , 0 , 0 , 1 , 5 , 190 , 0 , 1 ,
In Excel, with analysis :
This shows the BP32K bufferpool usage by objects by SyncIO
