DB2 Version 10.1 for Linux, UNIX, and Windows

MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW - Get formatted row-based component times

The MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW table function returns formatted row-based output for the component times contained in an XML metrics document.

Syntax


>>-MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW--(--xmldoc--)---------><

The schema is SYSPROC.

Table function parameters

xmldoc

An input argument of type BLOB(8M) that contains an XML document with either a system_metrics or activity_metrics element. XML documents with these elements can be obtained from the following sources:

Returned by one of the MON_GET_*_DETAILS table functions.
From the metrics column output by statistics and activity event monitors.
From the formatted output of the unit of work, or package cache event monitors.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

The following example returns the breakdown of component times within the DB2® database manager for service subclasses, which shows both the total time spent in any given component, as well as the amount of time that was actually spent processing, rather than waiting, in a component.

SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS,
	 SUBSTR(T.SERVICE_SUBCLASS_NAME,1,19) AS SUBCLASS,
	 T.MEMBER,
	 SUBSTR(COMP.METRIC_NAME,1,15) AS METRIC_NAME
	 SUBSTR(COMP.PARENT_METRIC_NAME,1,15) AS PARENT_NAME
	 COMP.TOTAL_TIME_VALUE AS TOTAL_TIME,
	 COMP.PROC_TIME_VALUE AS TOTAL_PROC_TIME,
   COMP.COUNT 
	FROM TABLE (MON_GET_SERVICE_SUBCLASS_DETAILS(NULL, 
               NULL,-2)) AS T,
   	   TABLE(MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW(                                  
                                             T.DETAILS
                                            )) AS COMP

	WHERE COMP.PARENT_METRIC_NAME IS NOT NULL;

The following is an example of output from this query.

SUPERCLASS          SUBCLASS           MEMBER METRIC_NAME     PARENT_NAME    ... 
------------------- ------------------ ------ --------------- ---------------...
MYSC                MYSSC                   0 TOTAL_COMPILE_T TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_IMPLICIT_ TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_SECTION_T TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_COMMIT_TI TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_ROLLBACK_ TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_RUNSTATS_ TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_REORG_TIM TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_LOAD_TIME TOTAL_RQST_TIME...
MYSC                MYSSC                   0 TOTAL_SECTION_S TOTAL_SECTION_T...

  9 record(s) selected.

The following is a continuation of sample output from this query.

...TOTAL_TIME           TOTAL_PROC_TIME      COUNT               
...-------------------- -------------------- --------------------
...                 100                  100                    1
...                   0                    0                    0
...                1253                  953                    0
...                 213                  153                    0
...                   0                    0                    0
...                   0                    0                    0
...                   0                    0                    0
...                   0                    0                    0
...                   0                    0                    0

  9 record(s) selected.

Information returned

Table 1. Information returned for MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW
Column Name	Data Type	Description
METRIC_NAME	VARCHAR(128)	The unique identifier for the total time metric value.
PROC_METRIC_NAME	VARCHAR(128)	The unique identifier for the processing time metric.
TOTAL_TIME_VALUE	BIGINT	The total time value in milliseconds corresponding to metric_name.
PROC_TIME_VALUE	BIGINT	The processing time value in milliseconds corresponding to proc_metric_name
COUNT	BIGINT	Number of occurrences of this type of interval.
PARENT_METRIC_NAME	VARCHAR(128)	The identifier of the parent total time metric whose value contains the total_time_value as a subset
PARENT_PROC_METRIC_NAME	VARCHAR(128)	The identifier of the parent processing time metric whose value contains the proc_time_value as a subset

XML documents that contain an element of type system_metrics are generated from the following interfaces:

MON_GET_CONNECTION_DETAILS
MON_GET_SERVICE_SUBCLASS_DETAILS
MON_GET_UNIT_OF_WORK_DETAILS
MON_GET_WORKLOAD_DETAILS
DETAILS_XML column from a STATISTICS event monitor
METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the UNIT OF WORK event monitor
XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the UNIT OF WORK event monitor

See Table 2 for the types of metrics and their parent metrics that are returned from the XML document in this case:

