News on DB2 Optimization Expert for z/OS V1.2 and DB2 Optimization Service Center Version 1.1

Abstract

On March 4, IBM announced V1.2 of DB2 Optimization Expert for z/OS.

In this technote we summarize the main enhancements provided in this release. DB2 for z/OS Version 8 support is also included among these enhancements.
For complete details, see:

http://www.ibm.com/software/data/db2imstools/db2tools/opti-expert-zos/

and

- IBM United States Software Announcement 208-044 March 4, 2008
- IBM DB2 Optimization Expert for z/OS Liicense Information, GC19-1131
- IBM DB2 Optimization Expert for z/OS Program Directory, GI10-8755
- IBM DB2 Optimization Expert for z/OS Getting Started, GC19-1144

We also mention the current maintenance level of DB2 Optimization Service Center Version 1.1.

For related information about this topic, refer to the following IBM Redbooks publication:
IBM DB2 9 for z/OS: New Tools for Query Optimization, SG24-7421-00

Contents


DB2 Optimization Expert for z/OS, V1.2

DB2 Optimization Expert for z/OS, V1.2 (program number 5655-S19) supports monitoring and tuning of SQL statements that run as a single statement or as part of a workload on your DB2 for z/OS subsystem, V8 (5625-DB2) and V9 (5635-DB2).

DB2 Optimization Expert for z/OS V1.2 introduces the support for DB2 for z/OS V8 and provides the ability to capture a workload across a data-sharing group. Many rules have been added to the advisors, and new user-specified "what-if" and table-based priority options have been added to the Index Advisor. Users can now extract queries directly from SQL Stored Procedures. Both experienced DBAs and application programmers will find DB2 Optimization Expert V1.2 easy to use and customize with powerful new filters, views, and search options.

DB2 Optimization Expert for z/OS, V1.2 support for DB2 for z/OS V8 includes DB2 for z/OS V8 Compatibility Mode (CM) and DB2 for z/OS V8 New Function Mode (NFM).

The enhancements include:

A. Extended ability to capture queries:
A.1 Retrieve queries from plans or packages based on tables, views and indexes dependencies
    To invoke this function:
    1. Click the View Query tab to get to View Query panel.
    2. Click Query Source and select Catalog Plan or Packages.
    3. Define a new view by clicking the View icon to see the pull down menu, select New to define a new view.
    4. Follow the dialog wizard. Under the heading "Specify plan or package dependency filter," click Add Object Filter.
    5. Enter the Object Qualifier, Object Name, and Object Type. Users can specify 0 or more entries.
A.2 New predefined options for viewing list of queries sorted in descending order on average CPU or average elapsed time (AVG_CPU_DESC and AVG_ELAP_DESC)
    To invoke this function:
    1. Click the View Query tab to get to the View Query panel,
    2. Click Query Source and select Statement cache.
    3. Under View name, select either AVG_CPU_DESC or AVG_ELAP_DESC.
A.3 New dynamic statement cache search options
    Users can search the snapshot taken from dynamic statement cache for statements containing specified keywords.
    To invoke this function:
    1. Click the View Query tab to get to the View Query panel.
    2. Click Query Source and select Statement cache.
    3. Under View name, select any one of the provided views as appropriate. A snapshot of the dynamic statement cache displays.
    4. Locate the Find SQL Text icon and click it.
    5. Enter the search criteria in the pop-up panel.
A.4 Identify the data-sharing member where the dynamic statement is taken from.
    In a data-sharing environment, it is important to identify from which member the dynamic statement cache snapshot is taken.
    The report simply need to have a group member column to identify the origin of the snapshot.
    To enable this function:
    1. Click the View Query tab to get to the View Query panel.
    2. Click Query Source and select Statement cache.
    3. Under View name, select any one of the provided views as appropriate. A snapshot of the dynamic statement cache displays.
    4. Locate the Customize icon and click to show the pull down menu. Select Column.
    5. Follow the wizard dialog box to add the GROUP_MEMBER column to the view.
A.5 A new stored procedure to extract queries from dynamic statement cache to support users without SYSADM authority.
    The name of the stored procedure is SYSPROC.OSC_RUNSQL

