Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the common dynamic performance views and monitoring table functions of db2. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Summary of dynamic View and Monitoring Table functions commonly used in DB2

Here I summarize the dynamic view and monitoring table functions that are commonly used in DB2. Another article related to monitoring will be sent when you are free, introducing the specific components of monitoring in DB2, as well as the relevant monitoring modes and monitoring parameters.

View container information:

Db2 "select tbsp_name,container_name,container_type,total_pages,usable_pages,accessible,1.0*fs_total_size_kb/1024 as fs_total_size_mb,1.0*fs_used_size_kb/1024 as fs_used_size_mb,1.0*fs_used_size_kb/fs_total_size_kb as fs_used_pct from SYSIBMADM.CONTAINER_UTILIZATION"

Check the rows read/rows return of app:

Db2 "select application_handle,rows_read_per_rows_returned from sysibmadm.mon_connection_summary"

View the longest SQL currently being executed:

Db2 "select elapsed_time_sec,activity_state,activity_type,application_handle from sysibmadm.mon_current_sql order by elapsed_time_sec desc fetch first 5 rows only"

View the efficiency of the buffer pool:

Db2 "SELECT BP_NAME, AVG_WRITE_TIME, SYNC_WRITES_PERCENT,AVG_SYNC_WRITE_TIME, AVG_ASYNC_WRITE_TIME FROM SYSIBMADM.MON_BP_UTILIZATION"

View various time-consuming element information in a service class in milliseconds:

Db2 "SELECT SUM (TOTAL_WAIT_TIME) AS WAIT,SUM (TOTAL_COMPILE_PROC_TIME) AS COMPILE,SUM (TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS IMP_COMPILE,SUM (TOTAL_SECTION_PROC_TIME) AS SECTION,SUM (total_routine_user_code_proc_time) as ROUTINE_USER_CODE,SUM (TOTAL_COMMIT_PROC_TIME) AS COMMIT,SUM (TOTAL_REORG_PROC_TIME) AS REORG,SUM (TOTAL_RUNSTATS_PROC_TIME) AS RUNSTATS,SUM (TOTAL_ROLLBACK_PROC_TIME) AS ROLLBACK SUM (TOTAL_LOAD_PROC_TIME) AS LOAD,SUM (total_connect_request_proc_time) as CONNECT_REQUEST FROM TABLE (MON_GET_SERVICE_SUBCLASS ('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL)) "

View the total percentage of time spent:

