Showing posts with label IFCID. Show all posts
Showing posts with label IFCID. Show all posts

Tuesday, August 08, 2023

Top 10 Db2 Performance Tips - No. 6 Monitoring and Performance Metrics

Monitoring and measuring performance metrics are essential practices for maintaining and optimizing the performance of an IBM Db2 environment. By regularly monitoring and analyzing performance data, DBAs can identify bottlenecks, proactively address performance issues, and make informed decisions to improve overall system efficiency.

It is important to monitor and measure performance metrics in order to gain insights into the behavior of the applications and databases in use at your site. By examining their behavior and identifying areas for improvement, you can improve the overall satisfaction of your customers and end users. 

Performance metrics provide valuable information about resource utilization, query response times, disk I/O, CPU usage, and other critical aspects of database performance. By tracking these metrics over time, administrators can detect patterns, identify trends, and pinpoint potential performance bottlenecks.

A Strategy

The first part of any Db2 performance management strategy should be to provide a comprehensive approach to the monitoring of the Db2 subsystems operating at your site. This approach involves monitoring not only the threads accessing Db2 and the SQL they issue, but also the Db2 address spaces. You can accomplish this task in three ways:

  • Batch reports run against DB2 trace records. While DB2 is running, you can activate traces that accumulate information, which can be used to monitor both the performance of the DB2 subsystem and the applications being run.
  • Online access to DB2 trace information and DB2 control blocks. This type of monitoring also can provide information on DB2 and its subordinate applications.
  • Sampling DB2 application programs as they run and analyzing which portions of the code use the most resources.
Using all three approaches can be a reasonable approach if you have analyzed the type of workloads in use and which types of monitoring are most effective for each. 
You also need to establish a strategy for your organization's tuning goals. And your tuning goals should be set using the discipline of service level management (SLM). A service level is a measure of operational behavior. SLM ensures applications behave accordingly by applying resources to those applications based on their importance to the organization. Depending on the needs of the organization, SLM can focus on availability, performance, or both. In terms of availability, the service level can be defined as “99.95% up time, during the hours of 9:00 AM to 10:00 PM on weekdays.” Of course, a service level can be more specific, stating “average response time for transactions will be two seconds or less for workloads of strategic external clients.”

For a service level agreement (SLA) to be successful, all of the parties involved must agree upon stated objectives for availability and performance. The end users must be satisfied with the performance of their applications, and the DBAs and technicians must be content with their ability to manage the system to the objectives. Compromise is essential to reach a useful SLA.

Furthermore, the objectives of the SLA must be defined and measurable. For example, in the earlier SLA you must define what a “strategic client” is and differentiate strategic from nonstrategic. Although doing this may be difficult, failing to do so can result in worthless SLAs that cannot be achieved.

In the end, if you do not identify service levels for each transaction, then you will always be managing to an unidentified requirement. Without a predefined and agreed upon SLA, how will the DBA and the end users know whether an application is performing adequately? Without SLAs, business users and DBAs might have different expectations, resulting in unsatisfied business executives and frustrated DBAs. Not a good situation.

Db2 Traces

One of the first types of performance metrics to consider is monitoring based on reading trace information. You can think of a Db2 trace as a window into the performance characteristics of aspects of your Db2 workload. Db2 traces record diagnostic information describing particular events. As Db2 operates, it writes trace information that can be read and analyzed to obtain performance information.

Db2 provides six types of traces, and each describes information about the Db2 environment:

  • Accounting - Records performance information about the execution of DB2 application programs
  • Audit - Provides information about DB2 DDL, security, ­utilities, and data modification
  • Global - Provides information for the servicing of DB2
  • Monitor - Records data useful for online monitoring of the DB2 subsystem and DB2 application programs
  • Performance - Collects detailed data about DB2 events, enabling database and performance analysts to pinpoint the causes of performance problems
  • Statistics - Records information regarding the DB2 subsystem’s use of resources

You can start Db2 traces in two ways: by specifying the appropriate DSNZPARMs at Db2 startup or by using the -START TRACE command to initiate specific traces when Db2 is already running.

Each trace is broken down further into classes, each of which provides information about aspects of that trace. Additional informatiohn about the classes for each type of trace can be found here, here, and here.

