Re: SQL logging

On 23-Feb-2012 11:31 , Mike Cunningham wrote:

We have an application running on a Linux server making a database
connect via ODBC to DB2 on our iSeries. On some occasions CPU use on
the iSeries goes sky high on the QZDASOINIT jobs that service the
ODBC connection. This application has some components that were
developed as DB2 stored procedures, all written in SQL (no high level
RPG stored procedures). Looking at the jobs does not reveal much. We
have hundreds of users on this system and the high CPU use is not
consistent. We suspect that it might be one of the stored procedures
that might be doing this but don't know which one. Is there any way
to log every request coming in from an ODBC connection so the next
time it happens we can see what was being run around the time of high
CPU? We have dumped the DB2 database transaction journal records and
they do not show high numbers during the busy time which makes me
think it is either a loop in the code in a procedure doing
calculations or is an index being built on the fly. However index
advisor does not show any long running indexing going on.

The "database monitor". IIRC the monitor can be activated as part of a connection string.

_i Connection string keywords - Diagnostic properties i_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzaik/rzaikconnstrkeywordsdiagprop.htm
"
Use these iSeries Access ODBC driver connection string keywords to change Diagnostic properties of the ODBC connection.

The following table lists connection string keywords for Diagnostic properties that are recognized by the iSeries Access ODBC driver:
...
"

_i Server-side ODBC diagnostic and performance tools i_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzaii/rzaiiodbc62serverside.htm
"
Use server-side tools to help diagnose iSeries Access ODBC problems.

The following tables contain ODBC diagnostic and performance tools the server side:

Server-side tools
...
Performance tools
...
You can also use the tools Database Monitor and Visual Explain. Refer to the iSeries Navigator Online help for more information.
...
"

Regards, Chuck