Db2 "WITH PCTPROC AS (SELECT SUM (TOTAL_SECTION_TIME) AS SECT_TIME, SUM (TOTAL_SECTION_PROC_TIME) AS SECT_PROC_TIME,SUM (TOTAL_COMPILE_TIME) AS COMP_TIME, SUM (TOTAL_COMPILE_PROC_TIME) AS COMP_PROC_TIME,SUM (TOTAL_IMPLICIT_COMPILE_TIME) AS IMP_C_TIME, SUM (TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS IMP_C_PROC_TIME,SUM (TOTAL_COMMIT_TIME) AS COMMIT_TIME SUM (TOTAL_COMMIT_PROC_TIME) AS COMMIT_PROC_TIME,SUM (TOTAL_ROLLBACK_TIME) AS ROLLBACK_TIME, SUM (TOTAL_ROLLBACK_PROC_TIME) AS ROLLBACK_PROC_TIME,SUM (TOTAL_RUNSTATS_TIME) AS RUNSTATS_TIME, SUM (TOTAL_RUNSTATS_PROC_TIME) AS RUNSTATS_PROC_TIME,SUM (TOTAL_REORG_TIME) AS REORG_TIME, SUM (TOTAL_REORG_PROC_TIME) AS REORG_PROC_TIME,SUM (TOTAL_LOAD_TIME) AS LOAD_TIME SUM (TOTAL_LOAD_PROC_TIME) AS LOAD_PROC_TIME FROM TABLE (MON_GET_CONNECTION (NULL,-2) AS METRICS) SELECT CASE WHEN SECT_TIME > 0 THEN DEC ((FLOAT (SECT_PROC_TIME) / FLOAT (SECT_TIME)) * 100SECT_TIME 5) ELSE NULL END AS SECT_PROC_PCT, CASE WHEN COMP_TIME > 0 THEN DEC ((FLOAT (COMP_PROC_TIME) / FLOAT (COMP_TIME)) * 100min5 (COMP_TIME)) ELSE NULL END AS COMPILE_PROC_PCT CASE WHEN IMP_C_TIME > 0 THEN DEC ((FLOAT (IMP_C_PROC_TIME) / FLOAT (IMP_C_TIME)) * 100 FLOAT 5) ELSE NULL END AS IMPL_COMPILE_PROC_PCT, CASE WHEN ROLLBACK_TIME > 0 THEN DEC ((FLOAT (ROLLBACK_PROC_TIME) / FLOAT (ROLLBACK_TIME)) * 100 THEN DEC 5) ELSE NULL END AS ROLLBACK_PROC_PCT, CASE WHEN COMMIT_TIME > 0 THEN DEC ((FLOAT (COMMIT_PROC_TIME) / FLOAT (COMMIT_TIME) * 100 THEN DEC 5) 1) ELSE NULL END AS COMMIT_PROC_PCT, CASE WHEN RUNSTATS_TIME > 0 THEN DEC ((FLOAT (RUNSTATS_PROC_TIME) / FLOAT (RUNSTATS_TIME)) * 100 THEN DEC 5 CASE WHEN REORG_TIME 1) ELSE NULL END AS RUNSTATS_PROC_PCT, CASE WHEN REORG_TIME > 0 THEN DEC ((FLOAT (REORG_PROC_TIME) / FLOAT (REORG_TIME)) * 100 REORG_PROC_TIME 5) ELSE NULL END AS REORG_PROC_PCT, CASE WHEN LOAD_TIME > 0 THEN DEC ((FLOAT (LOAD_PROC_TIME) / FLOAT (LOAD_TIME)) * 100L5 1) ELSE NULL END AS LOAD_PROC_PCT FROM PCTPROC "

Get information based on the lock name:

Db2 "SELECT SUBSTR (NAME,1,20) AS NAME,SUBSTR (VALUE,1,50) AS VALUE FROM TABLE (MON_FORMAT_LOCK_NAME ('030011000000000000000054')) as LOCK"

MON_GET_ACTIVITY: will only get the running application information. In other words, you should not be able to view historical information unless you use the ACTIVITY event monitor:

Some basic information:

[db2inst1@DB2_105 ~] $db2 "select application_handle,LOCAL_START_TIME,uow_id,activity_id,parent_uow_id,parent_activity_id,activity_state,activity_type FROM table (MON_GET_ACTIVITY (NULL,-1))"

Group 1:

Db2 "select application_handle,uow_id,activity_id,total_cpu_time,rows_read,rows_returned,query_cost_estimate FROM table (MON_GET_ACTIVITY (NULL,-1))"

Group 2:

Db2 "select application_handle,uow_id,activity_id,direct_reads,direct_writes,effective_isolation,effective_lock_timeout,effective_query_degree FROM table (MON_GET_ACTIVITY (NULL,-1))"

Group 3:

Db2 "select application_handle,uow_id,activity_id,direct_reads,direct_writes,effective_isolation,effective_lock_timeout,effective_query_degree FROM table (MON_GET_ACTIVITY (NULL,-1))"

Group 4:

Db2 "select application_handle,uow_id,activity_id,stmt_text,eff_stmt_text FROM table (MON_GET_ACTIVITY (NULL,-1))"

Get mempool information in KB:

Db2 "SELECT varchar (memory_set_type, 20) AS set_type,varchar (memory_pool_type,20) AS pool_type,varchar (db_name, 20) AS dbname,memory_pool_used,memory_pool_used_hwm FROM TABLE (MON_GET_MEMORY_POOL (NULL, CURRENT_SERVER,-2)"

MON_GET_PKG_CACHE_STMT: the difference from the above MON_GET_ACTIVITY is that the above thing only looks for the running XXX, while this one looks from the package cache. However, for things that no longer exist in package cache, you can't see them, so turn on the package cache monitor if you want to see it:

