DB2 Data Sharing Performance and Coupling Facility Enhancements for DB2 Version 8

(Originally posted 2007-05-08.)

Occasioned by some recent enhancements to MXG I thought it high time I talked about a couple of Coupling Facility commands that DB2 Version 8 can take advantage of:

(And thanks to Barry Merrill for the MXG information and the sample data I’m using here.)

MXG Version 25.04, May 7, 2007 provides support for these enhancements.

Coupling Facility Level (CFLEVEL) 13 introduced the WARM and RFCOM commands. These batch up pages for both the Write And Register process and the Read for Castout process. The former is for when pages have to be written to the coupling facility and the latter is for when pages are written out to disk by the castout owner at castout time. They’re intended to be more efficient than their single-page analogues.

DB2 still does single page actions – where appropriate.

DB2 Statistics Trace (SMF 100 in this case) provides instrumentation:

On the Castout side:

QBGLCM      RFCOM*REQUESTS*FOR*MULTIPLE*PAGES          3964
QBGLCR      RFCO*REQUESTS*FOR*SINGLE*PAGE              1900
QBGLRC      PAGES*CASTOUT                             31638

Using the above MXG variables, the number of pages castout using RFCOM is 31638 – 1900 or 29738 or 94%. The number of pages read for each RFCOM is 29738 / 3964 or 7.5.

Similarly one can do arithmetic with the WARM and WAR statistics:

QBGLMW      WARM*REQUESTS*FOR*MULTIPLE*PAGES           8066
QBGLWP      PAGES*WRITTEN*BY*WARM                     85449
QBGLWS      WAR*REQUESTS*FOR*SINGLE*PAGES             85714

Actually these last two numbers look suspicious: One might suspect that the number of WAR requests was 85714 – 85449 which actually is exceedingly small. Assuming the names are right (and they do reflect what’s in SDSNMACS) then one can conclude about half of the pages written were written by WAR and half by WARM (which is credible). The average number of pages written per WARM command is 85449 / 8066 or 10.6.

These are useful statistics as they show how these two CF commands (WARM and RFCOM) do make things more efficient by batching up requests. The other thing is I expect there will be conditions that prevent WARM and RFCOM from batching to this degree. Things in the category of “trickling”, for example. It might change the way we recommend configuring buffer pool and GBP thresholds.

I’d like to hear from other sites about their numbers. Especially the WAR / WARM ones.