DB2 V8 for z/OS Statistics and Diagnostics

6 Pages

As an IT professional, it’s your job to provide your clients with the information they need to successfully run their business. But you, too, need information to quickly and effectively do your job. Sometimes, that can be difficult when you don’t have enough information about the problems you’re trying to solve. Also, just as you need as much information as possible to make accurate decisions, so does DB2.  

  1. Version 8 of DB2 for z/OS provides more ways to diagnose and solve problems and enables you to provide DB2 with more information about your data, so access path determination can be more accurate. Without this constant two-way communication of information, your ability to deliver the best service to your customers becomes difficult.   

This article examines some of the new information DB2 provides and new ways to provide information to DB2. Let’s start with some of the new ways of obtaining information from DB2 to help you with performance and other problems.

New Instrumentation Information

New and updated information is now being provided by the performance, accounting, and statistics traces. This will help you detect problems sooner and have more information about the application experiencing the problem.

Performance Trace

  1. There are several new enhancements to the way you’re able to gather performance trace information.   

There’s a new IFCID 350 (Instrumentation Facility Counter ID [similar to IFCID 63]) for tracing an entire SQL statement. This is now done in a repeating (up to six times) 5,000-byte tracing record (previously, you were limited to one 5,000-byte record). If six repeating groups aren’t enough, there will be multiple IFCID 350 records written.

Changes to PREPARE attributes (i.e., cursor isolation level) can now also be traced. IFCID 63 will contain the attribute string of the short prepare; IFCID 124 will also contain the attribute string for READS for currently executing SQL; and IFCID 317 will contain the attribute string for statements in the dynamic SQL cache.

IFCID 124 will now contain the dynamic statement cache ID (four bytes) for statements that are currently in the dynamic SQL cache. This ID can be used with the new EXPLAIN STMT-CACHE feature to allow explains to be performed on statements in the cache.

To monitor use of the TEMP database and workfiles at the application level, IFCID 342 has been added. It will be written when space is allocated or deallocated for a TEMP database or workfile. Usage information will include the total amount of space currently used and maximum amount used by the application. It will also include this for indexes created on declared temporary tables. This information can help us see what resources are being used for temporary space and help better monitor usage so one application doesn’t monopolize this space.


6 Pages