db2 perf tools ee - ibm · • db2 sql performance analyzer for z/os • cost estimate and explain...

®

IBM Software Group

®

IBM Software Group

© 2008 IBM Corporation

Norbert JenningerIBM Germany, email: [email protected]

DB2 Performance Management Update IIDB2 Query Monitor, DB2 SQL Performance Analyzer, Optimization Service Center/Optimization Expert

April 23, 2008

DB2 Performance Management Update II (DB2 QM, DB2 SQLPA, OSC/OE) © 2008 IBM Corporation2

IBM Software Group

DisclaimersTHE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY.

WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS ORIMPLIED.

IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE.

IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION.

NOTHING CONTAINED IN THIS PRESENTATION IS INTENDED TO, OR SHALL HAVE THE EFFECT OF:

CREATING ANY WARRANTY OR REPRESENTATION FROM IBM (OR ITS AFFILIATES OR ITS OR THEIR SUPPLIERS AND/OR LICENSORS); OR

ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE AGREEMENT GOVERNING THE USE OF IBM SOFTWARE.


IBM Software Group

IBM DB2 Performance Management Tools• Full Range System and Application Monitor

• OMEGAMON XE for DB2 Performance Expert on z/ OS• Detailed reporting and Performance Warehouse• Integrated Real-Time monitor with deep dive zooming capability• Early detection with out of line situation with tak e action capability• Buffer Pool Expert reporting, analysis and simulati on

• Dedicated SQL Monitoring• DB2 Query Monitor for z/OS

• Provides current and historical views of query acti vity throughout DB2 subsystems.• Detection of runaway SQL with capability to execute Actions (Mail, Term..)• Integration with SQL PA, PE Client, Visual Explain

• SQL Performance Analysis (Explain, Cost Estimate, A dvisor)• DB2 SQL Performance Analyzer for z/OS

• Cost estimate and explain under consideration of va rious environments variables and DB2 catalog information

• Easy Explain function with compare plan function• Performance hints• Governor function to prevent execution of expensive SQL

• Visual Explain for DB2 V8 (Free of Charge)• Optimization Service Center (free) / Optimization Expert (fee)


IBM Software Group

Horizontal and vertical Tools Integration

DB2 OMEGAMON DB2SQL PA XE for DB2 PE Query Monitor

Visual Explain Cont rol CenterOptimization Expert

…

OMEGAMONXE for CICS

OMEGAMONXE for IMS

OMEGAMONXE for z/OS

…. => DWL or additionally using OMEGAMON DE (Dashboard Edition)

Information Management Tools

TivoliOMEGAMONMonitors

DB2 Path Checker


IBM Software Group

SQLSQL

DB2 SQL Performance Analyzer for z/OS• Extensive analysis of SQL queries

without executing them. • Cost estimate and explain under

consideration of various environments variables and DB2 catalog information

• Easy Explain function with compare plan function

• Performance Hints for tuning queries• Governor function to prevent execution

of expensive SQL• Integration with other DB2 Tools

� � � � � � � � � � � � � �

• Tune long running online queries or batch jobs

• Resolve database and index design problems and better alternative indexes

• Identify poor use of SQL predicates and clauses

• Prevent runaway queries, before cancellation by QMF and RLF Governors

• Find poor SQL hidden in Production applications

• Eliminate prototyping and stress testing

• Build performance into applications during design

• Allows to do What-If scenarios with catalog changes

� � � � � �

• In Batch as a z/OS job• In TSO as an ISPF dialog• In DB2 applications via a Stored

Procedure call• In QMF as a Governor Intercept

� � � � � � � � � � � � � �


IBM Software Group

What does SQLPA do ?forecasts SQL performance:- Response Times- CPU Times- I/O Countsplus the COST of the query, in terms of: - Charge Back (monetary, in national currency)- QUNITS ™ (query service units)� transforms Optimizer access paths into real world costs

more…It provides an Enhanced EXPLAIN report:- Catalog Statistics- Access Path Information- RI RelationshipsPlus key ADVICE on each SQL statement: - Warnings and Alerts- Guidelines and Recommendations- Performance Notes and Good News � SQL PA teaches users how to write better SQL


IBM Software Group

Review DB2 SQL PA V3 - SummaryAnnouncement: March 7, 2006Announcement: March 7, 2006Announced GA date: March 10, 2006Announced GA date: March 10, 2006

� Completes the integration of EEE

� Extended to include fully interactive TSO ISPF support, seamless navigation

� Common language and input source files

� Storage of access path along with SQL statement for later comparison

� Customer will be able to provide a set of "rules" t hat conform to their installation standards (advisory messages)

� New user-modifiable thresholds for the Advisor

� Set high-water marks, triggers, and other controls for Advisor warning messages

� Path warning flags, e.g. for any tablespace scan, non-matching index scan

� Number of data page to be read and index leaf pages to be read

