Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010

    Unanswered: Audit full sql statements including parameter markers

    I've searched the forum and the web for an understandable guide but can not find one, since all guide's seems to be for advanced users, and I'm not.

    Here's my situation. We are running 32bit db2 v9.5.fp4 enterprise on windows server 2003 x86. The application is a large and fairly undocumented 3rd party application so getting support from developers are very problematic.

    We suspect that the application "may" create multiple statements against different databases and somewhere in that "area" performance issues arises.

    Here's what we want to do.

    1. Capture the full sql statements generated by the application and user. Including the values of parameter markers.
    2. Preferably be able to capture full statements in realtime for one user during 10 minutes.

    I have not been able to capture the full sql statement with event monitor, only partially with parameter markers showing alot of "?" which is not very helpful. We need to know the "?"
    I have been able to isolate an installation without users so if I only could log "all statements" on an instance level that would be great. I have been looking into db2audit, but I simply can not get started because I don't understand it. I need a short tutorial.

    If our databases are like these in the default db2 instance.
    Personal (for personal data)
    Central (for central data)
    Auth (for user rights and groups)

    How would I get started on an isolated server?
    Preferably I would like to audit all statements generated by the application when I logon through the application and test the application.

    Are there any graphical utilities or 3rd party application like Quest that could in an production environment single out one user and capture all full sql statements in realtime for that user?

    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Why do you need the literals instead of parameter markers in order to fix a performance problem? You should be able to explain the SQL and determine what the access path is, and that can usually provide guidance on what indexes are needed to improve performance (since it appears you cannot change the code).

    BTW, if all the SQL is dynamic, I would run a snapshot for dynamic SQL to determine how long each statement takes on average, in order to isolate the problem statements.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    You can use Workload Manager activity monitor to capture SQL statements and their various performance metrics, such as cost, CPU utilization, or the number of rows retrieved. There are detailed instructions in the DB2 Infocentre: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    As Marcus said, you probably don't need parameter values in order to find and address performance issues. If you feel that you do, you will have to deal with the audit facility.

  4. #4
    Join Date
    Aug 2010
    Thanks for your reply.
    Two followup questions arises.
    Do you know if any trial ware for Workload Manager exists? Could only find a video demo.

    Do you have a link to a understandable guide for db audit / execute, since I want to record a series of full statements, and I guess a snapshot is just a one timer?


Posting Permissions

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