Auditing users of STRSQL
I want to audit the SQL statements of users who use
STRSQL. I know if you press F13, you can change the interactive SQL Session
Services. If you choose option 4 it will save all SQL statements into a file.
But I was wondering if it is possible if we setup this as default when starting
up STRSQL?
So I want the SQL statements always to be written into a certain file without using F13 and choose option 4 all the time.
Software/Hardware used:
STRSQL, iSeries
So I want the SQL statements always to be written into a certain file without using F13 and choose option 4 all the time.
Software/Hardware used:
STRSQL, iSeries
ASKED:
February 16, 2010 9:02 PM
UPDATED:
February 18, 2010 3:40 AM
Answer Wiki:
Consider setting an exit program for the STRSQL command against the QIBM_QCA_RTV_COMMAND exit point. (Or possibly the QIBM_QCA_CHG_COMMAND exit point.) When your exit program is called, run STRDBMON for the job. The DB monitor will end when the job ends.
You might want to mark the fact that the DB monitor had already been started within the job so you don't try to start it multiple times for times when STRSQL is run again in the same job. Maybe create a job environment variable in case you want to check its existence later in the job. Or you can simply handle a CPF436C (I think) message if it gets sent.
It's less easy to know when to turn the DB monitor off. It shouldn't hurt much to leave it on for as long as that job runs, but it will continue collecting information after STRSQL is exited. You can ignore or remove extra records later.
Otherwise, you might need to front-end STRSQL with your own process. Start and end the DB monitor directly in that process instead of in the exit program. You'd then want the exit program to handle cases where the command was somehow being accessed outside of your wrapper.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
We used a product called Tango 400 that uses journals to monitor SQL updates and provides reports that stand up to auditors.