Group 1:

Db2 "select section_type,effective_isolation,num_executions FROM TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)"

Group 2:

Db2 "select lock_wait_time,log_buffer_wait_time,log_disk_wait_time,evmon_wait_time,diaglog_write_wait_time,pool_read_time,pool_write_time,direct_read_time,direct_write_time,prefetch_wait_time,total_section_proc_time FROM TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)"

Get Bufferpool hit rate:

Db2 "WITH BPMETRICS AS (SELECT bp_name, pool_data_l_reads + pool_temp_data_l_reads + pool_index_l_reads + pool_temp_index_l_reads + pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads, pool_data_p_reads + pool_temp_data_p_reads + pool_index_p_reads + pool_temp_index_p_reads + pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads) Member FROM TABLE (MON_GET_BUFFERPOOL (",-2) AS METRICS) SELECT VARCHAR (bp_name,20) AS bp_name,logical_reads,physical_reads,CASE WHEN logical_reads > 0 THEN DEC ((1-(FLOAT (physical_reads) / FLOAT (logical_reads) * 100jue 5) ELSE NULL END AS HIT_RATIO,member FROM BPMETRICS"

Or

Db2 "select substr (bp_name,1,30) as BP_NAME,data_hit_ratio_percent,index_hit_ratio_percent,prefetch_ratio_percent from sysibmadm.mon_bp_utilization where bp_name not like 'IBMSYSTEM%'"

View the heat meter:

Db2 "select substr (tabschema,1,15) as tabschema,substr (tabname,1,15) as tabname,TAB_TYPE,TABLE_SCANS,ROWS_READ, (ROWS_INSERTED+ROWS_UPDATED+ROWS_DELETED) as rows_IUD from table (MON_GET_TABLE (null,null,null)) order by rows_read desc"

View information about the index:

Db2 "select substr (mon.tabname,1,16) as table,member,substr (cat.indname,1,16) as IX_Name,mon.IID as Index_id,mon.index_scans, mon.index_only_scans from table (mon_get_index (NULL,NULL,-2)) as mon,syscat.indexes as cat where mon.tabname=cat.tabname and mon.tabschema=cat.tabschema and mon.iid=cat.iid order by mon.tabname"

View information about sorting:

Db2 "with dbcfg1 as (select int (value) as sheapthres_shr from sysibmadm.dbcfg where name='sheapthres_shr') select sheapthres_shr,sort_shrheap_allocated,dec ((100*sort_shrheap_allocated) / sheapthres_shr,5,2) as sheap_alloc_pct,dec ((100*sort_shrheap_top) / sheapthres_shr,5,2) as MAX_SHEAP_ALLOC_PCT,total_sorts,total_sort_time,1.0 * total_sort_time/ (total_sorts + 1) as avg_sort_time,sort_overflows 1.0 * sort_overflows / (total_sorts+1) as overflow_pct,active_sorts from dbcfg1,sysibmadm.snapdb "

View some information about the package cache:

Db2 "with dbcfg1 as (select int (value) as pckcachesz from sysibmadm.dbcfg where name='pckcachesz') select pckcachesz,pkg_cache_lookups,pkg_cache_inserts,pkg_cache_num_overflows,100*pkg_cache_size_top/ (pckcachesz*4096) as pkg_cache_alloc_pct from dbcfg1,sysibmadm.snapdb"

View some information about the catalog cache:

Db2 "with dbcfg1 as (select int (value) as catcachesz_pages from sysibmadm.dbcfg where name='catalogcache_sz') select catcachesz_pages,CAT_CACHE_LOOKUPS,CAT_CACHE_INSERTS,CAT_CACHE_OVERFLOWS, (1.0-1.0*CAT_CACHE_INSERTS/CAT_CACHE_LOOKUPS) as cat_insert_pct,100*CAT_CACHE_SIZE_TOP/ (catcachesz_pages*4096) as cat_cache_alloc_pct from dbcfg1,sysibmadm.snapdb"

View log usage information:

Db2 "select 1.0*TOTAL_LOG_AVAILABLE/1024/1024 as total_log_available_mb,1.0*TOTAL_LOG_USED/1024/1024 as total_log_used_mb,1.0*SEC_LOG_USED_TOP/1024/1024 as sec_log_used_top_mb,1.0*TOT_LOG_USED_TOP/1024/1024 as total_log_used_top,1.0*SEC_LOGS_ALLOCATED/1024/1024 as sec_logs_allocated_mb APPL_ID_OLDEST_XACT from TABLE (SNAP_GET_DB (CAST (NULL AS VARCHAR (128c)-2)) "

View log rate:

Db2 "select log_reads, (1.0*LOG_READ_TIME_S+1.0*LOG_READ_TIME_NS) / 1000000000.0 as log_read_time_s,LOG_WRITES, (1.0*LOG_WRITE_TIME_S + 1.0*LOG_WRITE_TIME_NS) / 1000000000.0 as log_write_time_s,NUM_LOG_WRITE_IO,NUM_LOG_READ_IO (1.0*LOG_READ_TIME_S+1.0*LOG_READ_TIME_NS) / 1000000000.0 / (1.0+num_log_read_io) as read_io_speed_s, (1.0*LOG_WRITE_TIME_S + 1.0*LOG_WRITE_TIME_NS) / 1000000000.0/ (1.0+num_log_write_io) as write_io_speed_s,NUM_LOG_PART_PAGE_IO,NUM_LOG_BUFFER_FULL NUM_LOG_DATA_FOUND_IN_BUFFER FROM TABLE (SNAP_GET_DB (CAST (NULL AS VARCHAR (128c)-2)) "

Statement execution time, you can adjust that order by to see:

Db2 "select num_executions,total_act_time as total_time_ms, (total_act_time/num_executions) as avg_time_ms,total_sorts, (total_sorts/num_executions) as sorts_per_stmt,substr (stmt_text,1,35) as sql_stmt from table (mon_get_pkg_cache_stmt) as dyn_cache where num_executions > 0 order by 2 desc fetch first 5 rows only"

View long-running SQL statements:

Db2 "select substr (application_name,1,16) as appl_name,elapsed_time_sec as elapsed_secondes,substr (activity_state,1,20) as status,substr (session_auth_id,1,16) as auth_id,total_cpu_time,rows_returned,substr (stmt_text,1,30) as sql_stmt from sysibmadm.mon_current_sql order by 2 desc"

View the wait time of the current app:

Db2 "select application_handle as app_id,total_wait_time,pool_read_time,pool_write_time,log_disk_wait_time,log_buffer_wait_time,tcpip_send_wait_time,tcpip_recv_wait_time,lock_wait_time from table (mon_get_connection (NULL,-1)) order by total_wait_time"

Check locks, etc.:

Db2 "select substr (lw.hld_application_name,1,10) as hold_app,substr (lw.hld_userid,1,10) as holder,substr (HLD_CURRENT_STMT_TEXT,1,16) as holder_stmt,substr (lw.req_application_name,1,10) as wait_app,substr (lw.req_userid,1,10) as waiter,substr (REQ_STMT_TEXT,1,16) as waiter_stmt,lw.lock_mode,lw.lock_object_type,substr (lw.tabname,1,10) as tabname,substr (lw.tabschema,1,10) as schema Lw.lock_wait_elapsed_time as waiting_seconds from sysibmadm.mon_lockwaits lw "

View the lock information held:

Db2 "select substr (conn.application_name,1,10) as application,substr (conn.system_auth_id,1,10) as authid,conn.num_locks_held as number_of_locks,conn.lock_escals as escalations,conn.lock_timeouts as lock_timeouts,conn.deadlocks as deadlocks, (conn.lock_wait_time/100) as lock_wait_time_ms from table (mon_get_connection (null,-1)) as conn"

Check the lock memory related:

Db2 "with dbcfg1 as (select float (bigint (value) * 4096) as locklist from sysibmadm.dbcfg where name='locklist'), dbcfg2 as (select float (bigint (value)) as maxlocks from sysibmadm.dbcfg where name='maxlocks') select dec ((lock_list_in_use/locklist) * 100ppe) as LOCK_LIST_USED_PCT,dec ((lock_list_in_use/ (locklist* (maxlocks/100)) * 100), 4meme 1) as TO_MAXLOCK_PCT,appls_cur_cons as NUMBER_OF_CONS Lock_list_in_use/appls_cur_cons as avg_lock_mem_per_con_bytes from dbcfg1,dbcfg2,sysibmadm.snapdb "

View the information of the full table scan:

Db2 "select substr (session_auth_id,1,10) as auth_id,substr (application_name,1,20) as appl_name,io_wait_time_percent as io_wait_pct,rows_read_per_rows_returned as rows_read_vs_returned from sysibmadm.mon_connection_summary"

Check the percentage of dirty pages:

Db2 "with db_snap as (select float (pool_drty_pg_steal_clns) as pg_steal,float (pool_drty_pg_thrsh_clns) as chg_pg_thrsh,float (pool_lsn_gap_clns) as softmax Float (pool_drty_pg_steal_clns+pool_drty_pg_thrsh_clns+pool_lsn_gap_clns) as total_clns from sysibmadm.snapdb where (pool_drty_pg_steal_clns+pool_drty_pg_thrsh_clns+pool_lsn_gap_clns) 0) select dec ((pg_steal/total_clns) * 100pje 4) as steals_pct,dec ((chg_pg_thrsh/total_clns) * 100pr 4) as threshold_pct,dec ((softmax/total_clns) * 100pr 4) as softmax_pct from db_snap "

View the information about prefetching (asny read data directly instead of from the index, and it is not in the cache pool at the beginning of the read, sync read directly from the index without scanning the table from the index):

Db2 "with bp_info as (select substr (bp_name,1,30) as bp_name,unread_prefetch_pages,pool_async_data_reads+pool_async_index_reads as async_reads,pool_data_p_reads+pool_index_p_reads+pool_temp_data_p_reads+pool_temp_index_p_reads as total_reads from table (mon_get_bufferpool (null,-1)) as bp_stats where bp_name not like 'IBMSYSTEM%') select bp_name,unread_prefetch_pages Dec (100 * (total_reads-async_reads) / total_reads,5,2) as sync_reads_pct,dec (100 million unreadable prefetchpages pagespace to taltalizer reads 2) as unread_pages_pct from bp_info "

View memory pool's information:

Db2 "select pool_id,pool_secondary_id,pool_cur_size,pool_watermark from sysibmadm.snapdb_memory_pool order by 1 Magazine 2"

View information about the oldest transaction:

Db2 "select substr (uow.workload_occurrence_state,1,20) as status,substr (uow.session_auth_id,1,10) as authid,uow.application_handle as appl_handle,int (uow.uow_log_space_used/1024/1024) as log_used_MB,uow.total_act_time as total_activity_time_ms,uow.total_act_wait_time as total_activity_wait_time,uow.uow_start_time as uow_start_time from sysibmadm.snapdb db Table (mon_get_unit_of_work (null,-1)) as uow where uow.application_handle=db.appl_id_oldest_xact "

View information about the tablespace:

Db2 "select substr (tbsp_name,1,30) as tablespace_name,tbsp_type as type,TBSP_USING_AUTO_STORAGE,TBSP_AUTO_RESIZE_ENABLED,substr (tbsp_state,1,20) as status, (tbsp_total_size_kb/1024) as size_MB, (100-tbsp_utilization_percent) as free_space_pct, ((100-tbsp_utilization_percent) * tbsp_usable_size_kb) / 100000) as free_space_mb from sysibmadm.mon_tbsp_utilization"

Get the information of PATH:

Db2 "select substr (type,1,20) as type,substr (path,1,50) as path from sysibmadm.dbpaths order by type"

Some information about the current connection, such as average CPU usage time, ROWS_READ_PER_ROWS_RETURNED:

Db2 "select application_handle as app_hdl,total_app_commits,total_app_rollbacks,avg_rqst_cpu_time,ROWS_READ_PER_ROWS_RETURNED from sysibmadm.MON_CONNECTION_SUMMARY"

The percentage of wait time in the total request time in the current connection:

Db2 "select application_handle as app_hdl,ROUTINE_TIME_RQST_PERCENT,RQST_WAIT_TIME_PERCENT from sysibmadm.MON_CONNECTION_SUMMARY"

Percentage of time spent by various WAIT TIME in the current connection (the time of LOG is not counted in IO):

Db2 "select application_handle as app_hdl,IO_WAIT_TIME_PERCENT,LOCK_WAIT_TIME_PERCENT,AGENT_WAIT_TIME_PERCENT,NETWORK_WAIT_TIME_PERCENT from sysibmadm.MON_CONNECTION_SUMMARY"

Percentage of time spent actually doing work in the current connection:

Db2 "select application_handle as app_hdl,SECTION_SORT_PROC_TIME_PERCENT, (SECTION_PROC_TIME_PERCENT-SECTION_SORT_PROC_TIME_PERCENT) as SECTION_NON_SORT_TIME_PERCENT,COMPILE_PROC_TIME_PERCENT,TRANSACT_END_PROC_TIME_PERCENT,UTILS_PROC_TIME_PERCENT from sysibmadm.MON_CONNECTION_SUMMARY"

Get the SQL of the top 10 of current rows read/rows returned:

Db2 "select APPLICATION_HANDLE,substr (ACTIVITY_STATE,1,15) as state,substr (ACTIVITY_TYPE,1,15) as type,ELAPSED_TIME_SEC,TOTAL_CPU_TIME,ROWS_READ,ROWS_RETURNED,QUERY_COST_ESTIMATE,substr (STMT_TEXT,1,15) as stmt from sysibmadm.mon_current_sql order by ROWS_READ/ (ROWS_RETURNED+1) desc fetch first 10 rows only"

Get the heat meter information:

Db2 "SELECT varchar (tabschema,20) as tabschema, varchar (tabname,20) as tabname, sum (rows_read) as total_rows_read, sum (rows_inserted) as total_rows_inserted, sum (rows_updated) as total_rows_updated, sum (rows_deleted) as total_rows_deleted,sum (TABLE_SCANS) as TABLE_SCANS FROM TABLE (MON_GET_TABLE (",-2)) AS t GROUP BY tabschema, tabname ORDER BY TABLE_SCANS DESC fetch first 20 rows only "

