technical: How I Process SMF Records with Excel

It seems that every day I'm processing SMF records, producing tables and charts for my clients. The most common are SMF type 70, 72 and 30 to analyse workloads. SMF type 14, 15, 42 and 64 (dataset activity) are also common, as are type 118/119 (TCP/IP) and type 80 (RACF). In fact, I do this so often, I have a process to quickly get the tables and charts I need. My tools of choice: Excel and Pivot tables.

So, in this article, I will show you how I quickly generate charts, using SMF Type 70 records as an example.

Step 1. Get Data in CSV Format

SMF records are simply data records: we need something to extract it out. Most clients will have software like Merrill MXG or Broadcom MICS to get this information. Failing this, I'll resort to creating a DFSORT ICETOOL job (an example processing SMF Type 30 records can be found in the July 2005 edition of the old Xephon Updates), custom REXX (Martin Packer talks about this in his blog) or similar. The new Zowe SMF Explorer also looks interesting.

But what format should I produce? Excel has features that can be used to import data. So, we could simply produce a text file with information in columns. However, I prefer to produce data in CSV format: it's easy to then open this using Excel.

Producing CSV usually isn't that difficult. As most sites will have Broadcom MICS or Merrill MXG, I can use SAS features to produce a CSV format. The easiest is to use the SAS ODS function:

ODS _ALL_ CLOSE;
ODS CSV FILE=CSV RS=NONE;
PROC PRINT;
   VAR SYSTEM JOB DATE
   CPUSRBTM CPUTCBTM CPUTM PROGRAM CPUIFETM CPUZIETM;
RUN;
ODS CSV CLOSE;

The following shows an example of a CSV file that I have created from SMF type 70 records.:

Plex,Sys,SDate,STime,EDate,ETime,Hr,Procsr,Lpar,R4hr,PrCap,Wght,EDCP_CPU
PRDPLX1,MVS1,30/06/2023,0:00:00,30/06/2023,0:15:00,0,8561-510,MVS1,395,805,892

I can now open this CSV file in Excel:

Step 2: Pivot Table

OK, I have my data, now I want to produce some charts or graphs. In this example, I'll produce a graph showing the four-hour rolling MSU (4HRA) CPU usage for all z/OS systems on one CEC. This is where I use Excel pivot tables. I select all the data that I want (including the heading row), and do this:

What I want is a table of four-hour rolling average MSU by z/OS system, date and hour. Here's the pivot table I create:

The rows are date (dd/mm/yyyy format) and hour. Columns are the z/OS system name.

The Value is the average of each 4HRA MSU value. This is worth noting. MSU values (including 4HRA MSU) are rates. So, we want the average of each rate by hour. If we were using CPU seconds or I/Os, we'd want to sum each value.

This produces a table. Sometimes, this is enough, and I can cut and paste values, and present them as a table.

One point: you'll notice that the 29-Jun has only one hour, and very small numbers for MVS3 and MVS5. This is probably because the SMF archive jobs for MVS3 and MVS5 occurred later, and the SMF records were put into our later archive. I'll exclude these, and start my analysis from 30-Jun-2023:

I'll similarly exclude any other 'outliers' like data that is a 'once off' or data that is incomplete.

Step 3: Graph

We have a pivot table. The final step is to use the Pivot table graph option:

I like stacked column graphs:

This will create the following graph:

From here, I can remove the 'buttons' from the graph by right-clicking any of the buttons, and selecting the "Hide All Field Buttons on Chart":

So, the final graph looks like this:

And there you have it. With practice, I can produce these graphs fast. However, I wouldn't give this graph to my client: it's not ready. It needs modifying and formatting, so it is clearer. I explain what I'd do in another article about graphs and charts.

David Stephens

LongEx Quarterly is a quarterly eZine produced by Longpela Expertise. It provides Mainframe articles for management and technical experts. It is published every November, February, May and August.

The opinions in this article are solely those of the author, and do not necessarily represent the opinions of any other person or organisation. All trademarks, trade names, service marks and logos referenced in these articles belong to their respective companies.

Although Longpela Expertise may be paid by organisations reprinting our articles, all articles are independent. Longpela Expertise has not been paid money by any vendor or company to write any articles appearing in our e-zine.