DB2 tuning SQL Execution Analysis

 

DB2 tuning SQL Execution Analysis

I have always had a misunderstanding that it is the same to not creating indexes for all the fields in the table. Therefore, when the data volume reaches 1 million in a practical application, the retrieval speed is obviously slow, and the CPU usage during query execution is very high, which affects other jobs and leads to chain reactions.
After discussing with some friends, I understand that although the index is created for all the fields in the table, it seems that the whole table is queried, And the whole table is queried if no index is created. However, the query efficiency is determined by the data storage method and the query algorithm under the storage method. A friend pointed out that the storage and query of indexes are in B-tree mode, table data is not stored. as you can imagine, table data is stored in B-tree mode, which consumes a lot in general access operations.
This poor SQL statement is found that the DBA has ranked among the top five in terms of execution time in a certain period of time. it was criticized by DB2. the DBA told me to use db2expln (db2exfmt) to check whether the full table scan is displayed in the SQL statement.
Learning...

DB2 command for optimization analysis
# Capture the snapshot information of DB2
DB2 get snapshot for dynamic SQL on tablename
# DB2 event monitoring DB2
(1) DB2 create event monitor statev for tables, statements, transactions, deadlocks write to file 'C: \ logs \ event' maxfiles 10 maxfilesize 5
(2) DB2 set event monitor statev state 1
(3) run the SQL statements
(4) DB2 flush event monitor statev
(5) db2evmon databasename statev> output.txt
(6) DB2 set event monitor statev state 0
# DB2 SQL Execution Analysis
DB2 set current explain mode [No | Yes | explain]
Db2expln-database databasename-statement "select * from example"-T-z @-G> C: \ ex. Log
# DB2 SQL detailed analysis
(1) execute \ sqllib \ MISC \ explain. DDL
DB2-tvf explain. DDL
(2) set the command interpretation mode
DB2 set current explain mode explain
(3) execute the SQL to be monitored
(4) Disable resolution naming
DB2 set current explain mode No
(5) Generate information files
Db2exfmt-D databasename-G Tic-w-1-N %-S %-#0-o C: \ exfmt_runstats.out