EZDB2 for Dynamic and Static SQL Workload Analysis and Optimization

1
EZ-DB2 for Dynamic and Static SQL Workload
Analysis and Optimization
Helios Matheson Software solutions
Welcome to the EZ-DB2 Webinar
Helios Matheson North America 978-203-9128 HMsof
tware_at_hmna.com
2
Overview
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

3
DB2 Performance Tuning Overview
Helios Matheson Software solutions

• Environment
• Agent Systems (TSO, CICS, IMS)
• Network Configuration
• Operating System Configuration
• DB2 Subsystem
• Pools (Buffer, EDM, SORT, RID)
• DSNZPARM, Locking, IRLM, DDF
• Database
• Database and Index Organization
• Index Design
• Database Design (normalization/denormalization)
• Application
• SQL Statements
• Process Logic Code

4
DB2 Performance Tuning ROI
Helios Matheson Software solutions
5
SQL Performance Tuning
Helios Matheson Software solutions

• Optimize the SQL Text
• Optimize the Program Logic
• Optimize the Index Design
• Optimize the DB2 Pools (Buffers, etc.)

6
Overview
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

7
The EZ-DB2 Family - What Are We About
Helios Matheson Software solutions

• SQL Performance This Presentation!
• The Impact of SQL Statements (Dynamic and/or
  Static) on DB2 Performance
• Index Analysis and Optimization Email us for
  Presentation!
• How Efficient is the Existing Index Design when
  Processing the Current Workload
• What is the Optimum Index Design for the Current
  Workload
• Access Path Change Management Email us for
  Presentation!
• What is the Impact on Access Path when moving an
  Application from Test to QA to Production
• What is the Impact on Production Access Path when
  a Change is made to the Production Environment
• Catalog Statistics Management Email us for
  Presentation!
• Copy Production Stats to QA and Test and Changes
  as needed
• Save/Change/Restore Production Stats

8
EZ-DB2 SQL Warehouse
Helios Matheson Software solutions
9
Overview
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

10
DB2 as a Database Server in the Distributed
Environment
Helios Matheson Software solutions
In the Distributed World, Dynamic SQL is the norm
when accessing Mainframe DB2 data
11
The Dynamic SQL and Distributed Environment
Challenge
Helios Matheson Software solutions

• Capturing and Consolidating
• Extremely difficult!
• Tuning
• An Unknown Set of SQL Statements.
• Constantly Changing SQL Statements.
• SQL Statements are Constructed On-The-Fly.
• SQL Syntax Changes based on the way the user uses
  the Application.
• Managing Access Path Changes
• With Static SQL changes are deferred until Rebind
• With Dynamic SQL statements are continuously
  optimized

12
SQL Literal Consolidation
Helios Matheson Software solutions

• Patent Pending in USA UK
• EZ-Tracer by Default Consolidates equivalent SQL
  Statements eg.
• SELECT X FROM Y WHERE Z ?
• SELECT X FROM Y WHERE Z 123
• SELECT X FROM Y WHERE Z 456
• From a tuning opportunity point of view these
  are really the same statement
• Without consolidation you might be presented with
  100s of thousands of SQL statements making it
  difficult to focus on the real issues.
• With Consolidation, the number of distinct SQL is
  a manageable amount.

13
SQL Cursor Consolidation
Helios Matheson Software solutions

• Merge Cursor Statements
• Report costs for entire SQL and broken down by
  OPEN and FETCH

14
SELECT COUNT() FROM SYSIBM.SYSDUMMY1
Helios Matheson Software solutions

• Ping Is DB2 there?
• CPU Time (MMSS.SSSSSS) is 0000.000098
• Will never get anyones attention as a
  performance tuning opportunity.
• Assuming only 100,000 web users hitting the SEND
  KEY 100 times a day, it will translate into
  10,000,000 Pings.
• Total CPU Time for 10 million Pings is 980
  Seconds or 16 CPU Minutes.
• A Single SQL Statement taking 16 CPU Minutes,
  will be considered a tuning opportunity.
• In many cases the number of Pings gets to 100
  million per day.

15
A Smarter Ping
Helios Matheson Software solutions

• SELECT COUNT() FROM SYSIBM.SYSDUMMY1
• WHERE 1 0
• CPU Time (MMSS.SSSSSS) is 0000.000077
  Representing about 20 avings.
• Customer saved over 4000 CPU seconds per day

