Tuning with indexes
You will likely get requests to evaluate SQL queries and suggest indexes for best performance. Let's see together how it's done. You can use the GUI for this case, so we'll be doing it on the command line.
We will use DB2's db2advis utility. There are many ways you can use it. In our case, we have SQL commands with recommendations for indexes.
Getting ready
Now, before you can perform any EXPLAIN PLAN or performance analysis, you need to have fresh statistics on tables and indexes or reasonably recent ones; otherwise, the results may not correspond to your expectations.
How to do it...
If you're not connecting as instance owner, you will need a user with create table authority to run the EXPLAIN.DDL utility.
As instance owner, grant access to the database to the user who will perform SQL evaluations. That user should have profile values set and can execute DB2 from the shell or Windows command line:
db2 connect to uatdb db2 grant createtab, connect on database to user rpellet...
