Track SQL Statements run by a specific user through ODBC Connection

215 pts.
Tags:
iSeries DB2
SQL statements
There is a user id connecting to our iSeries System through ODBC Connection and will be executing some selected queries on iSeries using a 3rd Party Application. Those selected queries are approved by the Application Owner. Hence we would like to track the SQL Statements which are executed by this id on daily basis to see if the users are running queries other than which are approved.

Software/Hardware used:
i570, V6R1

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • pdraebel
    Have a look at exit point programming. A quick look at WRKREGINF pointed me to exit points QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2.
    2,845 pointsBadges:
    report
  • TomLiotta
    Simplest is probably iSeries Navigator, your connection, Databases-> {yourDatabase}-> SQL Performance Monitors. Set up a monitor for the <CurrentUser> filter to match the user id you want to track. You might create a new library just to hold the collected data. Specify that library for 'Schema for data:'. You probably want a 'Detailed' collection. Shouldn't be a need to collect anything but 'User' actions, but there's no way to know from here. Regardless, set one up for yourself first, just to get a feel for it. After it starts, use Navigator to view the content of a table or two, try 'Run SQL scripts' to run a query or two. Then end the monitor and select it for analysis. Look at what it gives you so you'll have an idea if it suits your need. Tom
    125,585 pointsBadges:
    report
  • Haaiderali
    Tom as suggested I had created a SQL Monitor through iseries Navigator after which SELECT Queries were executed and on completion the monitor was ended. However while analysing the output I could not find the SQL Statements which were exeucted by my ID. I also tried running the below command which creates a dump of the SQL Statements run through a Particular ID. DMPSYSOBJ OBJ(ISQLSTQLIKVIEW*) CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE) Command failed with error as No objects printed because no objects found.
    215 pointsBadges:
    report
  • TomLiotta
    ...while analysing the output I could not find the SQL Statements which were exeucted by my ID. Please clarify. Could not find the statements? ...or could not find a way to see the statements? Were any statements shown at all for you? Note that for ODBC/JDBC, the monitor must track 'Current user'. The 'Job user' will not work. Also, any filters may cause statements to be excluded from the list. I also tried running the below command which creates a dump of the SQL Statements run through a Particular ID. That is only used by interactive SQL, the STRSQL command. It's used just for STRSQL to be able to show you where you left off in previous uses of the command. If you don't save your session when you exit, the command won't store anything there. But the SQL monitor will capture the statements even if you don't save your STRSQL session. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    The ‘Job user’ will not work. That's not entirely true. You can use 'Job user', but the user will usually be QUSER and the results will include everybody running ODBC/JDBC jobs -- and possibly more. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following