16
Another Example
Helios Matheson Software solutions

• SELECT CUS.NAME, CUS.ADDR_LINE_1,
  CUS.ADDR_LINE_2, ORD.CUST_NUMBER, ORD.PRODUCT,
  ORD.QUANTITYORD.PRICE AS SPEND
• FROM CUSTOMER CUS, ORDER ORD , PRODUCT PRD
• WHERE NAME John Smith
• AND CUS.NUMBER ORD.NUMBER
• AND ORD.PRODUCT PRD.PRODUCT
• ORDER BY ORD.CUST_NUMBER, ORD.PRODUCT
• CPU Time (MMSS.SSSSSS) is 0000.070415
• SELECT CUS.NAME, CUS.ADDR_LINE_1,
  CUS.ADDR_LINE_2, ORD.CUST_NUMBER, ORD.PRODUCT,
  ORD.QUANTITYORD.PRICE AS SPEND
• FROM CUSTOMER CUS, ORDER ORD , PRODUCT PRD
• WHERE NAME Joann Williams
• AND CUS.NUMBER ORD.NUMBER
• AND ORD.PRODUCT PRD.PRODUCT
• ORDER BY ORD.CUST_NUMBER, ORD.PRODUCT

17
Another Example (Cont.)
Helios Matheson Software solutions

• A single execution takes (MMSS.SSSSSS)
  0000.070415 CPU
• Assuming 500,000 executions, Total CPU will be
• 37,075 Seconds
• Or 617 Minutes
• Or 10 CPU Hours
• For Sure this is a performance tuning opportunity

18
Dynamic Statement Cache
Helios Matheson Software solutions

• The COST of the PREPARE CAN BE COST OF QUERY
• Dynamic Statement Cache
• Set CACHEDYNYES
• Saves Prepare Cost
• Performance Feature 1 for Dynamic SQL
• SQL Must match EXACTLY to re-use the Prepare
• Same number of blanks and Column sequence
• No LITERAL Values

19
Dynamic Statement Cache
Helios Matheson Software solutions

• A Real Production Example
• Statement with literals needs about 37 minutes of
  CPU time more than same statement with parameter
  markers !
• SQL Authid Program Stmt Stmt SQL
  Average Total
• No Name No Type Execs
  Fetches CPU Cost
• ----- ------- ------- ----- -------- ---------
  ----------- ------------
• 3090 T62EGT1 DDF 1 D-CURSOR 9243
  47 3828.300411
• 2129 T62EGT1 DDF 2129 D-CURSOR 8135
  29 0114.180145

20
Overview
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

21
SQL Activity Reports
Helios Matheson Software solutions

• View Top n SQL after Consolidation
• SORT by any available metric
• E.g. Total / Average CPU, Get Pages, Stage-2
  Requests etc.
• GROUP by DB2 Objects
• Plans, Collections, Packages, DB Names, Tables
• Index Utilization reports by Table
• Optional Trend Analysis database

