If there is any operation that is running slowly in your
environment and some SQL statement might be causing this performance
issue, you can look for missing indexes on DB2®.
Procedure
- Capture the DB2 snapshot:
- Turn on the DB2 monitor switches: db2 update monitor switches using bufferpool
on LOCK on SORT on STATEMENT on TABLE on UOW on.
- Run the operation whose performance is slow.
- Get the DB snapshot by running the command: db2 get snapshot for all on FRSDB > /location/snapshot.txt.
- Analyze the DB2 snapshot
that you got:
- You can use the IBM Performance Analyst tool to analyze this snapshot. Download the tool from IBM® Smarter
Performance Analysis Suite.
- Open the tool and select .
- Select the file that contains the snapshot and open
it. The slow SQL statements show up in red.
- Optional: You can find the most commonly
used SQL statements by selecting .
- Get the access plan of the slow SQL statement:
- Connect to the database in use by running:
db2 connect to db_name
cd install_directory\SQLLIB\MISC;
install_directory\SQLLIB\MISC\>#db2 -tvf EXPLAIN.DDL;
- Set the explain mode by running: db2 set current
explain mode explain DB20000I The SQL command completed successfully.
- Run the slow SQL.
- Get the access plan to a file by running: db2exfmt
-d db_name -g db2inst1 -w -1 -n % -s % -# 0 -o access_plan_file
- Add an index that is based on the access plan by running
this command: db2 CREATE INDEX db2inst1.index_name ON db2inst1.table_name (col_name ASC, col_name ASC, col_name ASC) ALLOW REVERSE SCANS.
- Verify the performance results after you added this index
by running steps 1 to 3 again.
What to do next
If the index you added does not help getting improved
performance results, check whether you added an appropriate index.