B. Extended ability to capture, export, and import workloads:
B.1 Improved performance of Workload Control Center.
B.2 Users can capture workloads from the statement cache and create monitor profiles across an entire data-sharing group.
    To invoke this function:
    1. Click the View Workload tab to get to the Workload List panel.
    2. Locate the New Workload icon under the Workload List heading.
    3. Define a new workload. Click New Workload.
    4. Select Statement cache, and then select Immediate capture or Scheduled capture.
    5. Select Filter or Advanced.
    6. Follow the wizard dialog box and specify GROUP MEMBER of the data-sharing group.
B.3 Users can export a workload from one DB2 subsystem and import to another DB2 subsystem.
    To invoke this function:
    1. Click the View Workload tab to get to the Workload List panel.
    2. Locate the New Workload icon under the Workload List heading.
    3. To export a workload, select or highlight one of the existing workloads. Then, click New Workload, and select Export.
    4. Follow the wizard dialog box.
    5. To import a workload, click New Workload, and select Import.
    6. Follow the wizard dialog box.
B.4 Users can customize filters for object, access path, or cost-based searches.
    To invoke this function:
    1. Select a Workload Project tab to get to Workload Statements panel.
    2. Locate and click the View icon to show the pull down menu, select New.
    3. Follow the dialog wizard to define a new view.
      When defining the new view, specify the filter for:
      • Objects: tables, indexes, databases, and table spaces
      • Access Path elements:
        • Types of prefetch: list, dynamic, and sequential prefetch
        • Types of access: relational scan, non-matching index access, multi-index access, single index access, and so forth
        • Types of join methods: nested loop join, sort-merge join, hybrid join
        • Types of joins: inner join, outer join, star join
        • Types of sorts: sort composite table for group by, for order by, and so forth
        • Types of parallelism: IO, CPU, SYSPLEX
      • Cost based searches:
        • Accumulated CPU
        • Accumulated I/O
        • Average CPU time
        • Average elapsed time
C. Service Workload:
    A new component, called Service Workload, is provided in conjunction to Service SQL to collects DDL statements, statistics, and EXPLAIN information for query workloads. Service Workload is similar to Service SQL, except that it collects workload information for further analysis. This function can be invoked from any workload project, select any workload project tab to go to Workload Project main panel. From this panel, locate and click the Gather Service Workload icon.

D. Powerful new Index Advisor capabilities and options:
D.1 Users can specify their own virtual indexes to perform a "what-if" analysis for single queries
D.2 The workload-based index advisor evaluates all existing indexes and indicates whether each index is chosen by optimizer
D.3 Users can specify table priorities to indicate the importance of different tables.
    The index generation policy is applied according to the value of table priority. Therefore, a more aggressive index generation policy can be applied to statements that reference more important tables.
D.4 Users can configure benefit thresholds for query weight groups, suppress the index recommendations for less important queries, and see index recommendations for only the more important queries.
D.5 Users can disable index recommendation on user-specified tables.
D.6 Show runtime information (Elapsed time, CPU time, and so on) for the related statements of each recommended index.
D.7 Users can drill down into the related queries for each recommended index by launching query-based advisors and tools.
D.8 The relative performance improvement in percentage is now shown for each recommended index and each related statement.

E. New Statistics Advisor report, rules, and options:
E.1 New options are added in RUNSTATS recommendations, including SORTDEVT, SORTNUM, and REPORT.
E.2 A report similar to the query report can be generated for the statistics information for queries in a workload, including tables, indexes, columns, and column groups.
E.3 Users can search and set filters on the workload statistics explanations.
E.4 Conflicts in the conflict report are grouped by related tables.
E.5 A new conflict rule is added. When the maximum frequency of a column or a column group is less than the reciprocal of its cardinality, it is considered as a conflict and recommended to recollect statistics.
E.6 New parameters are provided for controlling the percentage of columns or column groups on which to collect statistics.
E.7 Users can check the updated statistics report of the workload after the RUNSTATS recommendations are executed for any remaining problematic statistics, such as missing and conflicting statistics.

