This course gives a brief overview of DB2 SQL performance considerations, including establishing goals for Application SQL performance.

We explore the capabilities of Visual Explain that are beyond those of the Explain Plan SQL statement - Filter Factor analysis, Stage1 and Stage2 analysis, number of rows returned.

The Visual Explain performance analysis capabilities are used to explore several SQL statements to illustrate the major DB2 data accesses - Table Space scans, Non-Matching and Matching Index scans, Index only access, and understanding sort impact.

Extensive, guided workshop sessions give additional insights into performance analysis using Visual Explain. One workshop improves performance of SQL statements via rewriting them.

LEARNING OBJECTIVES

As a result of this class, the attendee will be able to

• Examine Application performance goals
• Explain the additional capabilities of Visual Explain that are beyond those of the Explain Plan SQL statement - Filter Factor analysis, Stage1 and Stage2 analysis, number of rows returned.
• Use Visual Explain to discover DB2 SQL Access Paths
• Use Visual Explain to discover DB2 SQL Filter Factors
• Use Visual Explain to discover whether predicates are Stage 1 (early) or Stage 2 (late)
• Use Visual Explain to discover if DB2 SQL access is Table Space (TS) scan or Matching Index (MIS) scan or Non-Matching Index (NMIS) access or Index Only access
• Verify the validity of the number of rows returned Visual Explain
• Develop a list of Tuning Tips for DB2 SQL Developers