-Time Span Monitor SQL

1. Overall level view (at service class, workload level):

View various time-consuming element information in a service class, in milliseconds. First, use this to take a look at the actual time-consuming situation under a service subcategory:

Db2 "SELECT SUM (TOTAL_WAIT_TIME) AS WAIT,SUM (TOTAL_COMPILE_PROC_TIME) AS COMPILE,SUM (TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS IMP_COMPILE,SUM (TOTAL_SECTION_PROC_TIME) AS SECTION,SUM (total_routine_user_code_proc_time) as ROUTINE_USER_CODE,SUM (TOTAL_COMMIT_PROC_TIME) AS COMMIT,SUM (TOTAL_REORG_PROC_TIME) AS REORG,SUM (TOTAL_RUNSTATS_PROC_TIME) AS RUNSTATS,SUM (TOTAL_ROLLBACK_PROC_TIME) AS ROLLBACK SUM (TOTAL_LOAD_PROC_TIME) AS LOAD,SUM (total_connect_request_proc_time) as CONNECT_REQUEST FROM TABLE (MON_GET_SERVICE_SUBCLASS ('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL)) "

Older versions of DB2 use this:

Db2 "SELECT SUM (TOTAL_WAIT_TIME) AS WAIT,SUM (TOTAL_COMPILE_PROC_TIME) AS COMPILE,SUM (TOTAL_IMPLICIT_COMPILE_PROC_TIME) AS IMP_COMPILE,SUM (TOTAL_SECTION_PROC_TIME) AS SECTION,SUM (TOTAL_COMMIT_PROC_TIME) AS COMMIT,SUM (TOTAL_REORG_PROC_TIME) AS REORG,SUM (TOTAL_RUNSTATS_PROC_TIME) AS RUNSTATS,SUM (TOTAL_ROLLBACK_PROC_TIME) AS ROLLBACK SUM (TOTAL_LOAD_PROC_TIME) AS LOAD FROM TABLE (MON_GET_SERVICE_SUBCLASS ('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL)) "

