A previous article talked about logging the slow SQL statements.
There are some investigations where it helps to understand the SQL that is being executed.
One example is when a start centre is slow. If a start centre has a lot of portlets then there could be a number of queries that take a while but are lower than the threshold used for the slow SQL statements.
One method to investigate this is to enable SQL tracing which logs the SQL statements that Maximo executes.
This post refers to entries in the SystemOut.log /
When SQL Tracing is enabled it generates output like this:
[INFO] [MAXIMO] BMXAA6719I - USER = (SMITHJ) SPID = (68) app (null) object (SR) : select * from sr where 'SR' = 'SR' and 'SR63576' = ticketid
This functionality isn’t normally enabled.
Interpreting the message
[INFO] [MAXIMO] BMXAA6719I - USER = (SMITHJ) SPID = (68) app (SR) object (SR) : select * from sr where 'SR' = 'SR' and 'SR63576' = ticketid
Translated into non-technical terms this message is telling us:
· User SMITHJ was working in the SR (Service Requests) application
· The user performed an action that meant Maximo executed a piece of SQL. The query suggests that the user was working in the List tab and was using a filter to find the data.
· The query was a select on the SR table - select * from sr where 'SR' = 'SR' and 'SR63576' = ticketid
Tip -> Enable this logger on a development system and perform some common user processes to see the SQL that is generated. If you see the sample output then it will be easier to spot odd entries when a problem occurs.
Understanding which areas can be traced
The logging can be applied to different parts of Maximo which is important if you are looking at problems in a production system.
It may be necessary to record the SQL executed just for a particular area e.g. the WORKORDER objects.
Areas that can be logged include:
· Maximo Services e.g. WORKORDER – Library type code that performs various functions
· MBOs – Code related to specific objects e.g. WORKORDER
· All SQL – Maximo uses the database a lot so this is not advisable in production.
Will this functionality capture all the SQL statements Maximo executes?
No. The core Maximo code uses database tables to store details of the object definitions e.g. attributes of the WORKORDER object. In general these are highly efficient and do not cause problems.
Those statements are visible using database level SQL performance tools. Technote 1605339 explains how to enable this kind of tracing for DB2.
There can be a small number of cases where invalid SQL statements generate ERROR messages but no SQL statement is recorded when SQL tracing is enabled. This was fixed under APAR IV78784. If you are seeing this problem then check fixcentral so see if there is fix pack available that includes this fix.
Enabling SQL Tracing
1. Open the Logging application
2. Filter the root loggers to SQL
3. Move to the loggers sub section and click New Row
4. Select the object or service to change the log level to.
5. Set the Log level to INFO to log the SQL statements – This can generate a lot of log entries
6. Save the record
7. Select Action->Apply Settings
Technote 1385917 says that turning on the SQL logger put a 3-4% overhead on performance, it can also generate a lot of log entries so it should be used sparingly.
Tip -> As a minimum the SQL root logger should be set to WARN. There are some useful diagnostic messages that are written out when the WARN level is used.
Tip -> Consider configuring a logger so that it writes the output to a separate log file. Technote 1385917 explains how to configure it. Vetasi uses a custom log analyser which allows us to filter and summarise entries from the SystemOut.log file (among many others) so my blog articles will assume that the log entries are written to SystemOut.log
What is the SPID and why haven’t you explained about it?
The SPID uniquely identifies the DB connection used to execute the query. In other applications this would be very important because you could check all the log entries for that connection and find all the SQL statements being executed by that user.
Maximo is a very clever application and it allows users to share connections simultaneously and to use multiple connections at the same time. This means that you can’t make the assumption that all SQL statements being executed on that connection are for the same user.
A later article will discuss database connections in more detail.
If you like this article then please share or like it. Why not look at the other articles listed in this index.