EDM pool (BSTATDR)
This topic describes the EDM pool section of the DB2 statistics detail report (BSTATDR).
------------------------- -------- ------- ------- -------
PAGES IN RDS POOL (BELOW) 0 N/A N/A N/A
HELD BY CT 0 N/A N/A N/A
HELD BY PT 0 N/A N/A N/A
FREE PAGES 0 N/A N/A N/A
FAILS DUE TO POOL FULL 0 0.00 0.00 0.00
PAGES IN RDS POOL (ABOVE) 0 N/A N/A N/A
HELD BY CT 0 N/A N/A N/A
HELD BY PT 0 N/A N/A N/A
FREE PAGES 0 N/A N/A N/A
FAILS DUE TO POOL FULL 0 0.00 0.00 0.00
PAGES IN DBD POOL (ABOVE) 5850
HELD BY DBD 97
FREE PAGES 5753
FAILS DUE TO DBD PL FULL 0 0.00 0.00 0.00
PAGES IN STMT PL (ABOVE) 2500
HELD BY STATEMENTS 12
FREE PAGES 2488
FAILS DUE TO STMT PL FULL 0 0.00 0.00 0.00
PAGES IN SKEL PL (ABOVE) 1280
HELD BY SKCT 4
HELD BY SKPT 8
FREE PAGES 1268
FAILS DUE TO SKEL PL FULL 0 0.00 0.00 0.00
CT REQUESTS 265 11.04 1.00 0.92
CT NOT IN EDMPOOL 0 0.00 0.00 0.00
FAILS DUE POOL FULL 0 0.00 0.00 0.00
PAGES USED IN STMT POOL 12 0.00 0.04 0.04
FAILS DUE STMT POOL FULL 0 0.00 0.00 0.00
PAGES IN EDM STMT POOL 2500 0.00 9.43 8.68
FREE PAGES IN EDM STMT PL 2488 0.00 9.39 8.64
PAGES FOR DYN SQL CACHE N/P 0.00 0.00 0.00
CT REQS/NOT IN EDM N/P
PAGES IN DBD FREE CHAIN 5753 239.71 21.71 19.97
FAILS DUE TO DBD FULL 0 0 0 0
%STMT POOL IN USE 0.46
REQ FOR PT SECTS 1114 46.42 4.20 3.87
LOAD PT SECT/DASD 0 0.00 0.00 0.00
PT REQS/NOT IN EDM N/P
REQUESTS FOR DBD 846 35.25 3.19 2.94
LOAD DBD FROM DASD 1 0.04 0.00 0.00
DBD REQS/NOT IN EDM 846.00
STMTS IN GLOBAL CACHE 2 0.08 0.01 0.01
PAGES IN DBD 5850 243.77 22.08 20.31
%DBD POOL IN USE 0.02
The following table describes the fields in the EDM pool section.
Field | Description |
|---|---|
PAGES IN RDS POOL (BELOW) | Number of pages used below the bar for the RDS pool |
HELD BY CT | The number of pages below the bar used for cursor table sections (QISECT) This value is a snapshot of the number of pages allocated to cursor table sections (CTs). Tuning Tip: The ideal goal is to keep as many SKCTs, SKPTs, and DBDs in the EDM pool as possible to avoid I/O to the DB2 Directory. |
HELD BY PT | Number of pages below the bar used for package tables (QISEKT) This value is a snapshot of the number of pages allocated to working package tables (PTs). This number reflects the amount of storage consumed by working package tables. |
FREE PAGES | Number of free pages (QISEFREE) This counter is a snapshot of how many pages in the EDM pool are not being used by any CT or PT. Tuning Tip: A rough rule of thumb is that in a production system in which the workload is predictable, at least 15% of the EDM pool pages should be free to account for an unanticipated load of a large DBD. This amount can vary, however, because many shops run more plans than could practically be kept in memory. |
PAGES IN RDS POOL (ABOVE) | Number of pages used above the bar for the RDS pool |
HELD BY CT | The number of pages above the bar used for cursor table sections (QISECT) This value is a snapshot of the number of pages allocated to cursor table sections (CTs). Tuning Tip: The ideal goal is to keep as many SKCTs, SKPTs, and DBDs in the EDM pool as possible to avoid I/O to the DB2 Directory. |
HELD BY PT | Number of pages above the bar used for package tables (QISEKT) This value is a snapshot of the number of pages allocated to working package tables (PTs). This number reflects the amount of storage consumed by working package tables. |
FREE PAGES | Number of free pages (QISEFREE) This counter is a snapshot of how many pages in the EDM pool are not being used by any CT or PT. Tuning Tip: A rough rule of thumb is that in a production system in which the workload is predictable, at least 15% of the EDM pool pages should be free to account for an unanticipated load of a large DBD. This amount can vary, however, because many shops run more plans than could practically be kept in memory. |
PAGES IN DBD POOL (ABOVE) | Number of pages used for the DBD pool (QISEDBD) |
HELD BY DBD | Number of pages used for DBDs (QISEDBD) This counter is a snapshot of the number of pages currently occupied by database descriptors (DBDs). Tuning Tip: This count is the most important count of EDM pool objects because of an inherent rule that DBDs must occupy contiguous storage. SKPTs, SKCTs, CTs, or PTs do not have this restriction. This may result in EDM pool fragmentation. Ways of avoiding this problem include keeping DBD sizes to a reasonable size by not allowing more than 100 objects per DB2 database. If the number of DBD pages occupy more than half the EDM pool, consider increasing the size of the pool. |
FREE PAGES | Number of free pages (QISEFREE) This counter is a snapshot of how many pages in the EDM pool are not being used by any DBD. Tuning Tip: A rough rule of thumb is that in a production system in which the workload is predictable, at least 15% of the EDM pool pages should be free to account for an unanticipated load of a large DBD. This amount can vary, however, because many shops run more plans than could practically be kept in memory. |
PAGES IN STMT PL (ABOVE) | Storage allocated above the bar for statement plan processing (QISESQCA) |
HELD BY STATEMENTS | Number of pages used for statement plans |
FREE PAGES | Number of free DBD pages (QISEDLRU) This counter is a snapshot of how many pages in the EDM pool are not being used by any statements. Tuning Tip: A rough rule of thumb is that in a production system in which the workload is predictable, at least 15% of the EDM pool pages should be free to account for an unanticipated load of a large DBD. This amount can vary, however, because many shops run more plans than could practically be kept in memory. |
PAGES IN SKEL PL (ABOVE) | Storage allocated above the bar for skeleton tables |
HELD BY SKCT | Number of pages used for skeleton cursor tables (QISESKCT) This value is a snapshot of the number of EDM pool pages allocated to skeleton cursor tables (SKCTs). Skeleton cursor tables are the DB2 plan information which is read from directory table SCT01. |
HELD BY SKPT | Number of pages used for skeleton package tables (QISESKPT) This value is a snapshot of the number of pages allocated to skeleton package tables (SKPTs). This value reflects the amount of storage used by skeleton package tables. |
FREE PAGES | Number of free SKEL pages (QISEKLRU) This counter is a snapshot of how many pages in the EDM pool are not being used by any SKCT or SKPT. Tuning Tip: A rough rule of thumb is that in a production system in which the workload is predictable, at least 15% of the EDM pool pages should be free to account for an unanticipated load of a large DBD. This amount can vary, however, because many shops run more plans than could practically be kept in memory. |
CT REQUESTS | Number of requests for cursor table sections (QISECTG) Tuning Tip: This number is incremented every time DB2 needs a new 4K cursor table section (CT) of the skeleton cursor table (SKCT). The request can be resolved by looking for a copy in the EDM pool; if a copy does not exist, a read operation must be made to the directory table SCT01. This number reflects how many sections were requested during a specific interval period. |
FAILS DUE POOL FULL | Number of failures because EDM pool was full (QISEFAIL) This counter is incremented when an operation cannot proceed because the EDM pool was full. This situation is extremely undesirable when all pages in the Environmental Descriptor Manager pool (EDM pool) are allocated and in use as database descriptors (DBDs), skeleton cursor tables (SKCTs, which are internal copies of plans), skeleton package tables (SKPTs, which are internal copies of packages), and working cursor tables (CTs), and working package tables (PTs). No other operation can be commenced until one or more pages are freed in this pool. Tuning Tip: This number should be as close to zero as possible. If this situation occurs, consider increasing the number of pages in the EDM pool in DSNZPARM, on installation panel DSNTIPC. |
PAGES USED IN STMT POOL | Number of pages used for the statement pool (QISEDYNP) |
FAILS DUE POOL FULL | Number of EDM pool statement cache full failures (QISECFAL) |
PAGES IN EDM STMT POOL | Number of pages in the statement pool (QISECPGE) |
FREE PAGES IN EDM STMT PL | Number of free pages in the statement pool (QISECFRE) |
PAGES FOR DYN SQL CACHE | Number of inserts into the dynamic statement cache (QISEDSI) |
CT REQS/NOT IN EDM | Ratio of the number of requests for cursor table sections to the number of times CT sections not found in the EDM pool (QISECTG/QISECTL) |
PAGES IN DBD FREE CHAIN | Number of pages used for the DBD pool |
FAILS DUE TO DBD FULL | Number of DBD pool full failures (QISEDFAL) |
%STMT POOL IN USE | Percentage of pages in the statement pool that are in use |
REQ FOR PT SECTS | Number of requests for package table sections (QISEKTG). This counter is incremented every time a package section (PT) is requested Tuning Tip: Ideally, an SKPT (skeleton package table) with this PT will already be in the EDM pool. If not, the SKPT must be read from directory table SPT01. |
LOAD PT SECT/DASD | Number of times package table sections not found in EDM pool (QISEKTL) This counter is incremented every time a request for a skeleton package table section is made on behalf of a calling thread but the SKPT was not found in the EDM pool. Tuning Tip: Ideally, an SKPT (skeleton package table) will already be in the EDM pool. If not, it must be read from directory table SPT01. |
PT REQS/NOT IN EDM | Ratio of the number of requests for package table sections to the number of times package table sections not found in EDM pool (QISEKTG/QISEKTL) |
REQUESTS FOR DBD | Number of requests for DBDs (QISEDBDG) This number reflects the total number of requests for database descriptors (DBDs). Requests for DBDs are honored by either finding a working copy previously loaded into the EDM pool or by reading directory table DBD01. |
LOAD DBD FROM DASD | Number of times DBD not found in the EDM pool (QISEDBDL) This counter reflects the number of times the database descriptor for a table is not found in the EDM pool. It must be read from the catalog table DBD01 into contiguous storage in the EDM pool. Tuning Tip: If possible, the DBDs can be preloaded into the EDM pool by issuing the -DISPLAY DATABASE(*) command to seed the EDM pool right after DB2 start. This situation might not account for all fragmentation that can occur because of age, but it can establish a baseline of how large the pool should be sized. |
DBD REQS/NOT IN EDM | Ratio of the number of requests for DBDs to the number of times DBD not found in the EDM pool (QISEDBDG/QISEDBDL) |
STMTS IN GLOBAL CACHE | Number of statements in the global cache (QISESTMT) |
PAGES IN DBD | Number of pages used for the DBD pool (QISEDBD) |
%DBD POOL IN USE | Percentage of pages in the DBD pool that are in use |
Related topic