Analyze MXG mainframe performance data with Excel

Mainframe administrators can analyze a report from the MXG software suite by importing the information in Excel.

Mainframes by design produce detailed performance information that accounts for nearly every processor cycle and I/O request. Making sense of all that data can be a challenge, even for the most grizzled mainframe pro. Fortunately, there are some handy tools and techniques to ease the process of collecting and analyzing mainframe performance data.

Using MXG to gather mainframe data
MXG is a suite of Statistical Analysis System (SAS) programs from the SAS Institute designed to collect and analyze mainframe performance data. Once the information is imported, MXG can summarize and issue a detailed report. The MXG package also includes an authoritative textbook by MXG founder Dr. Barry Merrill on mainframe performance and tuning along with the MXG installation materials.

Extracting mainframe performance data starts with the right record. The CICS Monitoring Facility (CMF) type 110 system management facility (SMF) record contains dozens of fields tracking every transaction. MXG gathers the CMF records into a SAS dataset named CICSTRAN. MXG breaks some of these fields into two pieces, a counter and a timer. For instance, MXG divides the CMF user dispatch field (USRDISPT) into the number of times CICS dispatched a task (TASDSPCN) as well as the amount of time the task was available to dispatch (TASDSPTM). MXG also calculates some fields, such as un-captured wait (WTUNIOTM), which contains delays that can’t be accounted for by all the other wait buckets. Besides CMF, MXG can also capture data from other products such as Omegamon and Datacom.

The sample SAS code below prints the transaction ID, task start time, CICS region name, response time, CPU and file wait time for every record in CICSTRAN:

proc print data=cicstran label;     
    id strttime;     
    var tranname applid iresptm cputm wtfciotm;     
    format iresptm cputm wtfciotm 12.6;

This code produces an enormous amount of data. If an administrator wants specific events that are already known, further processing is not required. But administrators might want to do a deeper dive to assess the mainframe’s performance more thoroughly. This requires an analytical tool. There are numerous ones available, but Microsoft Excel can provide a convenient and low-cost alternative for data analysis. The goal is to get MXG data into a comma separated value (.CSV) format that Excel can open.

Rendering .CSV files from MXG
The addition of two SAS Output Data Services (ODS) statements can turn the above PROC PRINT report into a .CSV file without any additional coding:

ods listing close;         
ods csvall body=’/tmp/trans.csv’;
proc print data=cicstran label;     
    id strttime;     
    var tranname applid iresptm cputm wtfciotm;     
    format iresptm cputm wtfciotm 12.6;

The first “ods” statement directs SAS to close its log and list output files. The second “ods” line tells SAS to send all subsequent output to Unix System Services (USS) file /tmp/trans.csv in .CSV format. This code uses a USS file because it matches what Excel expects during the import process. It is also easier than coming up with a good record length.

The “label” operand in the third line tells SAS to print the variable labels at the top of the report. When imported into Excel, these labels become column headers. Also note the “format” statement that directs SAS to print the response, CPU and file I/O wait times in seconds instead of timestamps. This will make them easier to manipulate in Excel.

The resulting file contains values enclosed in double quotes and separated by commas, one row for every line in the report. The lines themselves can be any length, unrestrained by typical print line limits.

The .CSV file produced from MXG is first loaded into Excel
Figure 1
Use basic Excel filtering to locate key data points within the performance data file.
Figure 2
Filtering mainframe data can reveal performance relationships that offer new directions for tuning.
Figure 3
Excel can graph the .CSV data to help administrators identify trends and data anomalies.
Figure 4

Remember that ODS is a powerful facility. SAS programmers can use ODS to create web-ready HTML, send e-mails or produce PostScript files and handle other high-level tasks. It’s important to reference the ODS manual for more information about this power tool.

Visualizing .CSV report data in Excel
Now it’s time to analyze our mainframe performance data. Just double-clicking on a .CSV file launches Excel and loads the .CSV data into a worksheet. However, I prefer going through the text import wizard. The wizard adjusts column widths and gives you the option to skip lines to avoid including the report titles. The wizard also allows you to exclude columns and select formats. A typical data import looks similar to Figure 1. Note how the SAS variable labels become the column headers.

Some of the best data analysis is the simplest, such as using column filters. For instance, to see all the transactions that ran for greater than one second, an administrator can set a filter on the response time column as in Figure 2.

After filtering based on response time, you can see the long running transactions highlighted in Figure 3 spent most of their time waiting for file I/O.

Other simple techniques may provide important insights too. For instance, sorting rows based on CPU usage will highlight which transactions are the most processor intensive. Similarly, Excel has facilities for summing and averaging which can also help get down to the bottom of a problem.

Excel also makes graphing and other data visualization easy. An Excel wizard can graph the “Internal response time” data column to produce a visual representation of the data similar to Figure 4. This visualization can help administrators quickly identify anomalies and spot trends that might offer other avenues for performance investigation.

Making the most of MXG data in Excel
Excel is a comprehensive and sophisticated analytical tool in its own right, but there are some tips that can help you get more value from MXG imports:

  • Excel has problems interpreting SAS timestamp values which result in bad sorting and invalidly displayed values. To get around these issues you should import these fields as text.
  • By default, MXG likes to print fields like CPU and response time in the hh:mm:ss.x format, but this makes it difficult to do math on them. Tell SAS to print fields as numbers as shown in the FORMAT statement in the above SAS code fragments.
  • It can be tempting to download hundreds of fields for thousands of transactions into one large spreadsheet. But the sheer volume of data will make Excel crawl and analysis difficult. I recommend that you limit the data set imported into Excel. Pick a set of favorite metrics that most accurately reflect the behavior of your applications. For example, there’s no point in keeping the DB2 wait bucket for a purely VSAM application.

ABOUT THE AUTHOR: Robert Crawford has been a systems programmer for 29 years. While specializing in CICS technical support he has also worked with VSAM, DB2, IMS and assorted other mainframe products. He has programmed in Assembler, Rexx, C, C++, PL/1 and COBOL. The latest phase in his career finds him an operations architect responsible for establishing mainframe strategy and direction for a large Insurance company. He lives and works with his family in south Texas.

Dig Deeper on IBM system z and mainframe systems