It’s difficult to find which program is producing an SQL error by looking in the joblog, especially for Apache instance jobs (CGI jobs). You can see the error, but you can’t find out the name of the program that issued it. To solve this problem, you can use SQL Performance Monitor tools. You can start an SQL Performance Monitor one of two ways:
- Use System i Navigator, expand the Databases tree node for the system you are connected to, right-click on SQL Performance Monitors and create a new one.
- Run command STRDBMON from a command line.
You can limit which jobs get monitored (with either approach). For example, if you want all the INTERNET Apache instance jobs, just specify a job name of INTERNET.
To end the monitor, use the same interface in System i Navigator, or use the ENDDBMON command.
Make sure you create the monitor to collect detailed, not summary information. You can also choose the file and library name in which to store the collected information.
Don’t leave the monitor running for too long- it has a negative performance impact on the system, and it writes lots of data quickly.
You can easily query the output table to find errors. For example, I started a monitor via the System i Navigator which output data to a table called QZG0000048 in library DUNCAN. Using STRSQL, you can find SQL errors and their corresponding program names. Use this query:
SELECT qqi8 as "sqlstate",qqc81 as "sqlcode",qqc104 as
"program library",qqc103 as"program name",qqc21,qq1000 FROM
qzg0000048 WHERE qqrid=1000 and qqi8 < 0 ORDER BY qqstim
There are lots of other fields in this file, too, such as job name, user name, etc. but be careful, because if you try to select all the fields for viewing using STRSQL your 5250 session might end abnormally – some of the fields contain binary data that is illegal to display.