CLM Server Monitoring - Database Statement Monitor (DEPRECATED: CLM Server Monitoring is no longer supported and was dropped in version 6.0.3.)

Authors: JorgeAlbertoDiaz
Build basis: Collaborative Lifecycle Management 4.0.4

The DB Statement Monitor is responsible for gathering performance information of the creation and processing of database (DB) statements.

JMX Interface Name: com.ibm.team.server.monitoring.management.MonitorRollupMBean
JMX Object Name: team.server.<application public URI context>@<port>:type=DB Statement
JMX Object Name Example: team.server./jts@9443:type=DB Statement

A JMX object of this "type" will be created for each statement and each SQL statement processing phase: Prepare or Execute.

• DB Statement Monitor JMX Object Name: team.server.<application public URI context>@<port>:type=DB Statement
  
• DB Statement JMX Object Name: team.server.<application public URI context>@<port>:type=DB Statement,phase=(Prepare | Execute),name="<statement>"
  

The DB Statement Monitor performance metrics information gathered in its attributes is a rolled up of the performance information from all the individual monitored database statements. Visually, the DB Statement Monitor objects structure is like the following:

   team.server./<URL>@<port>:type=Cache
          |
          |
          |----Prepare
          |             |
          |             |
          |             ---- team.server./<URL>@<port>:type=DB Statement,phase=Prepare,name="<statement>"
          |                      ...    
          |----Execute
                        |
                        |
                        ---- team.server./<URL>@<port>:type=DB Statement,phase=Execute,name="<statement>"
                        ...      

Monitor Attributes

Following are the attributes exposed by the DB Statement Monitor

JMX Attribute	JMX Type	Is Writeable?	Default Value	Description
AverageResponseTime	long	No	0	Average response time from all the DB statements
Count	long	No	0	Counter of all monitored operations
Duration	long	No	0	Total duration of DB statements processing operations
Enabled	boolean	Yes	false	Whether this monitor is enabled. Default is disabled so no performance monitoring of DB statements is performed
MaximumResponseTime	long	No	0	Maximum response time from all the DB statements operations
MinimumResponseTime	long	No	0	Minimum response time from all the DB statements operations
Threshold	java.lang.Long	Yes	10000	Defines the maximum allowed time for a DB statement to be processed. If this time is exceeded a problem will be generated
TimeSinceLastReset	long	No	0	Total time since the last statistics reset

Note the attributes related with time statistics are expressed in miliseconds

Monitor Operations

JMX Operation Signature	Return Type	Description
dismissAllProblems()	void	When called, all the problems tracked by this monitor are dismissed
reset()	void	When called, resets the statistics of the monitor

Child MBeans

This section gathers information regarding MBeans that are contained within this monitor

DB Statement objects

An instance of a monitored DB statement object in Prepare or Execute phase. A different JMX object is created for the Prepare and Execute phases of the statement processing.

JMX Interface Name: com.ibm.team.server.monitoring.jmx.beans.sql.SQLStatementRollupMBean
JMX Object Name: team.server.<application public URI context>@<port>:type=DB Statement,phase=(Prepare | Execute),name=<>
JMX Object Name Example 1 (DB Statement Prepare phase): team.server./ccm@9443:type=DB Statement,phase=Prepare,name="select t1.ITEM_ID, t1.STATE_ID from FRIENDS.FRIEND t1"
JMX Object Name Example 2 (DB Statement Execute phase): team.server./ccm@9443:type=DB Statement,phase=Execute,name="select t1.ITEM_ID, t1.STATE_ID from FRIENDS.FRIEND t1"

Attributes

JMX Attribute	JMX Type	Is Writeable?	Default Value	Description
AverageResponseTime	long	No	0	Average response time spent processing this statement
Count	long	No	0	Counter of monitored operations
Duration	long	No	0	Total duration of the processing operations for this statement
MaximumResponseTime	long	No	0	Maximum response time that took to process this statement
MinimumResponseTime	long	No	0	Minimum response time that took to process this statement
SQL	java.lang.String	No	SQL statement	SQL statement. It matches the value of "name" of the JMX Object name of this statement instance removing scaping characters
TimeSinceLastReset	long	No	0	Total time since the last statistics reset

Note the attributes related with time statistics are expressed in miliseconds

Operations

JMX Operation Signature	Return Type	Description
reset()	void	When called, resets the statistics of this monitored statement

Database Statement Problems

When a Database statement related operation exceeds the threshold, a Problem DB Statement object is created. This object provides information that uniquely identifies the statement operation that exceeded the threshold. The generated JMX object follows the convention:

JMX Interface Name: com.ibm.team.server.monitoring.jmx.beans.ResourceInstanceMBean
JMX Object Name: team.server.<application public URI context>@<port>:type=Problem DB Statement,phase=Execute,name="<statement>",id=ID
JMX Object Name Example: team.server./ccm@9443:type=Problem DB Statement,phase=Execute,name="SELECT C.ITEM_ID, C.MODIFIED FROM ( SELECT ITEM_ID,MODIFIED FROM REPOSITORY.CHANGE_EVENT WHERE EVENT_CATEGORY IN (\?) ) AS C order by C.MODIFIED desc",id=d9e73493-a5ab-4a33-bc43-5857c1d0a449

Where phase and name matches the one of the DB Statement monitor object. A different problem object instance (with different ID) will be created each time the execution of a DB statement exceeds the defined threshold.

Attributes

Following are the attributes exposed by the DB Statement Cache objects

JMX Attribute	JMX Type	Is Writeable?	Default Value	Description
Duration	long	No	0	Total duration of the operation. This value will be greater than the defined threshold
FinishDate	Date	No	Finish Date/time	Date/time the operation finished
Identifier	String	No	UUID	Object UUID
Links	TabularType (Open Type)	No		Links to associated performance information. This attribute is not currently used in this release
Name	String	No	Object name	The statement. It matches the value of the name entry of the JMX Object name
RDF	String	No		RDF Representation of this object. This attribute is not currently used in this release
StartDate	Date	No	Start Date/time	Date/time at which the operation started
ThreadID	long	No		The ID of the thread that executed the operation
ThreadName	String	No		Name of the thread which activity resulted in the DB statements creation. It provides context information of the root operation
ThreadTraceRDF	String	No		The trace of other monitored object invocations along with their associated metrics as RDF. This attribute is not currently used in this release
URI	String	No		This attribute is not currently used in this release

Operations

JMX Operation Signature	Return Type	Description
dismiss()	void	When called, removes the problem instance

---

External links:

• IBM

Additional contributors: TWikiUser, TWikiUser