Where my DB2 database spending time

In order to identify where DB2 is spending most of it's time.

In 9.7 new in memory monitoring functions has been introduced.

One of the most interesting and useful feature I found, now you can easily determine where DB2 is spending most of its time.

You can use this SQL, V9.7 onward.

select u.metric_name,

u.parent_metric_name,

sum(u.total_time_value),

sum(u.count)

from table(mon_get_service_subclass_details(null,null,-2)) as t,

table(mon_format_xml_times_by_row(t.details)) as u

group by metric_name, parent_metric_name

Output:

Metric_Name	Parent_Metrics_Name	Total_Time_Value	Count
AGENT_WAIT_TIME	TOTAL_WAIT_TIME	0	0
AUDIT_FILE_WRITE_WAIT_TIME	TOTAL_WAIT_TIME	0	0
AUDIT_SUBSYSTEM_WAIT_TIME	TOTAL_WAIT_TIME	0	0
CLIENT_IDLE_WAIT_TIME		12781606
DIAGLOG_WRITE_WAIT_TIME	TOTAL_WAIT_TIME	452	211
DIRECT_READ_TIME	TOTAL_WAIT_TIME	13351	6066
DIRECT_WRITE_TIME	TOTAL_WAIT_TIME	635	90
FCM_MESSAGE_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	65799	35803
FCM_MESSAGE_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	0	0
FCM_RECV_WAIT_TIME	TOTAL_WAIT_TIME	66430	35943
FCM_SEND_WAIT_TIME	TOTAL_WAIT_TIME	0	0
FCM_TQ_RECV_WAIT_TIME	FCM_RECV_WAIT_TIME	631	140
FCM_TQ_SEND_WAIT_TIME	FCM_SEND_WAIT_TIME	0	0
IPC_RECV_WAIT_TIME	TOTAL_WAIT_TIME	414	105880
IPC_SEND_WAIT_TIME	TOTAL_WAIT_TIME	443	108850
LOCK_WAIT_TIME	TOTAL_WAIT_TIME	0	0
LOG_BUFFER_WAIT_TIME	TOTAL_WAIT_TIME	0	0
LOG_DISK_WAIT_TIME	TOTAL_WAIT_TIME	48250	6832
POOL_READ_TIME	TOTAL_WAIT_TIME	1398	990
POOL_WRITE_TIME	TOTAL_WAIT_TIME	1233	147
TCPIP_RECV_WAIT_TIME	TOTAL_WAIT_TIME	0	0
TCPIP_SEND_WAIT_TIME	TOTAL_WAIT_TIME	0	0
TOTAL_COMMIT_PROC_TIME	TOTAL_RQST_TIME	7344	11901
TOTAL_COMPILE_PROC_TIME	TOTAL_RQST_TIME	13021	19386
TOTAL_IMPLICIT_COMPILE_PROC_TIME	TOTAL_RQST_TIME	0	0
TOTAL_LOAD_PROC_TIME	TOTAL_RQST_TIME	0	0
TOTAL_REORG_PROC_TIME	TOTAL_RQST_TIME	0	0
TOTAL_ROLLBACK_PROC_TIME	TOTAL_RQST_TIME	2485	19189
TOTAL_ROUTINE_USER_CODE_PROC_TIME	TOTAL_RQST_TIME	23046	1247
TOTAL_RQST_TIME		504097	149909
TOTAL_RUNSTATS_PROC_TIME	TOTAL_RQST_TIME	8968	61
TOTAL_SECTION_PROC_TIME	TOTAL_RQST_TIME	21926	31140
TOTAL_SECTION_SORT_PROC_TIME	TOTAL_SECTION_PROC_TIME	22	182
TOTAL_WAIT_TIME	TOTAL_RQST_TIME	132649
WLM_QUEUE_TIME_TOTAL	TOTAL_WAIT_TIME	0	0