DB2 for z/OS: Static SQL and the EDM Pool
A lot of people responsible for the management of DB2 for z/OS systems regularly look over DB2 monitor statistics reports or online displays to verify that all is well or to identify (and subsequently resolve) anything that is amiss. This is good. Some of these folks are a little confused by what they see in the part in these reports pertaining to the EDM pool (long name: environmental descriptor manager pool), particularly the fields related to static SQL execution. This is not good. If you're going to make the best use of DB2 statistics trace data (nicely formatted by your monitor product), you need to understand the activity behind the numbers. If SKPTs and CTs and PTs and related hit ratios seem rather opaque to you, relax - you're not alone. I hope that the information provided in this blog entry will clear things up for you.
[Note: for consistency's sake, when referring to a program bound directly into a plan I'm going to assume that said plan was bound with ACQUIRE(USE) as opposed to ACQUIRE(ALLOCATE). ACQUIRE(USE) is the default for BIND PLAN. ACQUIRE is not an option for BIND PACKAGE - all packages function as though bound with ACQUIRE(USE).]
OK, so you start with a program that contains embedded (aka static) SQL statements. You precompile this program, and one of the outputs of that operation is a DBRM (database request module). For the program to function properly (from a DB2 perspective), the SQL statements contained in the DBRM have to be turned into a "control structure" that DB2 will use at statement execution time. This is accomplished by way of the BIND command. If the DBRM is bound directly into a DB2 plan (quite uncommon these days), the aforementioned control structure will be placed in a table in the DB2 directory (database DSNDB01) called the skeleton cursor table. If the DBRM is bound into a package (the norm), the resultant control structure goes into the skeleton package table, also in the directory.
The static SQL-containing program, having been precompiled, is subsequently compiled and linked and is ready to run. At execution time, it "issues" (in effect) an SQL statement by making a call to DB2 which includes the program name and a statement number and an identifier called a consistency token (these calls are inserted into the source code at precompile time, and the consistency token is also inserted into the DBRM and the bind-produced control structure). DB2 will respond to this call by looking in the EDM pool in the database services address space (aka DBM1) to see if the requisite sections of the control structure are there (some control structure sections contain header and directory information, and other sections correspond to SQL statements to be executed). Where DB2 looks is in the part of the EDM pool used for SKPTs (skeleton package table sections, if we're talking about a package) or SKCTs (skeleton cursor table sections, for programs bound directly into a plan). If the needed section or sections are not found, they are loaded into the EDM pool from the DB2 directory (via buffer pool BP0). In addition, copies of the sections are placed in the part of the EDM pool used for PTs (package table sections) or CTs (cursor table sections). Basically, the PT part of the EDM pool holds copies of control structure sections that are in the SKPT part of the EDM pool, with the PT copies being specific to a thread through which the static SQL-containing program is executing (the thread is the program's connection to the DB2 for z/OS subsystem). This also describes the relationship between the SKCT and CT parts of the EDM pool.
DB2 uses 4-KB chunks of EDM pool space for package and plan control structure sections. When a static SQL-containing program commits a unit of work, PT or CT pages that it has used are released in the EDM pool, thereby making them stealable, if the package or plan was bound with RELEASE(COMMIT). If the package or plan was bound with RELEASE(DEALLOCATE), the associated PT or CT pages in the EDM pool will remain non-stealable until the thread through which the program was executed is deallocated (i.e., terminated). An SKPT or SKCT page in the EDM pool will become stealable when a DB2-maintained "use count" for the SKPT or SKCT section in the page reaches zero (this use count is decremented each time the section is copied to the PT or CT part of the EDM pool on behalf of a thread).
On now to the DB2 monitor statistics report (and your monitor probably allows you to view the same information in an online display of subsystem-level information). You will see in the EDM pool section of the report an indication of the number of pages in the pool that hold SKPT and SKCT sections, and the number of pages that hold thread-related copies of package table (PT) and cursor table (CT) sections (DBDs, or database descriptors, also go into the EDM pool, but starting with DB2 for z/OS V8 these are loaded into a separate part of the pool above the 2 GB "bar" in the DBM1 address space). Additionally, you will see the number of free pages in the pool. These are pages that weren't holding anything when the data on which the report (or online display) is based was generated. [This and the other values mentioned thus far are "snapshot" (i.e., point-in-time) values.] Generally speaking, you shouldn't worry too much if the number of free pages is a relatively small percentage of the pages in the EDM pool (e.g., 10-20%), because in most cases a majority of the "in-use" pages are stealable (note that if your monitor reports a percentage of non-stealable pages in use, this probably applies only to non-stealable pages that hold PT or CT sections, as opposed to non-stealable SKPT and SKCT sections).
A little further down in the report, you'll see the hit ratios that pertain to requests for package and cursor table sections. A "not found" situation occurs when a section of a package or plan needed for the execution of a static SQL statement embedded in a running program is not found in the part of the EDM pool that holds sections of SKPTs (for packages) or SKCTs (for programs bound directly into plans). When that happens, the required section is loaded into the SKPT or SKCT part of the EDM pool from the skeleton package table or skeleton cursor table in the DB2 directory. That section is also copied to the PT or CT part of the EDM pool for the requesting thread (that being the thread through which the program is being executed). Because most programs at most DB2 for z/OS sites are bound into packages as opposed to being bound directly into plans, it is likely that you will see many more PT requests than CT requests (and more pages "held by PT" versus "held by CT"). Unless your EDM pool is pretty small (and these days, I tend to see pools that have 100-200 MB or more of space for SKPTs, SKCTs, PTs and CTs), your PT hit ratio (PT requests minus PT not found occurrences, divided by PT requests) will probably be very high (98% or more is not unusual). That's good (throughput is slightly impeded when package or plan sections have to be read into the EDM pool from the directory on disk). If the volume of CT requests on your system is low, you might see a relatively low CT hit ratio (e.g., under 70%). Is that bad? Probably not. Obviously, the first time a static SQL-containing program is executed after a DB2 start-up, a PT or CT "not found" situation will result because the needed section or sections have not yet been placed in the SKPT or SKCT part of the EDM pool (they're loaded on-demand). If programs bound directly into plans are rarely executed, there will be little in the way of repeated requests for CT sections that have previously been loaded into the SKCT part of the EDM pool, and since those "re-requests" are what drive a hit ratio higher, the CT hit ratio might be low. If it is low, don't worry about it if the CT request activity level is small compared to the number of PT requests on your system.
Finally, a word on "FAILS DUE TO POOL FULL." This happens when a requested package or plan section cannot be loaded into the EDM pool because the pool was full (the result is a program abend). That means no free pages and no stealable in-use pages. This tends to be quite a rare occurrence. When it does happen, it can caused by packages bound with RELEASE(DEALLOCATE) being executed via threads that persist for a long time before terminating. CICS-DB2 protected threads can be of this variety, persisting for hours before terminating thanks to thread re-use. If not many programs are bound with RELEASE(DEALLOCATE), the impact on the number of non-stealable PT (package section-holding) pages shouldn't be too significant. If lots of packages are bound with RELEASE(DEALLOCATE) and they are executed by threads that stick around for a long time, the part of the EDM pool used for PT sections can grow substantially, and lot of this space could be non-stealable. You can use RELEASE(DEALLOCATE) when binding programs (it provides a CPU efficiency benefit, especially for "small" programs that are executed by way of persistent threads), but try not to overdo it: the best candidates are programs that are frequently executed and are associated with threads that persist through commits. You can find out more about RELEASE(DEALLOCATE) versus RELEASE(COMMIT) in a blog entry I posted a few weeks ago.
I hope now that SKPT and SKCT and PT and CT are not abstract letter-combinations for you. Knowing what they're all about, you can be that much better at monitoring the health of your DB2 for z/OS subsystem.
[Note: for consistency's sake, when referring to a program bound directly into a plan I'm going to assume that said plan was bound with ACQUIRE(USE) as opposed to ACQUIRE(ALLOCATE). ACQUIRE(USE) is the default for BIND PLAN. ACQUIRE is not an option for BIND PACKAGE - all packages function as though bound with ACQUIRE(USE).]
OK, so you start with a program that contains embedded (aka static) SQL statements. You precompile this program, and one of the outputs of that operation is a DBRM (database request module). For the program to function properly (from a DB2 perspective), the SQL statements contained in the DBRM have to be turned into a "control structure" that DB2 will use at statement execution time. This is accomplished by way of the BIND command. If the DBRM is bound directly into a DB2 plan (quite uncommon these days), the aforementioned control structure will be placed in a table in the DB2 directory (database DSNDB01) called the skeleton cursor table. If the DBRM is bound into a package (the norm), the resultant control structure goes into the skeleton package table, also in the directory.
The static SQL-containing program, having been precompiled, is subsequently compiled and linked and is ready to run. At execution time, it "issues" (in effect) an SQL statement by making a call to DB2 which includes the program name and a statement number and an identifier called a consistency token (these calls are inserted into the source code at precompile time, and the consistency token is also inserted into the DBRM and the bind-produced control structure). DB2 will respond to this call by looking in the EDM pool in the database services address space (aka DBM1) to see if the requisite sections of the control structure are there (some control structure sections contain header and directory information, and other sections correspond to SQL statements to be executed). Where DB2 looks is in the part of the EDM pool used for SKPTs (skeleton package table sections, if we're talking about a package) or SKCTs (skeleton cursor table sections, for programs bound directly into a plan). If the needed section or sections are not found, they are loaded into the EDM pool from the DB2 directory (via buffer pool BP0). In addition, copies of the sections are placed in the part of the EDM pool used for PTs (package table sections) or CTs (cursor table sections). Basically, the PT part of the EDM pool holds copies of control structure sections that are in the SKPT part of the EDM pool, with the PT copies being specific to a thread through which the static SQL-containing program is executing (the thread is the program's connection to the DB2 for z/OS subsystem). This also describes the relationship between the SKCT and CT parts of the EDM pool.
DB2 uses 4-KB chunks of EDM pool space for package and plan control structure sections. When a static SQL-containing program commits a unit of work, PT or CT pages that it has used are released in the EDM pool, thereby making them stealable, if the package or plan was bound with RELEASE(COMMIT). If the package or plan was bound with RELEASE(DEALLOCATE), the associated PT or CT pages in the EDM pool will remain non-stealable until the thread through which the program was executed is deallocated (i.e., terminated). An SKPT or SKCT page in the EDM pool will become stealable when a DB2-maintained "use count" for the SKPT or SKCT section in the page reaches zero (this use count is decremented each time the section is copied to the PT or CT part of the EDM pool on behalf of a thread).
On now to the DB2 monitor statistics report (and your monitor probably allows you to view the same information in an online display of subsystem-level information). You will see in the EDM pool section of the report an indication of the number of pages in the pool that hold SKPT and SKCT sections, and the number of pages that hold thread-related copies of package table (PT) and cursor table (CT) sections (DBDs, or database descriptors, also go into the EDM pool, but starting with DB2 for z/OS V8 these are loaded into a separate part of the pool above the 2 GB "bar" in the DBM1 address space). Additionally, you will see the number of free pages in the pool. These are pages that weren't holding anything when the data on which the report (or online display) is based was generated. [This and the other values mentioned thus far are "snapshot" (i.e., point-in-time) values.] Generally speaking, you shouldn't worry too much if the number of free pages is a relatively small percentage of the pages in the EDM pool (e.g., 10-20%), because in most cases a majority of the "in-use" pages are stealable (note that if your monitor reports a percentage of non-stealable pages in use, this probably applies only to non-stealable pages that hold PT or CT sections, as opposed to non-stealable SKPT and SKCT sections).
A little further down in the report, you'll see the hit ratios that pertain to requests for package and cursor table sections. A "not found" situation occurs when a section of a package or plan needed for the execution of a static SQL statement embedded in a running program is not found in the part of the EDM pool that holds sections of SKPTs (for packages) or SKCTs (for programs bound directly into plans). When that happens, the required section is loaded into the SKPT or SKCT part of the EDM pool from the skeleton package table or skeleton cursor table in the DB2 directory. That section is also copied to the PT or CT part of the EDM pool for the requesting thread (that being the thread through which the program is being executed). Because most programs at most DB2 for z/OS sites are bound into packages as opposed to being bound directly into plans, it is likely that you will see many more PT requests than CT requests (and more pages "held by PT" versus "held by CT"). Unless your EDM pool is pretty small (and these days, I tend to see pools that have 100-200 MB or more of space for SKPTs, SKCTs, PTs and CTs), your PT hit ratio (PT requests minus PT not found occurrences, divided by PT requests) will probably be very high (98% or more is not unusual). That's good (throughput is slightly impeded when package or plan sections have to be read into the EDM pool from the directory on disk). If the volume of CT requests on your system is low, you might see a relatively low CT hit ratio (e.g., under 70%). Is that bad? Probably not. Obviously, the first time a static SQL-containing program is executed after a DB2 start-up, a PT or CT "not found" situation will result because the needed section or sections have not yet been placed in the SKPT or SKCT part of the EDM pool (they're loaded on-demand). If programs bound directly into plans are rarely executed, there will be little in the way of repeated requests for CT sections that have previously been loaded into the SKCT part of the EDM pool, and since those "re-requests" are what drive a hit ratio higher, the CT hit ratio might be low. If it is low, don't worry about it if the CT request activity level is small compared to the number of PT requests on your system.
Finally, a word on "FAILS DUE TO POOL FULL." This happens when a requested package or plan section cannot be loaded into the EDM pool because the pool was full (the result is a program abend). That means no free pages and no stealable in-use pages. This tends to be quite a rare occurrence. When it does happen, it can caused by packages bound with RELEASE(DEALLOCATE) being executed via threads that persist for a long time before terminating. CICS-DB2 protected threads can be of this variety, persisting for hours before terminating thanks to thread re-use. If not many programs are bound with RELEASE(DEALLOCATE), the impact on the number of non-stealable PT (package section-holding) pages shouldn't be too significant. If lots of packages are bound with RELEASE(DEALLOCATE) and they are executed by threads that stick around for a long time, the part of the EDM pool used for PT sections can grow substantially, and lot of this space could be non-stealable. You can use RELEASE(DEALLOCATE) when binding programs (it provides a CPU efficiency benefit, especially for "small" programs that are executed by way of persistent threads), but try not to overdo it: the best candidates are programs that are frequently executed and are associated with threads that persist through commits. You can find out more about RELEASE(DEALLOCATE) versus RELEASE(COMMIT) in a blog entry I posted a few weeks ago.
I hope now that SKPT and SKCT and PT and CT are not abstract letter-combinations for you. Knowing what they're all about, you can be that much better at monitoring the health of your DB2 for z/OS subsystem.
posted by Robert Catterall at
6:18 AM
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home