22
Tracer/Cache Reports
Helios Matheson Software solutions
Go to Option 1
23
Trace Summary Report
Helios Matheson Software solutions
Drill on trace 9
24
Trace Summary Report
Helios Matheson Software solutions
Drill on Interval
25
Trace Summary Report
Helios Matheson Software solutions
26
Trace Summary Report
Helios Matheson Software solutions
Drill on Consolidated SQL
27
Distinct SQL in Workload
Helios Matheson Software solutions
Sorted by Total CPU
Place Cursor and hit
28
Distinct SQL in Workload
Helios Matheson Software solutions
Toggle between All / Static or Dynamic
Now sorted by SQL Execs
29
Distinct SQL in Workload
Helios Matheson Software solutions
Page Right to see further details
Sorted by SQL Consolidated
30
Distinct SQL in Workload
Helios Matheson Software solutions
Page Right to see further details
31
Distinct SQL in Workload
Helios Matheson Software solutions
Page Right to see further details
32
Distinct SQL in Workload
Helios Matheson Software solutions
Page Right to see further details
33
Distinct SQL in Workload
Helios Matheson Software solutions
SQL Contains Literals
34
Distinct SQL in Workload
Helios Matheson Software solutions
DRILL on SQL no to see detail
35
SQL Detail Display (SQL Text)
Helios Matheson Software solutions
36
SQL Detail Display (SQL Text)
Helios Matheson Software solutions
Access Path Version E
DRILL on Index and/or Table names
37
Catalog Statistics Display
Helios Matheson Software solutions
38
SQL Detail Display (SQL Text)
Helios Matheson Software solutions
Use Help for Info about access path
39
SQL Detail Display (SQL Statistics)
Helios Matheson Software solutions
40
SQL Detail Display (SQL Statistics)
Helios Matheson Software solutions
41
SQL Detail Display (SQL Statistics)
Helios Matheson Software solutions
42
SQL Detail Display (SQL Statistics)
Helios Matheson Software solutions
DRILL on CPU time to see detail
43
SQL Detail Display (CPU Time)
Helios Matheson Software solutions
44
Edit and Explain SQL Text
Helios Matheson Software solutions
45
Edit and Explain Access Path(1)
Helios Matheson Software solutions
List Prefetch
ORDER BY
46
Edit and Explain Access Path(2)
Helios Matheson Software solutions
47
Edit and Explain Edit SQL
Helios Matheson Software solutions
FETCH FIRST 10 ROWS ONLY
48
Edit and Explain Access Path
Helios Matheson Software solutions
Version T Access Path
49
Edit and Explain SQL Statistics
Helios Matheson Software solutions
DRILL on Costs to see detail
50
Edit and Explain SQL Statistics
Helios Matheson Software solutions
51
More Reports
Helios Matheson Software solutions
52
SQL by AUTHID
Helios Matheson Software solutions
DRILL on an AUTHID
53
SQL by AUTHID
Helios Matheson Software solutions
54
More Reports
Helios Matheson Software solutions
55
SQL by DBNAME
Helios Matheson Software solutions
DRILL on DBname
56
SQL by Table
Helios Matheson Software solutions
DRILL on Table
57
SQL for Selected Table
Helios Matheson Software solutions
58
Index Utilization Report
Helios Matheson Software solutions
Number of SQL using Table Scan
Number of SQL using index
59
Reports Summary
Helios Matheson Software solutions
60
SQL Alert Reports
Helios Matheson Software solutions

• Filter SQL displays by user specified criteria-
• Access Path characteristics
• Table scan, List Prefetch, etc
• SQL Syntax Characteristics
• SQL uses IN LIST, BETWEEN, DB2 V8 Reserved Word
  etc.
• SQL Exceeds specified thresholds
• Average CPU, Get Pages, Timerons etc.

61
SQL Alert Reports
Helios Matheson Software solutions
62
SQL Alert Reports
Helios Matheson Software solutions
63
Overview
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

64
EZ-Tracer / EZ-Cache
Helios Matheson Software solutions

• EZ-Tracer
• Starts low cost EZ-DB2 Performance trace
• Static and Dynamic SQL
• Explicitly selected IFCIDs to minimize overhead
• EZ-Cache
• Samples DB2 Dynamic Statement Cache
• Dynamic SQL only
• Lowest overhead option

65
Starting a Trace
Helios Matheson Software solutions
66
Starting a Trace Trace Filters
Helios Matheson Software solutions
67
Starting a Trace Consolidation Controls
Helios Matheson Software solutions
68
Summary
Helios Matheson Software solutions

• DB2 Tuning Overview
• The EZ-DB2 Family of Products
• The Challenge of Dynamic SQL
• EZ-Tracer/ EZ-Cache Reports
• Refining the Analysis

69
Addendum How to RUNSTATS in a Dynamic SQL
environment without loosing sleep
Helios Matheson Software solutions
Capture Dynamic SQL Workload using EZ-Cache
ARCHIVE DB2 Statistics Using EZ-Stats
Run RUNSTATS
SAVE NEW Statistics Using EZ-Stats
Run EZ-Impact Analyzer
Access Paths Better?
RESTORE DB2 Statistics Using EZ-Stats
Analyze Impact Analysis Results at your Leisure
Fix issues / Tweak Statistics etc
NO
YES
When Ready Apply Statistics Using EZ-Stats
70
Questions
Helios Matheson Software solutions
for additional information Helios Matheson
North America HMsoftware_at_hmna.com www.HMNAsoftware
.com 978-203-9128

• ?

• www.HMNAsoftware.com
• 978-203-9128

Email us for other presentations - EZ-Impact
Analyzer Industrys leading solution for Access
Path Impact Analysis - EZ-Index Analyzer
Optimizer Unique Powerful solutions for Index
Tuning - DB2 Vcurrent to Vnext migrations
using EZ-Impact Analyzer