Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Exclamation Unanswered: Enable SQL Query traces on db2diag.log

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by RASE

    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
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    you might want to try db2trc (see db2 manuals)
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  6. #6
    Join Date
    Jan 2009
    Posts
    5
    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

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •