DB2 for z/OS Accounting: And a One, And a Two...
When people analyze DB2 for z/OS monitor accounting reports or online DB2 monitor displays of thread detail data, they often pay particular attention (with good reason) to the elapsed and CPU time figures contained therein. Some of these same folks are a little confused as to the meaning of the numbers they are examining. They see columns of figures under headings that read (depending on the monitor product) "Class 1" and "Class 2" or "In-Application" and "In-DB2," and wonder what to make of them. In this post I'll provide an explanation of DB2 monitor elapsed and CPU time fields. I hope that you'll find this information to be useful.
First, bear in mind the source of the numbers provided in a DB2 monitor accounting report or an online display of thread detail data: records generated via DB2 for z/OS accounting trace classes 1 and 2 - thus the frequent use in monitor products of "Class 1" and "Class2" as headings for the columns of in-application and in-DB2 (respectively) elapsed and CPU times. [Note that many mainframe DB2-using organizations have DB2 accounting trace classes 1 and 2 (and, typically, 3, as well) running at all times. The associated CPU overhead is generally less than 5%.]
Second, think of the Class 1 and Class 2 times in a DB2 thread context. Generally speaking, an application process gets a thread when it issues its first SQL statement (for an off-mainframe requester coming through the DB2 Distributed Data Facility - aka DDF - that would be a CONNECT statement). When the application process gets the thread (whether it's a local thread for a program in an allied address space or a database access thread - aka DBAT - for a remote DRDA requester), the class 1 and class 2 timers start running, conceptually speaking (the class 2 timer actually gets going a tiny fraction of a second after the class 1 timer, but this really is a very small differential). The class 1 elapsed time is basically from get-thread (could be a new thread, could be a reused thread, depending on the circumstances) until end-of-transaction (or end-of-batch-job). [I have to point out here, for the sake of completeness, that some persistent threads (such as CICS-DB2 protected entry threads) can stick around after the transaction using the thread completes. The effect of persistent threads on class 1 elapsed time is a little complex, but don't get hung up on that. DBATs can be persistent threads, but if you're using inactive DDF threads (this is a ZPARM thing, referring to the set of DB2 installation parameter value specifications) then the class 1 timer pretty much stops at end-of-transaction.]
OK, so class 1 elapsed is from get-thread to end-of-transaction. Class 2 elapsed is a subset of class 1 elapsed, and is basically that portion of class 1 elapsed time spent in the execution of SQL statements (referred to as "in-DB2" because when your program issues an SQL statement the associated z/OS task switches addressability and executes code in the DB2 Database Services Address Space). If your program calls a stored procedure, part of the class 1 and class 2 times will be associated with the execution of that stored procedure program because the stored procedure - though executing under its own z/OS task - uses the thread of the calling program when issuing SQL statements. The class 1 elapsed time attributed to stored procedure execution is, in essence, the elapsed time of the stored procedure program. The class 2 elapsed time attributed to the stored procedure is that portion of the stored procedure's class 1 time spent in the execution of SQL statements issued by the stored procedure. Similarly, class 1 CPU time is total CPU time for the application process from get-thread to end-of-transaction, and class 2 CPU time is the CPU time consumed in the execution of SQL statements. The assignment of class 1 and class 2 CPU time to a stored procedure called by the application process is like the just-described assignment of class 1 and class 2 elapsed time to a stored procedure.
As time - elapsed or CPU, in-application or in-DB2 - is broken out for any stored procedures called by your program, so is it broken out for any database triggers fired by your program (a trigger might update a column in a row of table X if your program inserts a row into table Y) and for any user-defined functions (UDFs) invoked by your program (DB2 for z/OS has a rich array of built-in functions such as CHARACTER_LENGTH, DAYOFWEEK, FLOOR, and ROUND, but you might need to define a function of your own to meet a special need). The portions of a program's elapsed and CPU times that are NOT associated with called stored procedures or fired triggers or invoked UDFs are referred to as "nonnested."
Class 3 time, by the way, is essentially in-DB2 time that is not CPU time. In other words, the program is in-DB2 because it has issued an SQL statement, but it is not using CPU time because it's waiting on something (maybe waiting to get a lock on a page or row, or waiting for a page to be read into a DB2 buffer pool from the disk subsystem). DB2 (thanks to accounting trace class 3) has about 20 "buckets" into which it can place in-DB2 time that is not CPU time. Sometimes, there is in-DB2 time that DB2 cannot explain (i.e., it's not CPU time and it doesn't fall into any of the class 3 wait-time "buckets"). I wrote a post on this topic (what's called "not-accounted-for time") in January of this year.
Now, get out there and speak confidently of class 1 and class 2 (and even class 3) times. Seriously, the better your understanding of the information contained in a DB2 monitor accounting report or online display of thread detail data, the more useful that information will be to you.
First, bear in mind the source of the numbers provided in a DB2 monitor accounting report or an online display of thread detail data: records generated via DB2 for z/OS accounting trace classes 1 and 2 - thus the frequent use in monitor products of "Class 1" and "Class2" as headings for the columns of in-application and in-DB2 (respectively) elapsed and CPU times. [Note that many mainframe DB2-using organizations have DB2 accounting trace classes 1 and 2 (and, typically, 3, as well) running at all times. The associated CPU overhead is generally less than 5%.]
Second, think of the Class 1 and Class 2 times in a DB2 thread context. Generally speaking, an application process gets a thread when it issues its first SQL statement (for an off-mainframe requester coming through the DB2 Distributed Data Facility - aka DDF - that would be a CONNECT statement). When the application process gets the thread (whether it's a local thread for a program in an allied address space or a database access thread - aka DBAT - for a remote DRDA requester), the class 1 and class 2 timers start running, conceptually speaking (the class 2 timer actually gets going a tiny fraction of a second after the class 1 timer, but this really is a very small differential). The class 1 elapsed time is basically from get-thread (could be a new thread, could be a reused thread, depending on the circumstances) until end-of-transaction (or end-of-batch-job). [I have to point out here, for the sake of completeness, that some persistent threads (such as CICS-DB2 protected entry threads) can stick around after the transaction using the thread completes. The effect of persistent threads on class 1 elapsed time is a little complex, but don't get hung up on that. DBATs can be persistent threads, but if you're using inactive DDF threads (this is a ZPARM thing, referring to the set of DB2 installation parameter value specifications) then the class 1 timer pretty much stops at end-of-transaction.]
OK, so class 1 elapsed is from get-thread to end-of-transaction. Class 2 elapsed is a subset of class 1 elapsed, and is basically that portion of class 1 elapsed time spent in the execution of SQL statements (referred to as "in-DB2" because when your program issues an SQL statement the associated z/OS task switches addressability and executes code in the DB2 Database Services Address Space). If your program calls a stored procedure, part of the class 1 and class 2 times will be associated with the execution of that stored procedure program because the stored procedure - though executing under its own z/OS task - uses the thread of the calling program when issuing SQL statements. The class 1 elapsed time attributed to stored procedure execution is, in essence, the elapsed time of the stored procedure program. The class 2 elapsed time attributed to the stored procedure is that portion of the stored procedure's class 1 time spent in the execution of SQL statements issued by the stored procedure. Similarly, class 1 CPU time is total CPU time for the application process from get-thread to end-of-transaction, and class 2 CPU time is the CPU time consumed in the execution of SQL statements. The assignment of class 1 and class 2 CPU time to a stored procedure called by the application process is like the just-described assignment of class 1 and class 2 elapsed time to a stored procedure.
As time - elapsed or CPU, in-application or in-DB2 - is broken out for any stored procedures called by your program, so is it broken out for any database triggers fired by your program (a trigger might update a column in a row of table X if your program inserts a row into table Y) and for any user-defined functions (UDFs) invoked by your program (DB2 for z/OS has a rich array of built-in functions such as CHARACTER_LENGTH, DAYOFWEEK, FLOOR, and ROUND, but you might need to define a function of your own to meet a special need). The portions of a program's elapsed and CPU times that are NOT associated with called stored procedures or fired triggers or invoked UDFs are referred to as "nonnested."
Class 3 time, by the way, is essentially in-DB2 time that is not CPU time. In other words, the program is in-DB2 because it has issued an SQL statement, but it is not using CPU time because it's waiting on something (maybe waiting to get a lock on a page or row, or waiting for a page to be read into a DB2 buffer pool from the disk subsystem). DB2 (thanks to accounting trace class 3) has about 20 "buckets" into which it can place in-DB2 time that is not CPU time. Sometimes, there is in-DB2 time that DB2 cannot explain (i.e., it's not CPU time and it doesn't fall into any of the class 3 wait-time "buckets"). I wrote a post on this topic (what's called "not-accounted-for time") in January of this year.
Now, get out there and speak confidently of class 1 and class 2 (and even class 3) times. Seriously, the better your understanding of the information contained in a DB2 monitor accounting report or online display of thread detail data, the more useful that information will be to you.
posted by Robert Catterall at
8:32 AM
3 Comments:
Robert good job on this. Is there a place where I can see all the columns for class 1 and 2 times? I want more than just this but for now maybe this can help me in answering my other questions. I mean a person could write alot about a trace class and all the associated ifcids... thanks Rocky
Robert I may have answered my own question, the descriptions are located at yourdb2id.SDSNIVPD(DSNWMSGS). If there is anything else to add please add, thanks.
The DSNWMSGS member of SDSNIVPD is indeed where you find the descriptions of the fields in the various DB2 for z/OS trace records. I also refer frequently to the IBM Tivoli OMEGAMON XE for DB2 Performance Monitor on z/OS Report Reference (available on the Web at http://publib.boulder.ibm.com/infocenter/tivihelp/v15r1/topic/com.ibm.omegamon.xe.pm_db2.doc/ko2rre12.htm. I particularly like the section titled "Accounting Report and Trace Blocks." Even if you don't use this particular DB2 for z/OS monitoring tool, the descriptions of the fields in the accounting report give you a good idea as to what is in the accounting trace records (which are formatted by the OMEGAMON monitor).
Robert
Post a Comment
Subscribe to Post Comments [Atom]
<< Home