Finding missing indexes

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®.

1. Capture the DB2 snapshot:
   1. Turn on the DB2 monitor switches: db2 update monitor switches using bufferpool on LOCK on SORT on STATEMENT on TABLE on UOW on.
   2. Run the operation whose performance is slow.
   3. Get the DB snapshot by running the command: db2 get snapshot for all on FRSDB > /location/snapshot.txt.
2. Analyze the DB2 snapshot that you got:
   1. You can use the IBM Performance Analyst tool to analyze this snapshot. Download the tool from IBM® Smarter Performance Analysis Suite.
   2. Open the tool and select File > Open > DB2::Snapshot.
   3. Select the file that contains the snapshot and open it. The slow SQL statements show up in red.
   4. Optional: You can find the most commonly used SQL statements by selecting Profile > most-commonly-used.
3. Get the access plan of the slow SQL statement:
   1. 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;

   2. Set the explain mode by running: db2 set current explain mode explain DB20000I The SQL command completed successfully.
   3. Run the slow SQL.
   4. Get the access plan to a file by running: db2exfmt -d db_name -g db2inst1 -w -1 -n % -s % -# 0 -o access_plan_file
4. 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.
5. Verify the performance results after you added this index by running steps 1 to 3 again.