DB2 tuning monitor for slow SQL execution

In the DB2 tuning process, you often want to be able to get SQL that is slow to execute in your current production environment, which you can then fine tune for specific slow SQL. The following scripts can be exploited, or are cumbersome to operate, and need to be run manually.

DB2 Connect to Tablename;db2-x "Select Current Timestamp | | ' | ' | | Stmt_text | | ' | ' | | member | | ' | ' | | Num_exec_with_metrics | | ' | ' | | Total_act_time | | ' | ' | | Total_act_wait_time | | ' | ' | | total_cpu_time| | ' | ' | | Pool_read_time | | ' | ' | | Lock_wait_time | | ' | ' | | Total_section_sort_time | | ' | ' | | Total_section_sorts | | ' | ' | | Rows_read | | ' | ' | | rows_returned | | ' | ' | | pool_data_l_reads | | ' | ' | | pool_temp_data_l_reads| | ' | ' | | pool_index_l_reads | | ' | ' | | pool_temp_index_l_reads | | ' | ' | | pool_data_p_reads | | ' | ' | | pool_temp_data_p_reads | | ' | ' | | pool_index_p_reads | | ' | ' | | pool_temp_index_p_reads | | ' | ' | | sort_overflows | | ' | ' | | Total_section_time | | ' | ' | | Total_section_proc_time | | ' | ' | | Fcm_recv_wait_time | | ' | ' | | Fcm_send_wait_time from (SELECT * from TABLE (mon_get_pkg_cache_stmt (null, NULL, ' <modified_within>5</modifi Ed_within> ',-2)) where Num_exec_with_metrics > 0 and total_act_wait_time>) where total_act_wait_time/(N um_exec_with_metrics*1.0) > ">> sqllist.txtdb2 conneCT reset; 

Script description

This script is a script that crawls slow SQL and needs to be executed every 5 minutes on the database server.

1. Each execution of the script fetches an SQL statement with an average execution time of more than 5 seconds in the last 5 minutes
2. Fetched SQL is output to file Sqllist.txt, monitor the size of the file, avoid disk space-related problems caused by too large files
3. Because the script runs every time it executes a SQL statement that was executed in the last 5 minutes, it is done during daylight hours, and W unattended time shuts down the script execution.

Parameter description

Specific monitoring content, can be queried through the IBM official website

such as Total_act_time

DB2 tuning monitor for slow SQL execution