Or use this XML-based:

Db2 "SELECT SUBSTR (T.SERVICEST SUPERCLASSNAMEL NAMEL 19) as SUPERCLASS,SUBSTR (T.SERVICESUBCLASSNAMENAMENE LINGONE 19) as SUBCLASS,T.MEMBER,SUBSTR (U.METRIC_NAME, 1LIN20) AS METRIC_NAME,SUBSTR (U.PARENTICEMETRICNAMELING 20) AS PARENT_NAME,U.TOTAL_TIME_VALUE,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 where U.PARENT_METRIC_NAME is null or U.PARENT.Metronic naming totalized RQSTIME 'order by superclass,subclass,total_time_value desc "

Then, if you find that the waiting time is too long, use the following to see which part of the waiting time is too long:

Db2 "SELECT sum (agent_wait_time) as agent,sum (wlm_queue_time_total) as wlm,sum (lock_wait_time) as lock,sum (log_buffer_wait_time) as log_buffer,sum (log_disk_wait_time) as log_disk,sum (tcpip_recv_wait_time+tcpip_send_wait_time) as tcpip,sum (ipc_recv_wait_time+ipc_send_wait_time) as ipc,sum (fcm_recv_wait_time+fcm_send_wait_time) as fcm Sum (audit_subsystem_wait_time+audit_file_write_wait_time) as audit,sum (diaglog_write_wait_time) as diaglog,sum (pool_write_time) as pool_write,sum (pool_read_time) as pool_read,sum (direct_read_time) as direct_read,sum (direct_write_time) as direct_write,sum (evmon_wait_time) as evmon,sum (total_extended_latch_wait_time) as latch,sum (prefetch_wait_time) as prefetch Sum (comm_exit_wait_time) as comm_exit,sum (ida_send_wait_time+ida_recv_wait_time) as ida,sum (cf_wait_time) as cluster_caching_facility,sum (reclaim_wait_time) as reclaim,sum (spacemappage_reclaim_wait_time) as space_map FROM TABLE (MON_GET_SERVICE_SUBCLASS ('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL)) "

