DBAs have a variety of tools to identify performance problems and one of the best ones is the ability to configure the database to record all the SQL that a user executes. This is often used in performance investigations for non-Maximo installations and periodically support calls are raised like this:

“the user is complaining about the performance of their queries in the Maximo front-end – the DBA wants to configure the database level SQL logging/tracing for that user.

Please provide instructions on how to identify the database connection that the user is using”

How does database session level SQL logging/tracing work?

Applications make database connections to execute the SQL for the users.

Each user session is assigned a SQL connection and their SQL is executed using that connection.

The DBA is able to enable SQL tracing for all the SQL executed on a selected connection. The application support team tells the DBA which connection is associated with the user.

The DBA configures the database to record details for a user’s session.

Why doesn’t this work with Maximo?

Maximo creates a pool of database connections using a single database user. It uses that pool for all its SQL operations *.

A user’s query could be submitted via one of a number of connections and over time that connection will change.

The situation can become even more complicated.

A user’s operation could result in a series of related queries being executed. For example opening a wokorder will force Maximo to query the database to retrieve data for the various tabs.

* the exception for this is the BIRT and Cognos reporting engines which handle database connections differently. 

What is the solution?

The Maximo developers foresaw the need to trace user’s SQL and built SQL Tracing functionality into Maximo. This allows the SQL to be traced and includes the userid that submitted the query.

I discussed the SQL tracing functionality in this article