Table 2. Metric names returned by MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW for XML documents containing a system_metrics element type
Metric Name	Proc Metric Name	Parent Metric Name	Parent Proc Metric Name	Description of metric or Monitor element
TOTAL_RQST_TIME	NULL	NULL	NULL	total_rqst_time - Total request time
TOTAL_COMPILE_TIME	TOTAL_COMPILE_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_compile_time - Total compile time
TOTAL_IMPLICIT_COMPILE_TIME	TOTAL_IMPLICIT_COMPILE_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_implicit_compile_time - Total implicit compile time
TOTAL_SECTION_TIME	TOTAL_SECTION_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_section_time - Total section time
TOTAL_COMMIT_TIME	TOTAL_COMMIT_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_commit_time - Total commit time
TOTAL_ROLLBACK_TIME	TOTAL_ROLLBACK_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_rollback_time - Total rollback time
TOTAL_ROUTINE_USER_CODE_TIME	TOTAL_ROUTINE_USER_CODE_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_routine_user_code_time - Total routine user code time
TOTAL_RUNSTATS_TIME	TOTAL_RUNSTATS_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_runstats_time - Total runtime statistics
TOTAL_REORG_TIME	TOTAL_REORG_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_reorg_time - Total reorganization time
TOTAL_LOAD_TIME	TOTAL_LOAD_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_load_time - Total load time
TOTAL_SECTION_SORT_TIME	TOTAL_SECTION_SORT_PROC_TIME	TOTAL_SECTION_TIME	TOTAL_SECTION_PROC_TIME	total_section_sort_time - Total section sort time
TOTAL_STATS_FABRICATION_TIME	TOTAL_STATS_FABRICATION_PROC_TIME	TOTAL_COMPILE_TIME	TOTAL_COMPILE_PROC_TIME	total_stats_fabrication_time - Total statistics fabrication time
TOTAL_SYNC_RUNSTATS_TIME	TOTAL_SYNC_RUNSTATS_PROC_TIME	TOTAL_COMPILE_TIME	TOTAL_COMPILE_PROC_TIME	total_sync_runstats_time - Total synchronous runstats time
TOTAL_CONNECT_REQUEST_TIME	TOTAL_CONNECT_REQUEST_PROC_TIME	TOTAL_RQST_TIME	TOTAL_RQST_TIME	total_connect_request_time - Total connection or switch user request time
TOTAL_CONNECT_AUTHENTICATION_TIME	TOTAL_CONNECT_AUTHENTICATION_PROC_TIME	TOTAL_CONNECT_REQUEST_TIME	TOTAL_CONNECT_REQUEST_PROC_TIME	total_connect_authentication_time - Total connection or switch user authentication request time

XML documents that contain an element of type activity_metrics are generated from the following interfaces:

MON_GET_ACTIVITY_DETAILS
MON_GET_PKG_CACHE_STMT_DETAILS
DETAILS_XML column from an ACTIVITY event monitor
METRICS column produced by EVMON_FORMAT_UE_TO_TABLES for the PACKAGE CACHE event monitor
XMLREPORT column of EVMON_FORMAT_UE_TO_XML for the PACKAGE CACHE event monitor

See Table 3 for the types of metrics and their parent metrics that are returned from the XML document in this case:

Table 3. Metric names returned by MON_FORMAT_XML_COMPONENT_TIMES_BY_ROW for XML documents containing an activity_metrics element type
Metric Name	Proc Metric Name	Parent Metric Name	Parent Proc Metric Name	Description or Monitor element
STMT_EXEC_TIME	NULL	NULL	NULL	stmt_exec_time - Statement execution time
TOTAL_ROUTINE_TIME	NULL	STMT_EXEC_TIME	NULL	total_routine_time - Total routine time
TOTAL_ROUTINE_NON_SECT_TIME	TOTAL_ROUTINE_NON_SECT_PROC_TIME	TOTAL_ROUTINE_TIME	STMT_EXEC_TIME	total_routine_non_sect_time - Non-section routine execution time
TOTAL_ROUTINE_USER_CODE_TIME	TOTAL_ROUTINE_USER_CODE_PROC_TIME	TOTAL_ROUTINE_NON_SECT_TIME	TOTAL_ROUTINE_NON_SECT_PROC_TIME	total_routine_user_code_time - Total routine user code time
TOTAL_SECTION_TIME	TOTAL_SECTION_PROC_TIME	STMT_EXEC_TIME	STMT_EXEC_TIME	total_section_time - Total section time
TOTAL_SECTION_SORT_TIME	TOTAL_SECTION_SORT_PROC_TIME	TOTAL_SECTION_TIME	TOTAL_SECTION_PROC_TIME	total_section_sort_time - Total section sort time