Hello
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.

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.

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.

Hello.
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?

Regards