Candle-lite DB2 manual Using Omegamon II and !DB/EXPLAIN Complementary Functions, Assumptions

Using OMEGAMON II and !DB/EXPLAIN Complementary Functions

Using OMEGAMON II and !DB/EXPLAIN Complementary Functions

This section describes the functions of !DB/EXPLAIN and OMEGAMON II for DB2 that work together to help improve DB2 performance, and suggests ways you can use them.

If you use Version 6 or Version 7 of DB2, refer to the !DB/Tools for DB2 Installation and Customization Guide, Version 510, for information on installing !DB/EXPLAIN.

Assumptions

The activities in this chapter presume the following:

■You have started a trace for the application you want to review and the results of that trace are available. Application trace results can be kept in memory, or you can write them to datasets for long-term retention.

Review your Application Trace Facility (ATF) information to ensure that

–It contains information about the plan or plans you want to study.

–It was run at a time that reflects the most current version of the plan or plans you want to study.

■By using the historical reporting capabilities of OMEGAMON II for DB2 from !DB/EXPLAIN, you can determine the names of the VSAM datasets or DB2 tables that contain the stored application trace data for OMEGAMON II. You can use the OPTIONS command from the OMEGAMON II Historical Reporter to specify the

–input datasets

–DB2 table names to use for reporting

–report filtering selection criteria.

When to use the complementary functions

Using !DB/EXPLAIN together with OMEGAMON II for DB2 assists you in resolving problems and making decisions related to the performance of an application.

Here are some example situations.

■You are running OMEGAMON II for DB2 and observe performance problems in an application. You want to analyze the SQL statements the application contains to determine what the problem may be.

■You are developing an application and want to

–obtain predictive access path information from !DB/EXPLAIN about the SQL contained in the application

–obtain cost information from !DB/EXPLAIN about the SQL contained in the application

–verify the predictive information from !DB/EXPLAIN with the real-time execution information provided by the OMEGAMON II Application Trace Facility

■You are testing your application by executing an SQL call within !DB/EXPLAIN and want to review online performance information.

Using OMEGAMON II for DB2 with !DB/EXPLAIN 91