F. New Query Advisor rules:
F.1 Cartesian join analysis rule provides warnings and rewrite recommendations when tables have no join predicates to other tables in the query.
F.2 Join key analysis rule provides warnings and query rewrite recommendations when an inner join has a data type mismatch or nullability mismatch on the joining columns.
F.3 Ambiguous cursor analysis rule provides warnings and query rewrite recommendations when an ambiguous cursor is opened on a read-only or update-only query.
F.4 Outer-join to parent table analysis rule provides warnings and query rewrite recommendations when an outer join occurs with a child table as the outer table and the parent table as the inner table of join, which matches an inner join.
F.5 NOT IN predicate analysis rule provides warnings and recommendations when there is a NOT IN (non-correlated sub query) in the SQL while sparse index cannot be applied.
F.6 Users can specify the severity levels for the rules.
F.7 Show runtime information (Elapsed time, CPU time, and so on) for each query. Execution count, accumulated elapsed time, accumulated CPU time, average elapsed time, and average CPU time are included in the Query Advisor's report.
F.8 Users can drill down into each query by launching query-based advisors and tools.

G. New Access Path Advisor rules:
G.1 Cartesian join rule: A form of nested loop join without join predicates between the two tables.
G.2 Relational scan rule: Whether the access method for a table is a relational scan. A relational scan is generally not the best access method if the number of qualified rows from the table is small while the table is large, or the relational scan is used to access the inner table in a nested loop join.
G.3 Non-matching index scan rule: No matching columns are in the index. Hence, all the index keys must be examined.
G.4 RID pool usage rule: Used by multiple index scan and list prefetch.
G.5 Individual sort rule: Whether a sort operation is used. Sort can be used for join processing, group by, order by, duplicate removal, and subquery processing. You can design queries and indexes to avoid some sorts.


DB2 Optimization Expert for z/OS, V1.2 (5655-S19) requires:
  • One of z/OS V1.7 (5694-A01) or later, or z/OS.e V1.7 (5655-G52) or later
  • DB2 for z/OS V8 (5625-DB2)/PDO level 0801 (Applicable maintenance: UK32046, UK33731, PK46687, UK32263, PK58941, PK61475, PK56284)
  • DB2 9 for z/OS (5635-DB2)/PDO level 0801 (Applicable maintenance: UK32047, UK32262, PK56524, PK59183, PK60931, UK30376, UK26456)
  • DB2 Connect, Application Server, Enterprise, Personal or Unlimited edition,
    • For Version 9.1: Fix Pack 4
    • For Version 8.1: Fix Pack 15
    • For Version 8.2: Fix Pack 8,
    • or later
DB2 Optimization Expert requires a full DB2 Connect license.
Notes: Profile Monitoring is only available in DB2 V9 NFM.
Dynamic Statement Cache is not available in DB2 V8 CM.

DB2 Optimization Service Center Version 1.1
This tool is included in the Accessories Suite of DB2 9 for z/OS at no additional charge and it is also available from the DB2 for z/OS download site:
http://www.ibm.com/software/data/db2/zos/downloads/osc.html
  • Fix Pack 3 (OSC V1.1 FP3) supports DB2 for z/OS V8 NFM and it has been available for some time.
  • Fix Pack 4 (OSC V1.1 FP4) supports DB2 for z/OS V8 CM and it is planned to be made available end of March 2008.
Notes: Full coexistence/fallback support requires Optimization Expert V1.2 Fix Pack 1, available end of March 2008.
PTFs for APAR PK58941 for DB2 V8 and V9 are required to enable this feature.
The PTFs will be available in conjunction with Fix Pack 1 OE V1.2 Fix Pack 1 and OSC 1.1 Fix Pack 4


Will Optimization Expert (OE) V1.2 GA new functions work in other DB2 for z/OS releases and are they also included in Optimization Service Center (OSC) V1.1 ?
OE V1.2 GA New Functions
V9 NFM
V8 NFM
V8 CM
Except functions that are not provided in V8:
  • Profile Monitor
  • Dynamic Statement Caching (OK in NFM)
in OE V1.2 GA
YES
YES
YES
in OSC V1.1
Except functions that are not originally available in OSC:
  • Index Advisor (Single Query and Workload)
  • Query Advisor (Single Query and Workload)
  • Access Path Advisor
in OSC 1.1 FP 3
NO
NO
NO
in OSC 1.1 FP 4
(planned to be shipped end of March 2008)
YES
YES
YES


Installation notes:
When installing Optimization Expert V1.2, we recommend to uninstall the previous version prior to installing the new product..
Similar recommendation applies to Optimization Service Center: uninstall Optimization Service center Fix Pack 3 prior to installing Fix Pack 4.

Special Notices

This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment. publib-b.boulder.ibm.com

Profile

Publish Date
07 March 2008

Last Update
10 March 2008


Rating: Not yet rated


Author(s)

IBM Form Number
TIPS0673