Pool detail buffer statistics table (DMRSBFDT)
The following table describes the columns in the DMRSBFDT and DMRSBSUM tables:
Performance report column name | SMF field name | Used in report | Field type | Description |
|---|---|---|---|---|
DMRTABLEVERSION | Not applicable | NO | Char(8) | Version of Performance Reporter that created or modified the table definition |
DMRVERSION | Not applicable | NO | Char(8) | Version of Performance Reporter that created or modified the row |
SYSTEMID | SM100SID | YES | Char(4) | System ID (SMF ID) |
SUBSYSTEM | SM100SSI | YES | Char(4) | Subsystem ID - name of Db2 subsystem |
QWHS - Standard Header | ||||
DB2VERSION | QWHSRN QWHSSUBV | NO | Char(6) | Version of Db2 that created the record For versions of Db2 prior to 8.1, a value of 0000 is used for QWHSSUBV because the field was not defined. |
INTERVAL | None | YES | Integer | Interval for the summary accounting table |
TRANSCNT | QWACPCNT for rollup records, otherwise, see description | YES | Integer | Transaction thread count For summary tables, this field contains the number of transactions that were used to calculate the column values. This value is 1 for each detail statistics record. |
IFCIDSEQ# | QWHSISEQ | NO | Integer | IFCID sequence number |
DATETIME | QWHSSTCK | YES | time stamp | Date and time record was created |
DATE | QWHSSTCK | YES | date | Date record was created |
YEAR | QWHSSTCK | YES | Char(4) | Year record was created |
MONTH | QWHSSTCK | YES | Char(2) | Month record was created |
DAY | QWHSSTCK | YES | Char(2) | Day record was created |
TIME | QWHSSTCK | YES | time | Time record was created |
HOUR | QWHSSTCK | YES | Char(2) | Hour record was created |
DAYOFWEEK# | QWHSSTCK | NO | smallint | Relative day of week, 1 to 7, where Monday=1 and Sunday=7 |
DAYOFWEEK | QWHSSTCK | NO | Char(3) | MON, TUE, WED, THU, FRI, SAT, SUN |
WEEK# | QWHSSTCK | NO | Integer | Week number relative to the 1 January 1900 epoch |
LOCATION | QWHSLOCN | YES | Char(16) | Local location name (Db2 subsystem ID if not defined) |
QWHA - Data Sharing | ||||
GROUPNAME | QWHADSGN | YES | Char(8) | Data sharing group name |
MEMBERNAME | QWHAMEMN | YES | Char(8) | Data sharing member name |
QBST - Buffer Manager | ||||
BPNAME | QBSTPID | NO | Char(6) | BP0 - BP32K9 character format buffer pool ID |
BPGETPAGE | QBSTGET | YES | Integer | Number of GETPAGES |
BPREADS | QBSTRIO | YES | Integer | Synchronous read I/O |
BPDWV | QBSTDWV | YES | Integer | Number of times the vertical deferred write threshold was reached |
BPFAILMAX | QBSTXFL | YES | Integer | Expansions failed - pool was full |
BPFAILSOS | QBSTXFV | YES | Integer | Expansions failed - GETMAIN |
BPPGUPDAT | QBSTSWS | YES | Integer | Number of page update requests |
BPPGWRITN | QBSTPWS | YES | Integer | Pages written |
BPASYNCWR | QBSTWIO | YES | Integer | Asynchronous writes |
BPACTBUFF | QBSTCBA | YES | Integer | Current active buffers |
BPPAGEINRD | QBSTRPI | YES | Integer | Pageins for read I/O |
BPPAGEINWR | QBSTWPI | YES | Integer | Pageins for write I/O |
BPOPENSOK | QBSTDSO | NO | Integer | Number of successful opens |
BPSYNCWR | QBSTIMW | YES | Integer | Immediate (synchronous) write I/O |
BPPFREQS | QBSTSEQ | YES | Integer | Number of sequential prefetch requests |
BPFPAGESRD | QBSTSPP | YES | Integer | Prefetch pages read |
BPPFNOBFR | QBSTSPD | YES | Integer | Prefetch disabled - no buffers |
BPPFNORDN | QBSTREE | YES | Integer | Prefetch disabled - no read engine |
BPDEFWRITE | QBSTDWT | YES | Integer | Times deferred write threshold reached |
BPUNLKCAST | QBSTPCO | NO | Integer | Number of pages on unlock castout |
BPCASTOUTIO | QBSTCIO | NO | Integer | Number of I/O on castout |
BPDMCRITIC | QBSTDMC | YES | Integer | Number of times the Data Management critical threshold reached |
BPMIGDS | QBSTMIG | YES | Integer | Migrated data sets encountered |
BPRTO | QBSTRTO | YES | Integer | Number of recall timeouts |
BPPIO | QBSTPIO | YES | Integer | Number of aynchronous read I/Os because of sequential prefetch |
BPPFNOWKF | QBSTWKPD | YES | Integer | Prefetch abort - zero quantity |
BPMAX | QBSTMAX | YES | Integer | Number of work files not created because of insufficient buffer resources |
BPMAXWKFIL | QBSTWFM | YES | Integer | Maximum work files in merge |
BPDISTREAD | QBSTWDRP | YES | Integer | Pages for destructive read |
BPDEQVDWQD | QBSTWBVQ | YES | Integer | Dequeue from VDWQ for destructive read |
BPMERGPASS | QBSTWFR | YES | Integer | Number of merge passes |
BPWKFLMERG | QBSTWFT | YES | Integer | Total work files in merge |
BPWKREJBUF | QBSTWFD | YES | Integer | Work files rejected / no buffer |
BPMERGNBUF | QBSTWFF | YES | Integer | Merge passes with insufficient buffers |
BPLISTPREF | QBSTLPF | YES | Integer | List prefetch requests |
BPDPF | QBSTDPF | YES | Integer | Number of dynamic prefetch requests |
BPVPA | QBSTVPA | YES | Integer | Number of successful virtual buffer pool expansions or contractions because of the ALTER BUFFERPOOL command |
BPVPL | QBSTVPL | YES | Integer | Number of buffer pools allocated for a virtual buffer pool |
BPDPP | QBSTDPP | YES | Integer | Number of asynchronous page reads because of dynamic prefetch |
BPLPP | QBSTLPP | YES | Integer | Number of asynchronous page reads because of list prefetch |
BPDIO | QBSTDIO | YES | Integer | Number of asynchronous read I/Os because of dynamic prefetch |
BPLIO | QBSTLIO | YES | Integer | Number of asynchronous read I/Os because of list prefetch |
BPSGT | QBSTSGT | YES | Integer | Number of GETPAGE requests issued by sequential access requesters |
BPSIO | QBSTSIO | YES | Integer | Number of synchronous read I/Os issued by sequential access requesters |
BPNGT | QBSTNGT | YES | Integer | Number of times conditional GETPAGE requests could not be satisfied for this buffer pool |
BPXIS | QBSTXIS | YES | Integer | Highest number of concurrent prefetch I/O streams allocated for supporting queries processed in parallel in this buffer pool |
BPJIS | QBSTJIS | YES | Integer | Number of requested prefetch I/O streams denied because of a storage shortage in the buffer pool |
BPPQO | QBSTPQO | YES | Integer | Number of requests made for processing queries in parallel in this buffer pool |
BPPQF | QBSTPQF | YES | Integer | Number of times during this statistics interval Db2 could not allocate the requested number of buffer pages to allow a parallel group to run to the planned degree |
BPPL1 | QBSTPL1 | YES | Integer | Number of occurrences when the prefetch quantity is reduced from normal to one-half of normal |
BPPL2 | QBSTPL2 | YES | Integer | Number of occurrences when the prefetch quantity is reduced from one-half to one-quarter of normal |
BPLPLADDS | QBSTLPL | NO | Integer | Number of times pages added to LPL |
DMRACSHARE | None | NO | Integer | DMR column used only as a marker during configuration to delete data sharing columns, if requested |
QBSTFLG | QBSTFLG | NO | Binary(1) | Buffer manager QBST flags |
QBSTNSG | QBSTNSG | NO | Bigint | Number of conditional sequential GETPAGE requests that failed because the page was not in the buffer pool |
QBSTSYIO | QBSTSYIO | NO | Bigint | Number of successful read I/Os using zHyperLink |
QBSTSIOC | QBSTSIOC | NO | Bigint | Number of read I/Os hitting disk cache (no zHL) |
QBGL - Group Buffer Pool | ||||
BPOOLID | QBGLGN | NO | Integer | Buffer pool ID |
GBPREADINVBD | QBGLXD | NO | Integer | Synchronous coupling facility reads caused by invalid buffer and with data returned |
GBPREADINVBR | QBGLXR | NO | Integer | Synchronous coupling facility reads caused by invalid buffer with no data returned and a directory entry created |
GBPREADNOPGD | QBGLMD | NO | Integer | Synchronous coupling facility reads caused by page not in buffer pool and with data returned |
GBPREADNOPGR | QBGLMR | NO | Integer | Synchronous coupling facility reads caused by page not in buffer pool with no data returned and a directory entry created |
GBPWRITCHG | QBGLSW | NO | Integer | Changed pages written synchronously to group buffer pool |
GBPWRITCLEAN | QBGLWC | NO | Integer | Clean pages written synchronously to group buffer pool |
GBPCASTCLASS | QBGLCT | NO | Integer | Group buffer pool castout because class threshold detected |
GBPCASTGBP | QBGLGT | NO | Integer | Group buffer pool castout because group buffer pool threshold detected. |
GBPAWRITCHG | QBGLAW | NO | Integer | Changed pages asynchronously written to group buffer pool |
GBPAWRITCLEAN | QBGLAC | NO | Integer | Clean pages asynchronously written to group buffer pool |
GBPCASTOPS | QBGLRC | NO | Integer | Castout operations performed |
GBPNOWRITE | QBGLWF | NO | Integer | Coupling facility write not complete |
GBPRDSTGST | QBGLOS | NO | Integer | Number of coupling facility requests to read storage statistics |
GBPCHKPT | QBGLCK | NO | Integer | Number of group buffer pool checkpoints triggered by this member |
GBPUNLKCAST | QBGLUN | NO | Integer | Number of coupling facility requests to unlock the castout lock on the pages |
GBPRDCASTCL | QBGLCC | NO | Integer | Number of coupling facility requests to read the castout class |
GBPRDCASTST | QBGLCS | NO | Integer | Number of coupling facility requests to read the castout statistics |
GBPDELETE | QBGLDN | NO | Integer | Number of group buffer pool requests to delete all directory and data entries for a page set or partition |
GBPRDDIR | QBGLRD | NO | Integer | Number of coupling facility requests to read directory information |
GBPREGPG | QBGLRG | NO | Integer | Number of coupling facility requests to register a page |
GBPUNREGPG | QBGLDG | NO | Integer | Number of coupling facility requests to unregister a page |
GBPREGPGLST | QBGLAX | NO | Integer | Number of requests to register a page list in the coupling facility |
GBPRETVCHP | QBGLAY | NO | Integer | Number of coupling facility reads to retrieve a changed page from the group buffer pool as a result of feedback from the request to register a page list |
GBPEXPLICITXI | QBGLEX | NO | Integer | Number of explicit cross-invalidations |
GBPWRITSTG2 | QBGL2F | NO | Integer | Number of coupling facility requests to write changed pages to the secondary group buffer pool for duplexing that failed due to a lack of storage in the coupling facility |
GBPWRITCHK2 | QBGL2S | NO | Integer | Number of completion checks for writes to the secondary GBP that were suspended because the write had not yet completed processing |
GBPDELNMLST2 | QBGL2D | NO | Integer | Number of group buffer pool requests to the secondary group buffer pool to delete a list of pages after they have been castout from the primary group buffer pool |
GBPRDCASTST2 | QBGL2R | NO | Integer | Number of coupling facility requests to read the castout statistics for the secondary group buffer pool. Issued by group buffer pool structure owner to check for orphaned data entries in the secondary |
GBPDELNM2 | QBGL2N | NO | Integer | Number of group buffer pool requests to delete a page from the secondary group buffer pool. Issued by group buffer pool structure owner to delete orphaned data entries in the secondary as part of the garbage collection logic |
GBPASYNPRIM | QBGLHS | NO | Integer | Asynchronous requests for primary GBP |
GBPASYNSEC | QBGL2H | NO | Integer | Asynchronous requests for secondary GBP |
GBPDEPGETPG | QBGLGG | NO | Integer | Getpages for GBP-dependent pages |
GBPPLKSPMAP | QBGLP1 | NO | Integer | Page P-lock requests for space map pages |
GBPPLKDATA | QBGLP2 | NO | Integer | Page P-lock requests for data pages |
GBPPLKIDX | QBGLP3 | NO | Integer | Page P-lock requests for index leaf pages |
GBPPLKUNLK | QBGLU1 | NO | Integer | Page P-lock unlock requests |
GBPPSUSSPMAP | QBGLS1 | NO | Integer | Page P-lock suspensions for space map pages |
GBPPSUSDATA | QBGLS2 | NO | Integer | Page P-lock suspensions for data pages |
GBPPSUSIDX | QBGLS3 | NO | Integer | Page P-lock suspensions for index leaf pages |
GBPPNEGSPMAP | QBGLN1 | NO | Integer | Page P-lock negotiations for space map pages |
GBPPNEGDATA | QBGLN2 | NO | Integer | Page P-lock negotiations for data pages |
GBPPNEGIDX | QBGLN3 | NO | Integer | Page P-lock negotiations for index leaf pages |
GBPWARMULTI | QBGLWM | NO | Integer | Number of Write And Register Multiple (WARM) requests |
GBPWAR | QBGLWS | NO | Integer | Number of Write And Register (WAR) requests |
GBPRFCOM | QBGLCM | NO | Integer | Number of Read for Castout Multiple (RFCOM) requests |
GBPRFCO | QBGLCR | NO | Integer | Number of Read for Castout (RFCO) requests |
GBPWWARM | QBGLWP | NO | Integer | Number of pages written using Write and Register Multiple (WARM) requests |
QBGLFLG | QBGLFLG | NO | Binary(1) | Buffer manager group buffer pool statistics flag |
QBGLWX | QBGLWX | NO | Bigint | Number of IXLCACHE requests with async XI (cross-invalidation) |
QBGLSU | QBGLSU | NO | Bigint | Number of IXLAXISN synch-up calls |
QBGLAS | QBGLAS | NO | Bigint | Number of IXLAXISN suspends waiting for the cross-invalidations to complete |
GBPWARND | QBGLWA | NO | Bigint | Number of changed pages that were written to disk due to conditional write failures to the group buffer poolConditional write failures to the group buffer pool can result in a "write-around" (writing the changed pages to disk). |
DMRAUTOCUST | None | NO | Integer | DMR column used only as a marker during configuration; all columns following this one (up to DMRAUTOLONG) in the create table member are not used in the reports (NO in Used in Report column) and can be deleted as a group as a configuration option |
DMRAUTOLONG | None | NO | Integer | DMR column used only as a marker during configuration; all columns following this one in the create table member are long name versions of the corresponding fields and can be deleted as a group as a configuration option |
LOCATION_L | QWHSLOCN | NO | varchar(128) | Long name version of the local location (Db2 subsystem ID if not defined) |