Here is an idea that you might try. Write a version of the STRSQL command that will sit in a library above QSY, so it gets called instead of IBM's version. From this command, call a program that does a STRDBMON command and then runs qsys/strsql. The format of the STRDBMON command would be STRDBMON OUTFILE(LIBRARY/YOURFILE) TYPE(*DETAIL). The default is that the command runs against the current job.
The db monitor will capture information about the user's query session and write it to the file you specify. At the end of the program, do an ENDDBMON. You can then write your own QRY/400 query over this file to extract the SQL statements (minus some specifics on selection criteria). The field name that you are interested in from the output file of STRDBMON is QQ1000. That field may contain things other than the exact SQL statement, so you can limit the output to just seeing the SQL statements by applying some rules to the Select Records section of the query. For example, if you wanted to see just select statements, you could apply the rules shown below:
Select Records Type comparisons, press Enter. Specify OR to start each new group. Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT... AND/OR Field Test Value (Field, Number, 'Characters', or ...) QQ1000 LIKE 'sel%' OR QQ1000 LIKE 'SEL%'
The output will look something like this:
SELECT * FROM lib1/ifsauts WHERE "USER" = ? select * from qmpgdata/qapmjobos select * from qmpgdata/qapmjobos SELECT * FROM qmpgdata/qapmjobos WHERE JBUSER = ? SELECT * FROM qmpgdata/qapmjobos WHERE JBNAME = ? AND JBUSER = ?
Note that the actual selection criteria is replaced with a question mark (?). However, you can see the majority of the query, including the fields that were tested in the where clause.
There may be other ways to achieve the same results; this is just one possible way for you to get the information about what your programmers are up to.
Dig Deeper on iSeries system performance and monitoring
Related Q&A from Dan Reusche
One Search400.com member writes, "Why are the locked objects shown as addresses only and not by name, library and type?" Continue Reading
One Search400.com member writes, "What does the QDBSRVnn job do?" Continue Reading
One Search400.com member wrote, "We recently upgraded to V5R3, and we're no longer able to change system value QUTCOFFSET. Do you know what the ... Continue Reading