You should understand what type of information is traced and the approximate overhead of each trace class before starting any of the Db2 traces. Some traces are better left off until or unless a performance problem is occurring, at which point, the trace can be started to capture details about the situation. Others are better to have turned on before problems occur. Keep in mind that you will have some trace classes (and IFCIDs) that are always started, and other that are only used in emergencies.

What are IFCIDs?

Each trace class is associated with specific trace events known as Instrumentation Facility Component Identifier (IFCIDs), pronounced “if-kid.” An IFCID defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked
by Db2.

In some cases, it can make sense to avoid activating trace classes altogether and start traces specifying only the IFCIDs needed. This way, you can reduce the overhead associated with tracing by recording only the trace events needed. 

There are several hundred different IFCIDs. Most IFCIDs contain data fields that describe events pertinent to the event being traced. Some IFCIDs have no data; instead they merely mark a specific time. Certain trace events of extended durations require a pair of IFCIDs: one for the beginning of the event and another for the end. These pairs enable the computation of elapsed times. Other trace events that are not as lengthy require only a single IFCID. 

You can find the IFCIDs associated with each trace class in the IBM Db2Command Reference manual in the section describing the -START TRACE command. But that manual does not describe the purpose of each IFCID. A list describing each IFCID can be found in the data set named  SDSNIVPD(DSNWMSGS), which is part of the Db2 installation

Db2 Performance Monitors

Several popular performance monitoring solutions are available for Db2 for z/OS. IBM’s Omegamon, BMC Software’s MainView, Broadcom’s Sysview, and Rocket Software's TMON are the leading performance monitors. When selecting a performance monitor, be sure to examine the online components as well as the batch reporting capabilities of the monitor. 

An online performance monitor is a tool that provides real-time reporting on Db2 performance statistics as Db2 operates. In contrast, a batch performance monitor reads previously generated trace records from an input data set. Most performance monitors today can be used to serve both purposes.

With online DB2 performance monitors, you can perform proactive performance management tasks. In other words, you can set up the monitor such that when it detects a problem it alerts a DBA and possibly takes actions on its own to resolve the problem.

The most common way to provide online performance monitoring capabilities is by online access to DB2 trace information in the MONITOR trace class. Some online DB2 performance monitors also provide direct access to Db2 performance data by reading the control blocks of the Db2 and application address spaces. This type of monitoring provides a “window” to up-to-the-minute performance statistics while Db2 runs. This information is important if a quick reaction to performance problems is required.

Most online Db2 performance monitors provide a menu-driven interface accessible from TSO or VTAM. It enables online performance monitors to start and stop traces as needed based on the menu options chosen by the user. Consequently, you can reduce overhead and diminish the learning curve involved in understanding Db2 traces and their correspondence to performance reports.

Following are some typical uses of online performance monitors. Many online performance monitors can establish effective exception-based monitoring. When specified performance thresholds are reached, triggers can offer notification and take action. For example, you could set a trigger when the number of lock suspensions for the TXN00002 plan is reached; when the trigger is activated, a message is sent to the console, and a batch report is generated to provide accounting detail information for the plan. You can set any number of triggers for many thresholds. Following are some examples of thresholds you might choose to set:

  • When a buffer pool threshold is reached (PREFETCH DISABLED, DEFERRED WRITE THRESHOLD, or DM CRITICAL THRESHOLD).
  • For critical transactions, when predefined performance objectives are not met. For example, if TXN00001 requires subsecond response time, set a trigger to notify a DBA when the transaction receives a class 1 accounting elapsed time exceeding 1 second by more than 25%.
  • Many types of thresholds can be established. Most online monitors support this capability. As such, you can customize the thresholds for the needs of your DB2 environment.

Most online performance monitors can produce real-time EXPLAINs for long-running SQL statements. If an SQL statement takes a significant amount of time to process, an analyst can display the SQL statement as it executes and dynamically issue an EXPLAIN for the statement. Even as the statement executes, an understanding of why it takes so long to run can be achieved. A complete discussion of the EXPLAIN statement is provided in the next chapter.

