Rexx to track your error SQLCODE

30 october 2019

It is always interesting to have a tool to track errors in sql, because :

• An application error can happen and nothing is seen in MSTR, DBM1 ..
• Optimize your code as unnecessary and repeated errors is waste of CPU  

It happens to us recently, a DBA launched a creation of triggers in Production as requested by the development, unfortunately the triggers don't work correctly driving the triggering SQL statements (inserts, updates) to have negative sqlcode. As the negative sqlcode is not well signaled in the program, the application support doesn't know what happens to the programs and  15 long minutes  happen between the alert and the problem resolution, and it was by chance because the DBA is here to check his triggers and realized that there is a problem.
We had Apptune , but it was disabled to save some CPU consumption. 
So i decided to write this Rexx to do exactly what Apptune does : Start a trace and then IFI Read from Opx buffers.

Update 27/05/2020 : 

Follow this link to get the Rexx code to read IFCID 0053 
https://github.com/ndt98/Db2z/blob/master/ifc053.rex

My daily monitor

I work on a Db2 site that manage about 150 Db2 subsystems from our banking group. So tooling is essential to have  eyes on what happen on them daily.

This Rexx runs on all our production lpars every morning, reads the statistics and write a warning / alert when some threshold is reached. (I also have one that reads directly the BMC Performance Reporter DMRSTAT table, contact me if you need it )

This is updated frequently with all the informations i get from the best practices and presentations ....


Get the code here
https://github.com/ndt98/Db2z/blob/master/rx1001h.rex


We also have a Rexx to scan daily the Db2 stc (mstr, dbm1 , irlm ..) output  , doing DFSMS checks , controling the  interval between two offloads ... put these informations in a dataset and send alerts to e-mail accounts.  Then we  can call a Rexx to read all these informations and present them to us in  a central, unique point.
It is here : https://github.com/ndt98/Db2z/blob/master/%24%24%24DB2.rex

Read dynamic statement cache full sql text IFCID 316 IFCID 317

This Rexx reads "dynamically" the dynamic statement cache with basic statistics on the  each SQL execution. It uses IFCID 316 and IFCID 317 (to get the full sql text )
With IFCID316 and IFCID 401 you have all executed statements at hand

Get the code here
https://github.com/ndt98/Db2z/blob/master/ifc316.rex

Rexx to decode IFCID 366 and IFCID 376 with sql statement for dynamic SQL

Finding SQL using incompatible functions is easy with static SQL as the package  and the statement number is provided, but it is difficult for dynamic SQL.
This Rexx captures IFCID 366/376 and for each  SQL statement returned , it reads IFCID 317 to get the full sql text in the case of dynamic SQL.

Last update : 27/05/2020

Get the rexx here : https://github.com/ndt98/Db2z/blob/master/ifc376f.rex