Using Explain products to analyze and improve SQL statements

This topic explains how to use Explain products to analyze and improve SQL statements.

To determine and improve the access path of dynamic and static SQL statements that contain a SELECT, UPDATE, INSERT, DELETE, or VALUES clause, you can use one of the following Explain products:

• IBM® DB2® Query Workload Tuner for z/OS® Version 5.1
• IBM InfoSphere® Optim Query Workload Tuner Version 4.1 or later (including Data Studio) (Db2 V12 requires Data Studio 4.1.3 or later)
• IBM Data Server Manager V2.1.5

You can use Explain products to view the access path of a dynamic or static SQL statement when:

• Monitoring an application or thread
• Viewing Statistics on the SQL statements stored in the dynamic SQL statement cache

If you want to use Query Workload Tuner, you must first define the local database alias of the currently monitored DB2 subsystem:

1. Open the following wizard or window:
   • The New DB2 System wizard as described in Defining a DB2 subsystem
   • The DB2 System Properties window as described in Changing the properties of a DB2 subsystem
2. Open the DB2 Subsystem page.
3. In the Local DB Alias field, type the local database alias of the DB2 subsystem.