Some online performance monitors provide historical trending. These monitors track performance statistics and store them in DB2 tables or in VSAM files with a timestamp. They also provide the capability to query these stores of performance data to assist in the following:

  • Analyzing recent history. Most SQL statements execute quickly, making difficult the job of capturing and displaying information about the SQL statement as it executes. However, you might not want to wait until the SMF data is available to run a batch report. Quick access to recent past-performance data in these external data stores provides a type of online monitoring that is as close to real time as is usually needed.
  • Determining performance trends, such as a transaction steadily increasing in its CPU consumption or elapsed time.
  • Performing capacity planning based on a snapshot of the recent performance of DB2 applications.
Some monitors also run when Db2 is down to provide access to the historical data accumulated by the monitor.

A final benefit of online DB2 performance monitors is their capability to interface with other z/OS monitors for IMS/TM, CICS, z/OS, or VTAM. This way, an analyst gets a view of the entire spectrum of system performance. Understanding and analyzing the data from each of these monitors, however, requires a different skill. Quite often, one person cannot master all these monitors

Conclusion 

Although this blog entry was brief, and there are many additional aspects to Db2 performance monitoring, monitoring and measuring performance metrics should be viewed as a vital requirement for all organizations using Db2 databases. By collecting and analyzing performance data, DBAs can detect performance bottlenecks, identify areas for improvement, and make informed decisions to enhance overall system efficiency. 


Friday, June 15, 2018

Db2 for z/OS Performance Traces Part 3 - Trace Destinations and IFCIDs


In parts 1 and 2 of this series we examined each of the six types of Db2 traces that you can start to get Db2 to record information about its activities. Be sure to check out those earlier blog posts to learn about what is available. 

Today we are going to look at a couple of additional details with regard to Db2 traces, starting with where the trace records can be written.

Trace Destinations

When a trace is started, Db2 formats records containing the requested information. After the information is prepared, it must be externalized somewhere. Db2 traces can be written to six destinations:

  • GTF, or Generalized Trace Facility, is a component of z/OS and is used for storing large volumes of trace data.
  • SMF , or System Management Facility, a source of data collection used by MVS to accumulate information and measurements. This destination is the most common for DB2 traces.
  • SRV is a routine used primarily by IBM support personnel for servicing Db2.
  • OPn, where n is a value from 1 to 8, is an output buffer area used by the Db2 Instrumentation Facility Interface (IFI).
  • OPX is a generic output buffer. When used as a destination, OPX signals Db2 to assign the next available OPn buffer (OP1 to OP8).
The Instrumentation Facility Interface, which is a DB2 trace interface, enables Db2 programs to read, write, and create Db2 trace records and issue Db2 commands. Many online Db2 performance monitors are based on the IFI.

But which trace destination should you use? Well, it depends! Consult Table 1 below for a synopsis of the available and recommended destinations for each Db2 trace type. A "Y" indicates that the specified trace destination is valid for the given type of trace; Default indicates that it is the default destination for that trace type.



Note: you can click on the Table to expand its size for easier viewing.

If you use SMF as your Db2 trace destination, consider using the SMFCOMP system parameter (DSNZPARM), which was introduced in Db2 10. This parameter causes the compression of Db2 trace records written to SMF. When SMFCOMP is set ON, the z/OS compression service CSRCESRV is used to compress any Db2 trace records written to SMF.

Use this parameter if your shop is concerned about the volume of SMF records written by Db2.

Using IFCIDs

Sometimes a trace class can be too broad, by which I mean that it generates more information than you need for the probelm at hand. This brings us to the IFCID.

Each trace class is associated with specific trace events known as Instrumentation Facility Component Identifier (IFCIDs), pronounced “if-kid.” An IFCID defines a record that represents a trace event. IFCIDs are the single smallest unit of tracing that can be invoked
by Db2.

In some cases, it can make sense to avoid activating trace classes altogether and start traces specifying only the IFCIDs needed. This way, you can reduce the overhead associated with tracing by recording only the trace events needed.

You can use trace classes 30 through 32 for starting IFCID-only traces. These classes have no predefined IFCIDs and are available for a location to use. Consider the following -START TRACE command, which starts only IFCIDs 1, 2, 42, 43, 107, and 153:

-START TRACE(PERFM) DEST(GTF) CLASS(30) IFCID(1,2,42,43,107,153)

