The logSQLTimeLimit Property and Its Use

Technote (FAQ)


Question

Is there a way to use the logSQLTimeLimit property to generate critical debug information for troubleshooting performance, stability, and runtime issues?

Answer

In V6 the mxe.db.logSQLTimeLimit property can be found in the path:
<application source root>/applications/maximo/properties/maximo.properties
The default value for this property is 1000.

In V7 the logSQLTimeLimit property can be found in the system properties application
The default value for this property is 1000.

The value associated with this property refers to milliseconds (1000 = 1 second) .

and the output generates any SQL statement that runs for longer than the value specified in the property

When enabled, this property generates an entry in the log for any SQL statement that runs for longer than the value specified in the property.

Each activity the user invokes in the application executes many SQL statements against the database. When too many SQL statements are running for more than 1 second the users may experience sluggish response from the application.

This property will not generate any output if the threshold is not met. This means that setting the value to a higher number may not provide the necessary results to diagnose a problem.

Typical output to the logs might look as follows:

01 Mar 2010 08:37:18:030 [INFO] app (null) object (INVOICELINE) :select * from invoiceline where (cstm_invsuspcode='QTY' and invoicenum in (select invoicenum from maximo.invoice where exists (select * from maximo.wfassignment where processname='INVQTYDISC' and ownerid=invoiceid)) and(select sum(invoiceqty*conversion) from maximo.invoiceline i1, maximo.invoice i2 where invoiceline.ponum=i1.ponum and invoiceline.polinenum=i1.polinenum and invoiceline.orgid=i1.orgid and invoiceline.siteid=i1.siteid and i1.invoicenum = i2.invoicenum and i1.orgid = i2.orgid and i1.siteid = i2.siteid and i2.documenttype not in ('CREDIT') and i2.status not in (select value from maximo.synonymdomain where maxvalue='CANCEL' and domainid = 'IVSTATUS')) <= (select 1.05*nvl(sum(quantity),0) from maximo.matrectrans, maximo.poline where matrectrans.ponum=poline.ponum and matrectrans.polinenum=poline.polinenum and matrectrans.orgid = poline.orgid and matrectrans.siteid = poline.siteid and matrectrans.polinenum=invoiceline.polinenum and matrectrans.ponum=invoiceline.ponum and matrectrans.orgid = invoiceline.orgid and matrectrans.siteid = invoiceline.siteid and ((matrectrans.issuetype in ('RECEIPT', 'RETURN') and poline.inspectionrequired=0) or (matrectrans.issuetype in ('TRANSFER', 'RETURN') and poline.inspectionrequired=1)) )) (execution took 336836 milliseconds)

In this example, the SQL statement ran for 336836 milliseconds or 5.6 minutes. This type of statement would have a heavy impact on database server resources. This will impact all users attached to the system while the database server provides services to this one request.

IBM has internal tools that can correlate and trend this information combined with the output of the mbocount property and the fetchResultLogLimit to determine many causes of performance, stability, and runtime problems. Since this runs every minute, IBM can gather an understanding of the health of the JVM over a period of time. Often IBM will request up to 24 hours of logs to determine trends in user loads, memory requirements and many other points of interest.

This property should be left running at all times since a problem can often not be foreseen or replicated. In order to troubleshoot or find root cause of many issues, the data from this property is required.

For more information on debug properties see the document Using debug properties to monitor and troubleshoot performance

Also see:

The document what is the mbocount property

Related information

What is MBOCount
What is FetchResultLogLimit
TPAE Document Collections


Cross reference information
Segment Product Component Platform Version Edition
Systems and Asset Management IBM Maximo Asset Management Essentials
Systems and Asset Management Tivoli Asset Management
Systems and Asset Management Tivoli Asset Management for IT
Systems and Asset Management Tivoli Change and Configuration Management Database
Systems and Asset Management Tivoli Service Request Manager
Systems and Asset Management Tivoli Service Automation Manager

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM Maximo Asset Management

Software version:

6.0, 6.1, 6.2, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.1.2, 7.2, 7.2.1, 7.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1426051

Modified date:

2010-03-29

Translate my page

Machine Translation

Content navigation