Hello,

I want to capture the sql query executed by the application to be captured on the db2diag.log file on the DB2 side. I have set the db2 trace level to 4. (default is 3 i believe). Still i am not able to get the SQL query stmt on the db2diag.log file.

Please let me know how to enable this to get the SQL query stmt on DB2DIAG.LOG file under the <<db2_inst_admin_home>>/sqllib/db2dump

Thanks and Regards,
Rase.

There is no way of doing that. You could create an event monitor for statements, but the information would go to a different place. You can read more in the manual.

you might want to try db2trc (see db2 manuals)

The db2diag.log is intended for, well, diagnostic purposes in case something goes wrong. It is not an audit facility, so queries shouldn't be written there (except for diagnostic purposes). I would use the existing mechanisms (like event monitors) and work with them.

Another easier way for doing the same is "get snapshot for all applications"
whenever all your queries are running. Repeat the same in frequent interval (like 5 minutes etc). Open the SNAPSHOT files one after another, and get a hint of the SQL queries running on the database with important facts like (Lockwait, Compiler Cost, Rows-Read Vs Rows Selected, Users running the query, Application Active/Idle time etc). Don't forget to RESET the MONITOR before you start the above excercise.

Hello All,

Thanks for your suggestions.

One more general query on enabling the traces on DB2. If i set my trace level to the max (to 4) and try running these kind of snapshots on a very frequent intervals, will it affect the performance of the DB server (say for a 100~150 user connected to the server)

Regards.
RASE

Tracing, diagnostic information, and snapshots are completely different things. Setting the DIAGLEVEL (not trace level) to 4 does not have an influence on capturing snapshots. You should only set the DIAGLEVEL that high if instructed by IBM support. Otherwise, level 3 (default) is typically used.

Collecting snapshots very frequently will have an impact on performance, of course, because you put additional load on the system. However, that additional work should be minimal and I rather doubt that you will be able to exactly measure it. In any case, I hope you don't want to do that to capture all SQL statements that way? Because this wouldn't work.