If you do not specify the IFCID option, only those IFCIDs contained in the activated trace classes are started. The maximum number of IFCIDs per trace is 156.

Because this task can be tedious, if you decide to trace only at the IFCID level, it is probably a good idea to use a performance monitor that starts these IFCID-level traces based on menu choices. For example, if you choose to trace the elapsed time of Db2 utility jobs, the monitor or tool would have a menu option for this, initiating the correct IFCID traces (for example, IFCIDs 023 through 025).

There are several hundred different IFCIDs. Most IFCIDs contain data fields that describe events pertinent to the event being traced. Some IFCIDs have no data; instead they merely mark a specific time. For example, IFCID 074, which marks the beginning of Terminate Thread, has no data field.

Certain trace events of extended durations require a pair of IFCIDs: one for the beginning of the event and another for the end. These pairs enable the computation of elapsed times. Other trace events that are not as lengthy require only a single IFCID. 

You can find the IFCIDs associated with each trace class in the IBM DB2 Command Reference in the section describing the -START TRACE command. But that manual does not describe the purpose of each IFCID. A list describing each IFCID can be found in the SDSNIVPD(DSNWMSGS) data set. It also contains DDL and LOAD utility statements that can be used to create DB2 tables containing IFCID information that can be easily queried by SQL.

For example, the following query retrieves field descriptions for performance trace records for particular classes:

SELECT DISTINCT NAME, CLASS, DESCRIPTION, A.IFCID, SEQ
FROM   ownerid.TRACE_DESCRIPTIONS  A,
       ownerid.TRACE_TYPES         B
WHERE  A.IFCID = B.IFCID
AND    TYPE =
'PERFORMANCE'
AND    CLASS IN (1,2,3,4,5,6,7)
ORDER BY SEQ;

You can modify the query as needed to retrieve only the specific IFCID information you need.

Synopsis


This series of blog posts has offered up a high-level introduction to Db2 tracing and what type of information is available using traces. A comprehensive strategy for monitoring the performance of your Db2 subsystems and applications is an important part of establishing a best practices approach to database performance management. Use this information to help you on your journey to an efficiently monitored Db2 for z/OS environment.


Of course, this information is just the beginning. Keep checking back on this blog for additional useful information, and/or make sure you have a copy of my book, Db2 Developer's Guide, which contains over 1,600 pages of Db2 guidance and advice.


Monday, April 18, 2016

Don’t let ICIs put your DB2 application in the ICU!

Those of you who have been paying close attention have probably already noticed, or indeed encountered, incompatibility issues with how DB2 for z/OS behaves from version to version. Oh, sure, we all know that there have been deprecated features, and we deal with those over the long periods of time it takes for the features to be completely removed from DB2. Sure, we don't like it, but it is not really a huge problem to manage.

But there are other DB2 incompatibilities that are more troublesome to manage. You see, over the course of the past several releases of DB2 for z/OS, IBM has been making changes that can modify the behavior of your DB2 application programs. Code that worked one way works differently after migration. The number of incompatible changes being introduced by IBM started off slowly in DB2 9 but has grown to a significant number today. So much so that there IBM has introduced ICIs, or incompatible change indicators that can be traced using IFCIDs. 

Additionally, there are ways to repress these changes from occurring, but it is not quite as simple as that. What ever is that simple, right?

Well, if you are at all interested in learning more about DB2 incompatibilities and how to manage them, join me and Roy Boxwell for our webinar, Don’t let ICIs put your DB2 application in the ICU! 

We will discuss the issues, how they impact your applications, and offer up some guidance on how to get your arms around the problem. And Roy will show us how SEG's Workload Expert technology can be used to make it easier to track these issues, as well as to manage and control their impact on your DB2 applications.

So register today and join us in this informative webinar scheduled for April 28 2016 at 1:00 PM Eastern time.


This session will discuss the incompatible changes, their potential impact on your applications, as well as provide guidance on how to tackle the whole experience. And you’ll also see how SEGUS’s Workload Expert technology makes it easier to manage and control these incompatible changes. With all of this information at your disposal, you can make sure that your DB2 applications do not wind up in the ICU (intensive care unit)!