Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

How to log use of STRSQL

Some special IT users have the possibility of using STRSQL. Is there any chance to log what they use it for?

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