








	*SMF Tools*

Sample Report from SMF 102, IFCID 380 Records
DB2 Stored Procedures Timing Report

It's easy to report on SMF data!

We have a low-cost 4GL report writer especially for SMF files. It's called Spectrum SMF Writer.

Spectrum SMF Writer handles the difficult SMF record parsing for you automatically. You just specify which fields you want to see.

Spectrum SMF Writer also converts the arcane date and time fields and reformats them into an attractive report.

Plus Spectrum SMF Writer even exports SMF data as comma delimited files to use on your PC.

The sample SMF report below was created with Spectrum SMF Writer, the low-cost 4GL SMF report writer.

In this report, the primary input file is the SMF file. We select just the type 102 "DB2 Audit" records with IFCID 380 ("Stored Procedure Detail Record"). (See SMF 102 record layout.) (But note that IFCID 380 sections also include the IFCID 233 section.)

We use a COMPUTE statement to translate the bit flags in QW0233EX into user-friendly text for our report. Other COMPUTE statements use Spectrum SMF Writer's "make number" function to convert the STCK times in the SMF record to numeric number of seconds.

We then print the data in a nicely formatted report. We customize the report by specifying our own column headings and display formats.

All of this with just a few lines of code!
Why not install a Spectrum SMF Writer trial right now and start making your own SMF reports!

These Spectrum SMF Writer Statements:

********************************************************************** 
* NAME THE INPUT FILE AND DEFINE THE 102 V11 FIELDS                  * 
********************************************************************** 
INPUT:  SMF10211                                                       
                                                                        
********************************************************************** 
* INCLUDE ONLY 102 RECORDS FROM INPUT FILE                           * 
********************************************************************** 
INCLUDE: SM102RTY = 102 AND QWHSIID = 380                              
                                                                        
COMPUTE: EVENT_TY =                                                    
         WHEN(QW0233EX = X'00')ASSIGN('ENTRY')                         
         WHEN(QW0233EX = X'01')ASSIGN('EXIT')                          
                                                                        
COMPUTE: CLS1CP = #MAKENUM(QW0380_CLS1CP)                              
COMPUTE: CLS2CP = #MAKENUM(QW0380_CLS2CP)                              
COMPUTE: CLS2ELAP = #MAKENUM(QW0380_CLS2ELAP)                          
COMPUTE: ROUTINEID = #SUBSTR(QW0233RID,7,2)                            
 
**********************************************************************
* NAME THE COLUMNS WE WANT TO SEE ON THE REPORT                      *
**********************************************************************
COLUMNS:                                                             
     SM102DTE('SMF DATE')  SM102TME('SMF TIME')                      
     EVENT_TY('EVENT TYPE')                                          
     QW0233PR('ROUTINE SPECIFIC NAME')                               
     QW0233SC('SCHEMA NAME')                                         
     CLS1CP(PIC'ZZ9.999999')                                         
     CLS2CP(PIC'ZZ9.999999')                                         
     CLS2ELAP(PIC'ZZ9.999999')                                       
     ROUTINEID(HEX)                                                  
 
**********************************************************************  
* SPECIFY THE REPORT TITLE                                           *  
**********************************************************************  
TITLE: #DATE #TIME / 'DB2 STORED PROCEDURE REPORT' / 'PAGE' #PAGE

Produce This SMF Report:

 08/15/17 10:34 AM                        DB2 STORE PROCEDURE REPORT                                PAGE    1

 SMF DATE  SMF TIME   EVENT TYPE ROUTINE SPECIFIC NAME SCHEMA NAME   CLS1CP     CLS2CP    CLS2ELAP  ROUTINEID
 ________ ___________ __________ _____________________ ___________ __________ __________ __________ _________

 08/11/17 14:11:05.40   ENTRY     SQLPROCEDURECOLS      SYSIBM       0.000000   0.000000   0.000000   293C
 08/11/17 14:11:05.44   EXIT      SQLPROCEDURECOLS      SYSIBM       0.004721   0.004604   0.037211   293C
 08/11/17 14:11:05.44   ENTRY     AFVCUSSP              AAFCOD1      0.004721   0.004604   0.037211   19B0
 08/11/17 14:11:05.45   EXIT      AFVCUSSP              AAFCOD1      0.006151   0.005320   0.048462   19B0
 08/11/17 14:11:05.98   ENTRY     SQLPROCEDURECOLS      SYSIBM       0.000000   0.000000   0.000000   293C
 08/11/17 14:11:05.98   EXIT      SQLPROCEDURECOLS      SYSIBM       0.001275   0.001195   0.001942   293C
 08/11/17 14:11:05.98   ENTRY     AFSTRNSP              AAFCOD1      0.001275   0.001195   0.001942   19B2
 08/11/17 14:11:06.03   EXIT      AFSTRNSP              AAFCOD1      0.005356   0.004777   0.041130   19B2
 08/11/17 14:11:06.56   ENTRY     SQLPROCEDURECOLS      SYSIBM       0.005356   0.004777   0.041130   293C
 08/11/17 14:11:06.58   EXIT      SQLPROCEDURECOLS      SYSIBM       0.006595   0.005947   0.055923   293C
 08/11/17 14:11:06.60   ENTRY     AFATRNSP              AAFCOD1      0.006595   0.005947   0.055923   19AE
 08/11/17 14:11:06.71   EXIT      AFATRNSP              AAFCOD1      0.009744   0.008570   0.168445   19AE
 08/11/17 14:11:07.19   ENTRY     SQLPROCEDURECOLS      SYSIBM       0.000000   0.000000   0.000000   293C
 08/11/17 14:11:07.20   EXIT      SQLPROCEDURECOLS      SYSIBM       0.001293   0.001209   0.001946   293C
 08/11/17 14:11:07.21   ENTRY     AFADUPSP              AAFCOD1      0.001293   0.001209   0.001946   19AF
 08/11/17 14:11:07.25   EXIT      AFADUPSP              AAFCOD1      0.003331   0.002629   0.022961   19AF
 08/11/17 14:11:07.85   ENTRY     SQLPROCEDURECOLS      SYSIBM       0.000000   0.000000   0.000000   293C
 08/11/17 14:11:07.86   EXIT      SQLPROCEDURECOLS      SYSIBM       0.001159   0.001082   0.005425   293C
 ...

See Other Sample SMF Reports

Spectrum SMF Writer - the 4GL SMF Report Writer.

Sample Report from SMF 102, IFCID 380 Records DB2 Stored Procedures Timing Report

It's easy to report on SMF data!

These Spectrum SMF Writer Statements:

Produce This SMF Report:

Sample Report from SMF 102, IFCID 380 Records
DB2 Stored Procedures Timing Report