The old version uses this:

Db2 "SELECT sum (agent_wait_time) as agent,sum (wlm_queue_time_total) as wlm,sum (lock_wait_time) as lock,sum (log_buffer_wait_time) as log_buffer,sum (log_disk_wait_time) as log_disk,sum (tcpip_recv_wait_time+tcpip_send_wait_time) as tcpip,sum (ipc_recv_wait_time+ipc_send_wait_time) as ipc,sum (fcm_recv_wait_time+fcm_send_wait_time) as fcm Sum (pool_write_time) as pool_write,sum (pool_read_time) as pool_read,sum (direct_read_time) as direct_read,sum (direct_write_time) as direct_write FROM TABLE (MON_GET_SERVICE_SUBCLASS ('SYSDEFAULTUSERCLASS','SYSDEFAULTSUBCLASS',NULL)) "

Or, with the following more convenient XML-based thing, the TOTAL_WAIT_TIME you see here is the sum of the following total time:

Db2 "SELECT SUBSTR (TFXML.WORKLOAD_NAME, 1,13) AS WORKLOAD_NAME,SUBSTR (WAITS.METRIC_NAME, 1,25) AS METRIC_NAME,WAITS.TOTAL_TIME_VALUE,WAITS.COUNT FROM TABLE (MON_GET_WORKLOAD_DETAILS (NULL,-2)) AS TFXML,TABLE (MON_FORMAT_XML_WAIT_TIMES_BY_ROW (TFXML.DETAILS)) AS WAITS ORDER BY WAITS.TOTAL_TIME_VALUE DESC"

