Environment
NetIQ Security Solutions for iSeries 8.0
System Auditing and Reporting (SAR)
SQL/QRY Monitor
PSAudit 8.0
Situation
The SQL/QRY Audit Report shows '?' entries instead of the values used.
My SQL/QRY Audit Report does not show the actual query values I used.
Resolution
The SQL/QRY monitor uses the IBM Database monitor (STRDBMON) to gather data about the queries used. The IBM Database monitor uses a "?" to replace variables. The actual value is listed after the query.
As a workaround, you can run the SQL/QRY Audit report (from PSMENU, select options 1, 1, 5, 3, and 9) interactively, then query the SAR0520F file in library QTEMP. Selecting the QQ1000 field yields the variable used for the '?' character.
With the addition of PTF 1A03008 for OS version V5R4, the SQL/QRY Audit Report will include the values specified on the WHERE clause of SQL SELECT and UPDATE statements, immediately following the end of the SQL statement.
For example:
For a SELECT, the values for question marks will appear immediately following the SQL statement.
SELECT * FROM MYLIB/MYFILE WHERE COLUMN1 LIKE ? OR COLUMN2 LIKE ? AAA%, BBB%
For an UPDATE, the values for question marks will appear immediately following the SQL statement.
UPDATE MYLIB/MYFILE SET COLUMN3 = ? WHERE COLUMN4 LIKE ? AND COLUMN5 = ? OR COLUMN6 = ? CCC%, DDD, EEE
Notice there are 4 question marks in the statement above and only 3 values following the last question mark. This is because the IBM STRDBMON outfile does not provide values specified for the SET clause, only values within the WHERE clause.
Regarding DELETE, the values in the WHERE clause will appear on the report, as in the following example:
DELETE FROM MYLIB/MYFILE WHERE COLUMN1 LIKE ? %SEARCHVALUE%
Regarding INSERT, IBM?s STRDBMON outfile does not provide values being inserted, so they will not appear on report:
INSERT INTO MYLIB/MYFILE VALUES(?)
Additional Information
The data for the INSERT statement is not available due a limitation in OS/400.