How to Gather / Collect Db2 SMF Data

One could spend a career getting familiar with all of the thousands of fields in the Db2 statistics records and learning how to use them to manage your Db2 subsystems. In this and in subsequent blogs, I will cover both basics and in-depth analysis of getting familiar with, understanding, and maximizing the potential of Db2 SMF data to improve performance and availability.

In this blog, I will specifically cover the crucial initial step for analyzing the data – how to gather and collect the SMF data in the first place. If you are a Db2 person, I recommend jumping to my next blog where I begin to analyze the data.

If you are not a Db2 person and find yourself in a position where you have to keep an eye on a Db2 system and feel a bit lost, I hope this will be of some help to you. Maybe it will help you tune your system a bit, and get more comfortable playing with this most wonderful, beautiful, DBMS.

Db2 Statistics Documentation

To help us with analyzing and gathering the previously mentioned thousands of Db2 statistics records, IBM helps us by documenting them in the prefix.SDSNIVPD(DSNWMSGS) member of the Db2 installation libraries.

The DSNWMSGS member provides not only field names and descriptions, but also information about the trace types and class numbers that gather those IFCIDs (Instrumentation Facility Component IDs) – the first few thousand lines of that member contain valuable information to help you understand the remainder of the member, and point to related members where you can get more detailed information. It is a little convoluted, because many IFCIDs appear in many different trace types and classes, but at least it’s all there for your consumption.

To make it a little easier to consume, IBM provides DDL to create tables into which you can load this information for fast queries. If you are more comfortable using spreadsheets, you can import the tables into a spreadsheet in Microsoft Excel, where the data can be filtered and searched easily without having to construct a different SQL query every time.

How to Gather Db2 SMF Data – Start a Trace

To gather Db2 SMF data, a Db2 “trace” must be started via the -START TRACE command.

In Db2, a trace identifies what type of data is going to be collected: statistics (SMF 100), accounting (SMF 101), performance (SMF 102), and more. The SMF type 100 record contains various types of Db2 trace information. Each trace record is identified by one or more IFCIDs. There are trace classes that define groupings of IFCIDs to make the collection of related sets more manageable.

Statistics traces are usually automatically started in groups of classes when Db2 comes up, and those trace classes are specified in the Db2 system parameters (‘zparms’).

Generally, the default classes provide sufficient information for most shops, but other classes may be started if needed. The topics covered in this article are all based on data gathered by the default classes. The cost of collecting statistics records is very low, and should not be of concern, considering the vast amount of data about your system that can be used in tuning Db2.

Db2 SMF Record Types

The intervals on which statistics records are cut are managed by two zparms: STATIME_MAIN, which specifies the interval for IFCIDs 1, 2, 202, 225, 230, 254, and 369;

and STATIME, which specifies the interval for IFCIDs 105, 106, 199, 365, and 402. Other IFCIDs are always cut at 1-minute intervals. For details, see Table 1. Note that if you start two trace classes that contain the same IFCID, Db2 is smart enough to know to write that information just once, avoiding a potential duplication.

Table 1 – Db2 IFCID to SMF Record Type Mapping

As you can see, collecting Db2 SMF data is not straightforward. When the -START TRACE (STAT) command is issued, one would think that it is going to be creating SMF type 100 records. But as Table 1 shows, many of the statistics are actually written to type 102 records.

In my next article, “Understanding and Analyzing Db2 Statistics CPU Times and DBAT Activity,” I will begin to delve into a few areas of SMF 100 Db2 statistics that can be of interest – namely CPU Usage and DBAT Activity. In a much longer article I wrote for Watson and Walker’s Tuning Letter, I cover everything above as well as dive into much greater detail for all of the below Db2 statistics areas:

• CPU Times
• Work File Usage
• RID Pool Usage
• Data Sharing Locking
• Dynamic Statement Cache
• DBAT Activity

You can access and read the full reprinted Watson and Walker Tuning Letter, Db2 Statistics: They’re Not Just for Buffer Pools.