PS:

You can use this to look at a certain part of the PROC time and the total time to see if the WAIT time is normal:

Db2 "SELECT SUBSTR (T.SERVICESUPERCLASS name MON_GET_SERVICE_SUBCLASS_DETAILS 19) AS SUPERCLASS,SUBSTR (T.SERVICESUBCLASSNAMEN AS 19) AS SUBCLASS,T.MEMBER,SUBSTR (COMP.METRIC_NAME,1,20) AS METRIC_NAME,SUBSTR (COMP.PARENT_METRIC_NAME,1,20) name (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 order by (COMP.TOTAL_TIME_VALUE-COMP.PROC_TIME_VALUE) desc "

two。 If you find a problem on it, you have to locate it. General problems occur on SQL. In other words, we need to find which SQL, one of the indicators of this SQL is too large, resulting in the above indicators such as WAIT is too large.

For example, if the POOL_READ_TIME time is too long, then:

Db2 "select SECTION_TYPE,NUM_EXECUTIONS,POOL_READ_TIME,substr (STMT_TEXT,1,32) as stmt from TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)) order by POOL_READ_TIME desc"

For example, if the LOCK_WAIT_TIME is too large, then:

Db2 "select SECTION_TYPE,NUM_EXECUTIONS,LOCK_WAIT_TIME,LOCK_ESCALS,LOCK_ESCALS,DEADLOCKS,LOCK_TIMEOUTS,substr (STMT_TEXT,1,32) as stmt from TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)) order by LOCK_WAIT_TIME desc"

