how to log use of STRSQL

I am trying to get the following monitor of STRSQL to work...but when I
add the library that contains the command above QSYS, it errors out
because it has 'invalid parameters'...if I run the program that starts
the STRDBMON from my test library, it works as stated...I can't test the
program by keying in 'STRSQL' from the command line because that would
pick up the IBM version of the command with no monitor in place...the
closest I could get was to call the CL program that is called by the new
command and it works just fine...any help would be appreciated.
Mary Koetting


How to log use of STRSQL by Dan Deusche

QUESTION POSED ON: 09 June 2005

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

EXPERT RESPONSE

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.