(DBA) Review SQL before production use
The following trace gives the DBA a simple way to check out the performance characteristics of any new plan:
Option | Use |
|---|---|
TYPE=D,SQL,SCAN | Medium overhead |
START= | Leave blank to start immediately |
STOP= | Leave blank to stop manually |
WRAP=YES | Keep the latest occurrences in the buffer |
DB2PLAN=XYZAB | Select the new plan |
STORAGE=1000K | Trace display buffer space is in BBI-SS PAS extended private area |
LOGTRAC=Y | Log to a dynamically allocated data set |
By tracing scan information integrated with the SQL statements, critical performance indicators can be easily identified, such as sorting, index usage, scan patterns, work (DSNDB07) scans, and so on. Events and resources used can be seen directly as they actually happened. Bad SQL statements can be identified and fixed before they are put into production.
For large plans with many SQL statements, the SQL summary section of STRAC can be selected and sorted by resource usage to easily detect the worst offenders. From this section, it is possible to select a statement to view row statistics, or a program to view only the detail events for that single program within a plan.
Table space usage can be analyzed for the whole program by selecting the database summary section of STRAC, where you can view data, work (sort), and index usage of sort by any column to identify high usage. TSTAT offers the same display but for all traced threads.
To see I/O usage by table space, also include IO in the trace TYPE options and then browse the STRAC or TSTAT Database Lock and I/O Summary section. Use the DTRAC detail event display to correlate scan and I/O data to specific SQL statements.
An additional valuable analysis tool can be gained by also tracing the BIND of the plan. Specify DB2PLAN=XYZAB, BINDCT++, and start the trace before the BIND. This information provides a record of the text of each static SQL statement and its related EXPLAIN data. It is not necessary to specify the EXPLAIN option on the BIND. Dynamic SQL text and EXPLAIN data are included in the plan execution trace, giving a complete picture.
Related topic