� Number of tables in a join, number of elements in an IN list

� Selectively switch on and off Advisor messages (ID with or w/o text)

� Usage of PDS for both input and output datasets in TSO/ISPF, including ISPF member list processing (following the ISPF consistency guidelines for field-level help, diagnostics support, and other usability enhancements

SQLSQL


IBM Software Group

Review DB2 SQL PA V3 – Summary …Announcement: March 7, 2006Announcement: March 7, 2006Announced GA date: March 10, 2006Announced GA date: March 10, 2006

� Statistics Collector: filtering by database, tables pace or table with wildcarding

� New FINDALL parameter

� Limit on the number of objects to be collected has increased substantially and commit points are inserted between each table’s catalog update

� Internal enhancements and improvements to many cost algorithms and path lengths (keep the estimates overall costs within reasonable accuracy

� Employing more lower case and reformatting the inpu t SQL in output reports provides better readability

� Temporary statistics and indexes (soon with a PTF)

� Define the “From” DB2 subsystem using parameters to choose the access SQL that performs best (use indexes) against the catalog for that release

� What if analysis

SQLSQL


IBM Software Group

DB2 SQL PA – Estimate result


IBM Software Group

New DB2 SQL PA V3.2 - SummaryAnnouncement: December 4, 2007Announcement: December 4, 2007Announced GA date: December 14, 2007Announced GA date: December 14, 2007

� Install and customize DB2 SQL PA with a fast, simple, automated experience.

SQLSQL


IBM Software Group


� Begin using DB2 SQL PA with a single point of entry into the product.SQLSQL


IBM Software Group


� Access any report for any input source (DBRM, plan, package, etc.).

� Selectively explain at a finer granularity: Drill down to view all the SQL or all the DB2 tables within the given input source (such as plans, packages, and DBRMs). This enables you to explain a single SQL query or all of the SQL for the DB2 table that is selected.

� Access embedded SQL from source programs (PL/I, COBOL, C, Assembler).

SQLSQL

� View summary reports that enable you to access a specific report directly from the summary report. Improved navigation enables quicker access to the information you need most.

� Use the DSN8.DSN8EXP procedure to enable application programmers to see SQL performance against tables for which they do not have authority to run SQL.

� Apply maintenance faster and easier with package versioning.


IBM Software Group

Print report Use the primary command PRon the QLIMIT report to print any of the available reports


IBM Software Group


� Easy access to data you need ... Place the Cursor and press ENTER

SQLSQL


IBM Software Group

Questions to DB2 SQL PA?

Next DB2 Query Monitor


IBM Software Group

DB2 Query Monitor for z/OS

• Low overhead SQL statement monitor.• Identify SQL requests which are

consuming excessive resources and may be preventing critical requests from completing on schedule.

• Triggers actions crossing thresholds• Proactively manage DB2 resources.• React quickly and effectively to DB2

problems like inefficient SQL or inadequate object structures.

• Determine which tables and indexes are actually being used.

• DB2 V8 support

� � � � � � � � � � � � � �

• Monitoring Agent• Query Monitor (QM) Subsystem• Consolidation and Analysis

Engine (CAE) Agent• CAE Server• ISPF Client• GUI Client

• Summary of DB2 activity• SQL statements• DB2 object activity• Negative SQL codes• DB2 Commands• Current SQL activity• Exceptions• Alerts

� � � � � � � � � � � � �

� � � � � � � � � � � � � � � � �

z/OS MV01

ISPF

WS

CAE Client

CAE Server

DB2A

Monitoring Agent

Unix System Services

CAE Agent

Backstore data sets

z/OS MV02

DB2B

Unix System Services

CAE Agent

ISPF

WS

CAE Client

Query Monitor Subsystem

Monitoring Agent

Backstore data sets

Query Monitor Subsystem


IBM Software Group

DB2 QM: Overview

• Collect, summarize and display • Dynamic and static SQL resource consumption down

to the individual SQL text statement• DB2 object access statistics down to the individual

table and index• View current activity• View historical statistics

• Load into DB2 tables for further analysis• Collect / display negative SQL codes

• Exclude specified negative SQL codes • Limit number of negative SQL codes captured• Available in history

• Collect / display DB2 command activity

V1.1 V2.1 V2.2 V2.312/2000 6/2004 12/2005 3/2008


IBM Software Group

DB2 QM: Overview

• Monitoring Profiles• Customizable• Can be dynamically activated / deactivated• Exclude workloads from data collection• Define exception thresholds

• Collect information relating to exceptional SQL related events• Includes host variables, SQL text, DB2 objects, BP statistics

• Perform real time knowledge based analysis of exceptional events

• Alert notification and curative actions on exceptional events• ISPF and GUI interfaces; both are customizable• Multi-column sorting• Supports DB2 data sharing


IBM Software Group

DB2 QM: Types of Information

• SUMMARY ACTIVITY• CURRENT ACTIVITY• EXCEPTIONS

• ALERTS• SQL CODE• DB2 COMMANDS

SUMMARY

1 entry / statement

No host variables

EXCEPTION

1 entry / exec statement

Host variables

ACTIVE

Real time


IBM Software Group

DB2 QM: Monitoring Profile

Include / Exclude

Identification

Exception

Alerts

Gather host variables ?

ISPF

GUI

Name Workload


IBM Software Group

ALERTS vs. EXCEPTIONS• Events • Both are dependent on thresholds.• Thresholds are defined in the Monitoring Profile by

Workload• EXCEPTIONs do not trigger notifications – only ALERTS

can do that• EXCEPTIONs capture host variables (if specified)• ALERTs do not contain host variables• Good rule: Always make an ALERT an EXCEPTION

• ALERT information goes to the CAE Server (not the Back Store)

• If the CAE Server goes down, the ALERT information will be lost


IBM Software Group



IBM Software Group

DB2 QM CAE GUI window

MENU BAR

ACTIVITY BROWSER

ALERT BROWSER

TREE TABLE DETAIL

ACTIVITY BROWSER

PANELS


IBM Software Group

DB2 QM Alert BrowserFREEZE

Select fields to display FILTERS

MESSAGE BOARD

Show message details

Acknowledge / Unacknowledged

Clear selected messages

Save messages to CSV file

Can have multiple message boards


IBM Software Group

What’s new in Version 2.3 of DB2 QM

• Coexistence with DB2 Audit Management Expert• “Shared” collector

• Summary caching• Periodically writes unreferenced summary data to DASD

prior to interval expiration to reduce memory footprint• Reliability and serviceability

• New load module coordination in ISPF• 64bit memory exploitation• Shared memory objects instead of dataspace

Announcement: March 4, 2008Announcement: March 4, 2008Announced GA date: March 14, 2008Announced GA date: March 14, 2008


IBM Software Group


• Usability improvements• Allow recursive navigation in ISPF summaries

• Includes “un-stack to prior level” option• Navigate between operational and structural summaries• New KEYMAP command

• Use numbers for ISPF commands instead of letters• Better conform to ISPF standards

• User specifiable ISPF options• Use old style commands• Dump on first error• Issue SORT warning



IBM Software Group


• Enhanced diagnostics• Multi-tier recovery architecture

• Bugs happen, production outages shouldn’t• Hierarchical storage manager• Enhanced error logging/reporting• Automatic loop detection

• ISPF session log• EXPORTLOG command exports a log of the ISPF session• Can auto-initiate if necessary



IBM Software Group

Questions to DB2 QM?

Next OSC / OE


IBM Software Group

Optimization Service Center / Optimization Expert

• OSC is a free of charge tool as part of the DB2 Accessories Suite for DB2 9

• Available via Web download• Visual Explain• Visual Plan Hint• Workload Control center• SQL statement and workload collection• Import from cache, catalog, QMF, file• Shows reports

� � � � � � � � � �

• OE uses OSC• Supports tuning of SQL statements • Provides expert advices

• Statistics Advisor• Query Advisor• Access path advisor• Index Advisor

1. Select statement(s) to be analyzed

� Analyze a single query making problems

� Check a whole workload (e.g. if new application is rolled out)

2. Analyze statement(s)� Run advisors to get tuning

recommendations *� Perform a manual analysis

using expert tools

3. Perform changes� Depends on necessary fix

�

Run suggested DDL or utilities or save them for later execution

�

Change SQL in application

�

Overwrite access path by hint

� � � � � � � � � � � � � � � �

� � � � � � � � � � � � � � � � � � � � � � �


IBM Software Group



IBM Software Group


Select SQL query ...

Run Analysis ...


IBM Software Group


Check out advisor hintswhile pressing “Details”

For example,run anyproposedutility


IBM Software Group


Access Plan Graph shows you the graph but let you also navigate through the catalog.


IBM Software Group

What’s new in Version 1.2 of DB2 OE

• V1.2 introduces 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.



IBM Software Group

References - Bibliography

Publication title number------------------------------------------------------ -----------DB2 Query Monitor V2.3

User’s Guide SC18-9202Program Directory GI10-8587

DB2 SQL Performance Analyzer V3.2User’s Guide SC18-9824Program Directory GI10-8707

DB2 Optimization Expert V1.2Getting Started GC19-1144Program Directory GI10-8755Redbook: New Tools for Query Optimization SG24-7421

References - InternetDM Tools Library (complete library of all tools, including additional updates)

http://www-306.ibm.com/software/data/db2imstools/db2tools-library.html


IBM Software Group

Norbert JenningerIBM Germany

e-mail: [email protected]

Thank you !

db2 perf tools ee - ibm · • db2 sql performance analyzer for z/os • cost estimate and explain...

Documents