When DB2 for z/OS Can't Account for in-DB2 Time
DB2 for z/OS is very good (through data provided in accounting trace classes 2 and 3) at showing how time is spent when SQL statements are executed (this is often referred to as in-DB2 time). That said, it is often the case that one will see, in a DB2 monitor accounting report or an online monitor display of DB2 accounting data, a non-zero value in a field labeled "not account." This label is short for "not-accounted-for time," and it refers to time that is in-DB2 (i.e., not "in-application-but-outside-DB2" time) that was accumulated for reasons unknown to DB2. The calculation for this figure is pretty straightforward: take the in-DB2 elapsed time and subtract from that value the in-DB2 CPU time and the total DB2 class 3 suspension time (the latter value is the sum of the wait times of which DB2 is aware, such as the time spent waiting for synchronous I/O operations to complete).
Usually this value is quite small, and if it's less than 10% of in-DB2 elapsed time I don't pay much attention to it. If, on the other hand, the not-accounted-for time is well in excess of 10% of in-DB2 elapsed time, I'm going to want to check into that because it could be indicative of a system performance problem (I've seen not-accounted-for values as high as 50% of in-DB2 elapsed time).
So, what can cause overly large not-accounted-for time values? First, I need to point out that there is one case in which a relatively high not-accounted-for figure is not necessarily a red flag: that of a local application issuing one or more requests through the DB2 subsystem that will be executed on one or more remote DRDA servers (in this case DB2, through its Distributed Data Facility, acts as a DRDA requester). When that situation occurs, the not-accounted-for value will include the time the requesting process waited for responses from the remote data server. Depending on the nature of the request for remote data services, that wait time could be considerable.
OK, so DRDA requester situations aside, what could lead to a large not-accounted-for time value? In my experience, it tends to be a symptom of a DB2 dispatching problem. That is to say, either the mainframe server is absolutely maxed out, or it's merely very busy and DB2 has a too-low priority in the z/OS system. Either way, you have a situation in which DB2 itself is not being readily dispatched by the operating system when it needs some CPU time. Since DB2 can't get an engine (i.e., a CPU or microprocessor) when it needs one, and it can't know what's going on when it doesn't have an engine, it realizes when it finally does get dispatched that time has elapsed in the process of executing an SQL statement, but it can't account for WHY that time has elapsed.
What to do if in-DB2 not-accounted-for time is overly large? First, check DB2's priority in the system. If it's too low, up it. What's too low? Well, at IBM's 2006 Information on Demand event in Anaheim I attended an excellent session presented by Glenn Anderson, an IBM WLM expert, and he said that an important started task such as DB2 should be assigned to a started task service class with a velocity of around 50 or 60 and Importance 1 (referring to the DB2 DBM1 and MSTR address spaces - the IRLM address space should be assigned to the SYSSTC service class).
If your production DB2 address spaces have an appropriately high priority within the z/OS system, and you still see high in-DB2 not-accounted-for time values, you may have an overloaded mainframe server. In that case, you have a couple of options. The first is to add processing capacity to the server (more and/or faster engines). The second is to reduce CPU consumption, perhaps through DB2 subsystem and application tuning.
So, if you have a really busy server running DB2 for z/OS, keep an eye on that in-DB2 not-accounted-for time. If it's a single-digit value (as a percent of in-DB2 elapsed time), relax. If it's high, do some investigating.
Usually this value is quite small, and if it's less than 10% of in-DB2 elapsed time I don't pay much attention to it. If, on the other hand, the not-accounted-for time is well in excess of 10% of in-DB2 elapsed time, I'm going to want to check into that because it could be indicative of a system performance problem (I've seen not-accounted-for values as high as 50% of in-DB2 elapsed time).
So, what can cause overly large not-accounted-for time values? First, I need to point out that there is one case in which a relatively high not-accounted-for figure is not necessarily a red flag: that of a local application issuing one or more requests through the DB2 subsystem that will be executed on one or more remote DRDA servers (in this case DB2, through its Distributed Data Facility, acts as a DRDA requester). When that situation occurs, the not-accounted-for value will include the time the requesting process waited for responses from the remote data server. Depending on the nature of the request for remote data services, that wait time could be considerable.
OK, so DRDA requester situations aside, what could lead to a large not-accounted-for time value? In my experience, it tends to be a symptom of a DB2 dispatching problem. That is to say, either the mainframe server is absolutely maxed out, or it's merely very busy and DB2 has a too-low priority in the z/OS system. Either way, you have a situation in which DB2 itself is not being readily dispatched by the operating system when it needs some CPU time. Since DB2 can't get an engine (i.e., a CPU or microprocessor) when it needs one, and it can't know what's going on when it doesn't have an engine, it realizes when it finally does get dispatched that time has elapsed in the process of executing an SQL statement, but it can't account for WHY that time has elapsed.
What to do if in-DB2 not-accounted-for time is overly large? First, check DB2's priority in the system. If it's too low, up it. What's too low? Well, at IBM's 2006 Information on Demand event in Anaheim I attended an excellent session presented by Glenn Anderson, an IBM WLM expert, and he said that an important started task such as DB2 should be assigned to a started task service class with a velocity of around 50 or 60 and Importance 1 (referring to the DB2 DBM1 and MSTR address spaces - the IRLM address space should be assigned to the SYSSTC service class).
If your production DB2 address spaces have an appropriately high priority within the z/OS system, and you still see high in-DB2 not-accounted-for time values, you may have an overloaded mainframe server. In that case, you have a couple of options. The first is to add processing capacity to the server (more and/or faster engines). The second is to reduce CPU consumption, perhaps through DB2 subsystem and application tuning.
So, if you have a really busy server running DB2 for z/OS, keep an eye on that in-DB2 not-accounted-for time. If it's a single-digit value (as a percent of in-DB2 elapsed time), relax. If it's high, do some investigating.
posted by Robert Catterall at
7:13 PM
4 Comments:
nice posting sir..
now i wanna ask u, how to display the elapse time in DB2 when we execute sql statement??
for example: if i want to execute a query for deleting operation, how to display the elapse time??
This is my problem when i do my final project. The topic is study performance of Multidimensional Clustering in DB2.. I must make some tests to know the performance based on response time and storage allocation.
Thank u very much...
Sorry about the delay in responding.
The simplest way to get the elapsed time for a query in a DB2 for Linux/UNIX/Windows environment (and I assume that you're talking about DB2 for LUW, as you referred to multidimensional clustering) is probably the db2batch tool that comes with DB2. You can read about db2batch in the DB2 for LUW Information Center (the DB2 9.5 Information Center is available at http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp) - just enter db2batch in the search window.
An alternative approach would be to use a statement event monitor or the WLM activity event monitor (the latter was introduced with DB2 9.5 for LUW). These have a fairly significant overhead, but for a specific test such as the one you've described this might not be a problem (note that in DB2 9.7 for LUW, just recently available, the overhead of the activity event monitors has been significantly reduced). Again, the DB2 for LUW Information Center is probably your best source of information - search on event monitor.
Isn't "excessive" not accounted time relative to the overall elapsed class 2 time? I've been running accounting long reports to help development analyze their applications. Based on the report, 10% of total elapsed time is In-DB2. Of that, the average class 2 time is 0.099556 with 53% not accounted. How much time do you spend on running down 0.05276468 seconds?
Thanks.
Hello, Tony.
How much time would I spend on this? That would depend on whether or not the situation you've described is isolated. That 53% in-DB2-but-not-accounted-for time figure -- is that for a single program, or for a group of programs? If you see that high figure for just the one program (perhaps the in-DB2-but-not-accounted-for time for other programs is closer to 10%), you might want to let it be and look for bigger fish to fry, since (as you've mentioned) it's just one-twentieth of the program's total elapsed time.
If, on the other hand, a number of other programs also have high in-DB2-but-not-accounted-for times, that could indicate the existence of a fairly severe CPU resource constraint in your environment. Such a constraint could be having a substantial negative impact on application response time and throughput, and you'd want to act decisively to reduce the pressure on the server's processing capacity, either through application and database performance tuning or with a server upgrade (or both).
In my experience, high in-DB2-but-not-accounted-for times are symptomatic of a wait-for-dispatch problem. If the problem in your system (assuming it's not just happening for the one program) appears to DB2-specific (i.e., if it looks as though work associated with other subsystems is being dispatched in a timely manner), it may be that the DB2 address spaces are not prioritized as they should be. In terms of the z/OS workload manager (WLM), IRLM needs to be in the SYSSTC service class, and the DB2 DBM1, MSTR, and DIST address spaces should be in a service class with a high velocity goal (a velocity of 50 or 60 might be good for these address spaces).
Robert
Post a Comment
Subscribe to Post Comments [Atom]
<< Home