If the DIRECT_READ_TIME is too large, then:

Db2 "select SECTION_TYPE,NUM_EXECUTIONS,direct_read_time,substr (STMT_TEXT,1,32) as stmt from TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)) order by direct_read_time desc fetch first 5 rows only"

For example, if the TOTAL_SECTION_PROC_TIME is too large, then (in addition, you can look at it from a CPU perspective, but this is actually another way to measure it):

Db2 "select SECTION_TYPE,NUM_EXECUTIONS,total_section_time,total_section_sort_time,total_col_time,total_routine_non_sect_time,substr (STMT_TEXT,1,32) as stmt from TABLE (MON_GET_PKG_CACHE_STMT (NULL,-2)) order by total_section_time desc"

In general, WAIT indicators can be found in MON_GET_PKG_CACHE_STMT to find the corresponding indicators, you can check Infocenter.

From the perspective of CPU, it is recommended to use the following view MON_PKG_CACHE_SUMMARY (note that some units are microseconds, some milliseconds):

Db2 "select section_type,TOTAL_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,TOTAL_CPU_TIME,AVG_CPU_TIME,TOTAL_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,TOTAL_IO_WAIT_TIME,AVG_IO_WAIT_TIME,PREP_TIME,ROWS_READ_PER_ROWS_RETURNED,AVG_ACT_WAIT_TIME,AVG_LOCK_ESCALS,AVG_RECLAIM_WAIT_TIME,AVG_SPACEMAPPAGE_RECLAIM_WAIT_TIME,substr (stmt_text,1,16) from SYSIBMADM.MON_PKG_CACHE_SUMMARY"

9.7 use the following:

Db2 "select section_type,TOTAL_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,TOTAL_CPU_TIME,AVG_CPU_TIME,TOTAL_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,TOTAL_IO_WAIT_TIME,AVG_IO_WAIT_TIME,PREP_TIME,ROWS_READ_PER_ROWS_RETURNED,substr (stmt_text,1,16) from SYSIBMADM.MON_PKG_CACHE_SUMMARY order by AVG_STMT_EXEC_TIME desc fetch first 5 rows only"

Or try this, you can look at the problem SQL by average execution time or sorting time:

Db2 "select num_executions,AVERAGE_EXECUTION_TIME_S,STMT_SORTS,SORTS_PER_EXECUTION,substr (stmt_text,1,16) as stmt from SYSIBMADM.TOP_DYNAMIC_SQL"

These are the common dynamic performance views and monitoring table functions of db2 shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.