|
The DB2 trace facility and OMEGAMON II for DB2 historical collection considerations
from CCR2, Issue 7 - 2004
DB2 Universal Database (UDB) for OS/390 and z/OS provides a robust trace facility that may be used to record information on a wide variety of activities in the DB2 database subsystem. DB2 offers four primary types of traces: statistics, accounting, audit, and performance.
OMEGAMON II® for DB2 historical data collection may reference any or all of these trace types. There are several configuration options as part of the setup of the OMEGAMON® historical data collector. Several of these options control what types of -- and how much -- trace data is gathered and stored by the OMEGAMON collector. In a prior article (CCR2TM, June 2004), history collection considerations for lock analysis were discussed. This paper will consider the pros and cons of the other historical collection options.
About DB2 traces
Each trace record within DB2 has an identifier called an Instrumentation Facility ID (IFCID). Accounting traces track application-level events within the DB2 subsystem. Accounting trace records are written to IFCID 3 and to SMF record ID 101. There are five main classes of accounting traces:
Class 1 (Elapsed time)
Class 2 (In-DB2 time)
Class 3 (Wait times)
Class 7 (Package level In-DB2)
Class 8 (Package level Wait)
In OMEGAMON, DB2 historical collection accounting records are written at application termination. The typical overhead of gathering the accounting traces is about 3-5 percent, depending upon the nature of the applications monitored. Some shops that create an extremely large amount of accounting data may not be able to run all the accounting trace classes (for example, they run accounting classes 1, 2, and 3, but not 7 and 8), but this is relatively rare.
The statistics trace reports on information about DB2 system services and database services. The information from the statistics trace provides system-wide data to analyze DB2 performance. The statistics trace creates two records -- IFCID 1 and 2 -- written on an interval basis (typically 15 minutes) to SMF record ID 100. When looking at statistics traces, some counters are cumulative from DB2 startup, and some are not. For more information about the counters, refer to DB2 library DSNSAMP member DSNWMSGS. This member documents in detail the various IFCIDs and how the counters are calculated. The overhead of statistics traces is very low, and all shops typically run statistics traces.
Performance traces provide detailed snapshots of internal DB2 activity. Many DB2 events may be traced. Performance traces consist of multiple trace classes and multiple event types (IFCIDs). There are literally hundreds of different performance trace IFCIDs to choose from. Each trace class consists of multiple event types (IFCIDs). Performance trace classes will impart varying amounts of overhead upon the DB2 subsystem. Certain trace classes are typically lower in overhead. These include Class 3 SQL events, and Class 1 and 2 subsystem types of events. Medium overhead traces include Class 6 lock summary-type traces. Higher overhead traces include buffer manager traces and log manager traces; traces higher still include Class 7 lock detail traces.
Different DB2 traces may be used to perform various levels of analysis. A sound strategy is to use a top-down approach and start with the accounting traces to isolate problem applications. Once the problem has been identified, the next step is to use the performance traces to analyze the application in further depth. Care should be used to run just the trace IFCIDs needed, so as not to incur unnecessary overhead by tracing irrelevant events.
Audit traces are used to monitor and track specific activities on the DB2 subsystem. With audit trace, the more tables that are audited and the more transactions that access them the larger the overhead. The overhead of audit trace is typically less than 5 percent.
OMEGAMON historical options
OMEGAMON for DB2 Near Term Historical data collection has several options that affect what traces and IFCIDs are being gathered. These choices implicitly impact the overhead of OMEGAMON DB2 historical data collection.
In general, it is always recommended to gather statistics history data. The overhead is low, the quantity of data gathered is small, and the value of the data is very high. You should gather accounting trace data Classes 1, 2, 3, 7, and 8. The quantity of data is large in most shops, but the value of the data in terms of being able to analyze application activity is great.
Accounting traces are the starting point for any DB2 application tuning analysis. Audit traces should be started, if required by the security and auditing needs of the installation. If in doubt, go to the OMEGAMON trace display (Option R.F) to see what is running on the subsystem.
Other history collection options may require that OMEGAMON start additional performance trace IFCIDs (typically as a Class 30 trace). To see the options in question, go to the historical collection options panel (Option H.C). Figure 1 shows an example of a typical panel. The options in question are Sort, Lock Contention, Scan, Lock Suspension, Dynamic SQL, and Negative SQL. Lock Contention and Lock Suspension were discussed in the previous article.
Figure 1 - Historical collector options 
The configuration options are YES to gather and NO to not gather. Each of these, if set to YES, will enable the gathering of some additional DB2 performance trace IFCIDs. The overhead, to a large extent, is a function of how many events are being traced by the given option. To see the number of events traced by the collector, select option B from the historical collector options panel. Figure 2 shows an example of the screen that shows the type of records gathered and the number of records gathered for the trace type.
Figure 2 - Historical data captured
Considering the options
One of the most important options to consider is whether to gather Dynamic SQL history. Setting Dynamic SQL to YES will enable the gathering of IFCID 22 and allow OMEGAMON for DB2 to store dynamic SQL text for later analysis. For shops that do a large amount of dynamic SQL, this can be an important tool for isolating problem SQL. The tradeoff is that in shops with applications executing a large amount of dynamic SQL, enabling this option will result in the gathering of a huge number of IFCID 22 records. In some shops, as many IFCID 22 records may be created as accounting records. This is a classic example of where the plusses and minuses of cost of monitoring vs. the value of the data gathered need to be considered.
Setting the Sort option to YES enables IFCID 95 and 96 to see sort information at the SQL level. This includes information on sort keys, number of runs, and duration of sorts. It is generally a low-overhead trace that provides useful information for analyzing DB2 SQL that may result in sort processing.
Setting Scan to YES will enable IFCIDs 15, 16 ,17, and 18 and provide scan counts and scan data by object type. This data is often of marginal value because the history data does not store the object name, just the object type. So again, the tradeoff must be taken into account.
The Negative SQL option, if set to YES, will enable additional SQL level tracing. IFCIDs 53, 58, 59, 60, 61, 64, 65, and 66 will be started to capture SQL-level information. As a standard, some installations like to maintain a history of all SQL that results in negative SQL codes. This is another good example of the tradeoff of the value of the data vs. the cost of monitoring. The general recommendation is to set this option to NO unless this data is very important to your shop.
Conclusion
When running OMEGAMON, always ask questions about the cost of monitoring vs. the value and importance of the data gathered. Certain historical data is very important to gather. Examples include DB2 statistics and accounting. Most shops will typically gather these traces for all subsystems. The other collector options discussed (Sort, Lock Contention, Scan, Lock Suspension, Dynamic SQL, and Negative SQL) require more thought and analysis.
Do not assume that the defaults are optimal. For example, all the audit traces default to ON, and the Sort and Scan options default to YES. These may not necessarily be the optimal and desired settings. You will want to enable what is important at your shop, and set to NO those options not considered essential.
For more information
Join next month's webinar for more on DB2 and how to get the most out of DB2 for z/OS V8.
Submit your questions
Do you have a question for the author? Or would you like your question addressed during next month's webinar? Request information at left to submit your questions.
|
