Let’s speak today about database monitoring. DB2 is usually presented as a black box that stores and retrieves data for you. Usually we don’t care much about what is happening inside it. However as soon as a trouble arises, information about its internals becomes critical.
How many connections are currently open with DB2? What process consumes or locks database resources? Also important are wider questions such as how much CPU and memory the DB2 subsystem uses overall, or maybe on Mondays, weekends, etc.? This is where DB2 performance monitoring will stand out.
DB2 uses a traditional approach for monitoring. It uses a tracing facility. DB2 produces a trace record whenever a system event takes place (asynchronous trace record) or when DB2 is requested to provide specific information about its state (synchronous trace record). Since there are many different types of DB2 events and system characteristic, there many are different trace records reporting on those events (about 350 records exist at the moment). Keep in mind that DB2 simply produces trace records, while storing and analysis of trace records should happen outside of DB2.
Each trace record has its own identifier, an IFCID – Instrumentation Facility Component Identifier. All IFCIDs are grouped into various trace types and classes:
- “STATS” – Statistics type trace (classes 1 – 9) gives overall information about how much DB2 resources are being used. Each class is intended to capture different set of system events.
- “ACCTG” – Accounting type trace (classes 1 – 5, 7, 8, 10) provides details about DB2 transactions (or threads). Each class determines amount of details, captured for a thread.
- “PERFM” – Performance type trace (classes 1 – 24) is used for deep performance analysis and tuning. Each class is indented to track a specific DB2 activity (for example, SQL, sorting, locking, etc.).
- “AUDIT” – Audit type trace (classes 1 – 11) monitors DB2 security.
- “MONITOR” – Monitor type trace (classes 1 – 10, 29) is similar to accounting type trace but is intended to be used by monitoring tools.
Detailed description of trace types and classes can be found in the IBM DB2 Performance Management guide, while details on each IFCID are provided with DB2 and can be found in the member db2_lib.SDSNIVPD(DSNWMSGS).
Any trace activity is only initiated with the corresponding command (a complete syntax can be found in DB2 Command Reference):
-START TRACE(STATS/ACCTG/PERFM/AUDIT/MONITOR) DEST(SMF/GTF/OPx) CLASS(n)
The command specifies what type of events DB2 would be monitoring and where the captured data will be written. The statistics, accounting and audit data are commonly written into an SMF (System Management Facility) file, which is an operating system-wide file used to capture trace records, using SMF trace record subtypes 100 –through 102, while performance data is often routed to GTF (Generalized Trace Facility) due to the volume of trace data produced. Monitor data is written to a DB2 internal performance buffer buffer (OPx ) where they are later consumed by a monitoring tool.
Since the trace data are captured in a machine readable format, it is desirable to use some performance monitoring product that would be able to format the data and build specific analysis reports. There are a number of vendors on the market that provide such products. Each vendor provides various reports based on trace types such as accounting reports (detailed reports for each captured record or summarized report by plan/package/etc.), statistic reports (detailed for each record or summarized by address space, etc.), performance reports (locking activity, I/O activity, SQL trace, etc.), audit reports. And of course in addition to batch reports, vendor products provide a user interface to monitor DB2 with trace data being captured in real time.
It is important to understand that traces bring an overhead to your DB2 subsystem, so they should be used wisely. It is recommended to capture basic statistics and accounting data continually so that as a problem arises, you have a means to get back to the history and analyze how the problem was developing. However sometimes this is not enough and performance traces will need to be started to allow deeper investigation.
This article only scratches the surface of performance monitoring but hopefully it gives you an idea where to start your learning path.
Denis Tronin, CA Technologies