Rexx SMF 102 Decoder :Trace SQL statement, host var and sqlcode, process IFCID 224 and IFCID 366

3/2019 Note : I just upload the Rexx to process IFCID 366 dynamically (using IFI READS / READA functions) , it is the recommended way to go to process  IFCID 366 IFCID 376 as this allows you the get the full SQL statement for dynamic SQL.  

28/01/2019 Add EDM requests stats 
06/2018 : Dataset Extend IFCID 258 - This is useful to track how often and when a dataset extend happens. As you may know, dataset extension is very bad in a performance perspective of an insert because DB2 has to find the place foe the insert in all the dataset before extending it, so if you can avoid this, your program will appreciate this. We have resolved a big issue of locks during inserts by using dsn1prnt (to check how the data is spread) and tracking ifcid 258. We've learned that when the spacemap page is locked by a thread, a concurent thread wil try to use another spacemap for inserts and if it can't after trying all the spacemap it will extend your dataset ...You you can get a dataset full even if there is nothing in the table ...   

19/12/2017 : Recently i need to analyse my workfile datasets usage , IFCID 342 is perfect for this, it reports each dataset used by Sort or Temprorary Table, the Kilobytes used and the Thread token which uses it ... By reading the stats, i can clarify a lot of things not written in the manual ...

24/08/2017 : i use this rexx with the IFCID224 option to list all the packages with Invalid SProc (we have thousands of this by hour) - when IBM says that the impact is 0-10% CPU , it is worth to do the rebind.
13/4/2017 : New source updated
10/4/2017 :  This rexx supports also IFCID 376, as IFCID366 and IFCID376 have exactly the same mapping in SDSNMACS. IFCID376 is just an enhanced, aggregate version of IFCID366. So use this rexx and just modify the code  
(...) ifi ifcid = 376 then ... call qw0366... (...)

06/04/2017 : Minor corrections

6/12/2016 : Add processing of IFCID366 (useful to indentify unsupported functions) and IFCID224 (Invalid Select Procedures encountered invalid, this arrives after applying new PTF or migration)  To keep REX102 simple, the program can process one kind of work in a pass, this means that if you process IFCID366 , set IFCID366=Y and the others options to N


18/08/2016 : Add the possibility to trace SQL activity : SQL statement text, including values of the input Host variables and SQLCODE. I wrote this because we had a weird behavior in our development DB2 with a program, and i wanted to trace the SQL text with host variables value used at execution time. It was impossible because there is a bug in Mainview. So, i wrote my own SQL activity decoder to be independent of the possible bugs/ configuration problem ... from the monitoring softwares.    
The trace to start is :
- STA TRACE(PERFM) DEST(SMF) AUTHID(xxx) CLASS(30) IFCID(63,247,53,58,350)
63 and 350 is for SQL text
247 is for Host Variable in input
53 and 58 is for the SQLCA ( sqlcode ...) 
=> if dynamic sql : 63,53,58,247 (or 350 instead of 63 if long sql text > 2500 bytes)
=> if static sql :  247,53,58 (sql statement is not provided but there is the stmtno that you can use to get the corresponding sql text in SYSPACKSTMT)
 


It is easy to decode DB2 SMF  data when you already have an example of Rexx code for SMF 100 and 101.  SMF102 program is copied from the SMF100 code.
Please use the same JCL as SMF100 jcl (don't forget to change x'64' to x'66' in the Sort Step to select only SMF102 records)
SMF102 is generally attached to "Performance Data" , and we can think that if we don't have a PERFM trace started, we don't have anything in SMF102. In fact, even if this trace is not started, you have a lot of IFCID records generated in SMF 102 data. Just try at your site.

This program has been written to decode ifcid 90 (DB2 commands text),as at our site, the DB2 commands are not tracked and so there is no way to know who has submitted a STOP DATABASE at 03:10 AM (It is nearly what happened to me when i was on call). We have Mainview , and it can have the commands logged (Monitor trace, not SMF) , but for a reason i don't know it doesn't work (and it is a quite long history to make it work ...). [Correction : it seems that commands are also logged in the DB2 logs]
There is an interesting feature : Display all the IFCID read from the SMF102 dataset, with a description and number of appearance, this helps to see if the ifcid data is there but never exploited (CPU waste ...), or to detect something wrong is one counter is abnormally high (Number of Dataset extend events for example). I will integrate this display in SMF100 and SMF101 rexx programs later.

     

23/06/2016 : The rexx now can decode IFCID316 and IFCID401 (Dynamic and static statement cache) , IFCID401 is produced in SMF only when the package is flushed from the cache, so it is more complete to read it with READS command (Check the READS sample that i provide also)
  
Have a nice day.
Duc

Get the code here (Update 27/05/2020)

https://github.com/ndt98/Db2z/blob/master/rex102.rex