Using EXPLAIN PLAN

SQL tuning should be done as early as possible in the development process. Often SQLs will be developed on small development databases and will probably make it to the QA database, and even into production, with acceptable performance. When the database grows, it's not unusual to find out the same query keeps getting slower and slower. This is where you will have to check access plans.

Getting ready

Now, before you can perform any explain or performance analysis, we need fresh statistics or reasonably recent ones, on tables and indexes. Otherwise, the results may not correspond with your expectations.

How to do it...

1. User with create table privileges: If you're not connecting as instance owner, you will need a user with create table authority to run the EXPLAIN.DDL utility, as the instance owner grants database access 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...