Sally Mir - 24 February 2022

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.

IFCID SMF Type Metric Type
1 100 System Statistics
2 100 Db2 Statistics
105 102 Db2 Performance Trace
106 102 System Parameters
199 102 Data Set Statistics
202 100 Buffer Pool Attributes
225 100 Storage Statistics
230 100 Group Buffer Pool Attributes
254 102 Coupling Facility Cache Statistics
365 102 Remote Location Statistics
369 100 Wait and CPU Time Aggregated by Connection Type
402 102 Monitor Profile Warnings or Exceptions

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.

This article's author

Sally Mir
Senior Consultant - Db2
More from Sally

Share this blog

Understanding and Analyzing Db2 Statistics CPU Times and DBAT Activity

Discover Modern Db2 Performance Monitoring

Learn how to add predictive and prescriptive value to your Db2 and CICS monitoring process with new analytics capabilities.

You May Also Be Interested In:

Blog

Evaluating and Tuning Db2 Subsystem Wait (Suspension) Times

Understanding what causes wait (suspension) time can be instrumental in improving your subsystem and application performance.

Read more
Webinar

Closing the Gap on Mainframe Application Profiling | IntelliMagic zAcademy

Break down the barriers between z/OS and distributed systems and communicate specific methods both sides can use to classify workloads properly.

Watch Webinar
Blog

10 Essential Reporting Features Every z/OS Performance Analyst Should Have

There are a lot of key tools and reporting features mainframe performance analysts need for infrastructure management. These are the top 10.

Read more

Explore Db2 Performance Management and Monitoring