DB2 for z/OS Best Practices

DB2 for z/OS
Best Practices
How DB2 Performance
Structures Improve
Performance
Sheryl M. Larsen
IBM WW DB2 for z/OS Evangelist
[email protected]
© 2014 IBM Corporation
DB2 for z/OS Best Practices
IBM®
Sheryl M. Larsen
[email protected]
Sheryl Larsen is an internationally
recognized researcher, consultant and
lecturer, specializing in DB2 and is
known for her extensive expertise in
SQL. She co-authored a book, DB2
Answers, Osborne-McGraw-Hill, 1999.
She now works for IBM, but this material
was developed before joining IBM and is
the culmination of 25 years as an
external consultant specializing in
superhuman efforts to tune customer
workloads.
© 2014 IBM Corporation
Her new title is IBM’s World Wide DB2
for z/OS Evangelist. Other titles include:
President of the Midwest Database
Users Group,
IDUG Hall of Fame Speaker,
IDUG Hall of Fame Volunteer, and
Northern Illinois University Computer
Science Alumni Council Board Member
Sheryl has over 25 years experience
in DB2, has published articles, white
papers, webcasts: bmc.com, ca.com,
softbase.com,
ibm.developerworks.com
2
DB2 for z/OS Best Practices
IBM®
Disclaimer/Trademarks
© Copyright IBM Corporation 2013. All rights reserved.
U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM
Corp.
THE INFORMATION CONTAINED IN THIS DOCUMENT HAS NOT BEEN SUBMITTED TO ANY FORMAL IBM TEST AND IS DISTRIBUTED AS IS.
THE USE OF THIS INFORMATION OR THE IMPLEMENTATION OF ANY OF THESE TECHNIQUES IS A CUSTOMER RESPONSIBILITY AND
DEPENDS ON THE CUSTOMER’S ABILITY TO EVALUATE AND INTEGRATE THEM INTO THE CUSTOMER’S OPERATIONAL ENVIRONMENT.
WHILE IBM MAY HAVE REVIEWED EACH ITEM FOR ACCURACY IN A SPECIFIC SITUATION, THERE IS NO GUARANTEE THAT THE SAME OR
SIMILAR RESULTS WILL BE OBTAINED ELSEWHERE. ANYONE ATTEMPTING TO ADAPT THESE TECHNIQUES TO THEIR OWN
ENVIRONMENTS DO SO AT THEIR OWN RISK.
ANY PERFORMANCE DATA CONTAINED IN THIS DOCUMENT WERE DETERMINED IN VARIOUS CONTROLLED LABORATORY
ENVIRONMENTS AND ARE FOR REFERENCE PURPOSES ONLY. CUSTOMERS SHOULD NOT ADAPT THESE PERFORMANCE NUMBERS TO
THEIR OWN ENVIRONMENTS AS SYSTEM PERFORMANCE STANDARDS. THE RESULTS THAT MAY BE OBTAINED IN OTHER OPERATING
ENVIRONMENTS MAY VARY SIGNIFICANTLY. USERS OF THIS DOCUMENT SHOULD VERIFY THE APPLICABLE DATA FOR THEIR SPECIFIC
ENVIRONMENT.
Trademarks
IBM, the IBM logo, ibm.com, and DB2 are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other
product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at “Copyright and
trademark information” at www.ibm.com/legal/copytrade.shtml.
© 2014 IBM Corporation
3
IBM®
DB2 for z/OS Best Practices
All the Performance Decisions
Use the Right Stuff for the Job!
Schema Design
Partitioning
Clustering
Base Table Indexes
Index on Expression
Materialize Query Tables (MQTs)
MQT Indexes
zIIPs
zAAPs
Accelerated Query Tables (AQTs)
© 2014 IBM Corporation
Performance
Structures
Appliances
4
IBM®
DB2 for z/OS Best Practices
Optimal Schema & Index & MQT Design
Indexes
Index
On
Expression
Schema
MQT
Indexes
MQT
MQT
MQT
M
Q
T
MQT
Schema
AQT
© 2014 IBM Corporation
5
DB2 for z/OS Best Practices
IBM®
Designing Optimal Performance
Structures
for One Query or a Workload
© 2014 IBM Corporation
6
IBM®
DB2 for z/OS Best Practices
One Query at a Time
1.
Equal predicates first
2.
Sequencing columns next
3.
Predicates most restrictive to least restrictive
4.
All remaining SELECT Columns
Type 2 Index
C4.C5.C6.C1.C2.C3.C9.C7.C8.C12
Non-Leaf Page
O O O
Leaf Page
o o o o o o
Leaf Page
o o o o o o
© 2014 IBM Corporation
Root Page
O O O
Non-Leaf Page
O O
Leaf Page
o o o o o o
Leaf Page
o o o o o o
Leaf Page
o o o o o o
SELECT C7, C8, C12, C2, C3
WHERE C4 = ‘L’
AND C5 = 99
AND C6 = :hv
AND C1 IN (:a,:b,:c)
AND C2 NOT LIKE :hv
AND C3 BETWEEN :hvstart
AND :hvend
AND C9 =
(CASE
expression)
Non-Leaf Page
O O O
ORDER BYOC1.C2.C3
Leaf Page
o o o o o o
Leaf Page
o o o o o o
Leaf Page
o o o o o o
Leaf Page
o o o o o o
7
IBM®
DB2 for z/OS Best Practices
Materialized Query Tables
 Can be almost ANY valid SELECT statement
 CREATE TABLE MQT1 AS SELECT… use your imagination ..
;
– System-maintained
Can be fast!
– SQL REFRESH TABLE option
– Deletes all rows, refills table, updates catalog
– User-maintained (via triggers, replication, batch updates, etc.)
– REFRESH DEFERRED
– Via LOAD, INSERT, UPDATE, DELETE
– REFRESH IMMEDIATE – LUW only
© 2014 IBM Corporation
8
DB2 for z/OS Best Practices
IBM®
MQT…. To Build or not to Build
Generic MQT strategies
– HUGE MQT with many indexes
– Many small MQTs with few indexes
Optimal MQT strategy
– Most popular queries as MQTs
– Common tables
– Common predicate sets
– Common data translations
– Indexes on the popular ones to allow access to subset of MQT
© 2014 IBM Corporation
9
IBM®
DB2 for z/OS Best Practices
Expensive Recursive Syntax
WITH ORG_CHART(BOSS, MANAGES, N) AS
(SELECT
Ginni
BOSS, MANAGES, 1
FROM
ORG_TABLE
WHERE
BOSS= ‘GINNI’
UNION ALL
SELECT
T1.BOSS, T2.MANAGES, N+1
FROM
ORG_CHART T2, ORG_TABLE T1
WHERE
T2.MANAGES = T2.BOSS
AND
N < 500,000)
SELECT BOSS, MANAGES, N
FROM
ORG_CHART
© 2014 IBM Corporation
Generated
at Execution
10
DB2 for z/OS Best Practices
MQT INDEX:
BOSS
IBM®
MQT
CREATE TABLE ORG_LIST AS (
WITH ORG_CHART(BOSS, MANAGES, N) AS
(SELECT BOSS, MANAGES, 1
FROM
ORG_TABLE
WHERE
BOSS= ‘GINNI’
UNION ALL
SELECT
T1.BOSS, T2. MANAGES, N+1
FROM
ORG_CHART T2, ORG_TABLE T1
WHERE
T2. MANAGES = T2.BOSS )
SELECT BOSS, MANAGES, N
FROM
ORG_CHART)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTMIZATION;
© 2014 IBM Corporation
11
IBM®
DB2 for z/OS Best Practices
Query Speed Up
WITH ORG_CHART (BOSS, MANAGES N) AS
(SELECT
BOSS, MANAGES, 1
FROM ORG_TABLE
WHERE BOSS= ‘BETH’
UNION ALL
SELECT T1.BOSS, T2.MANAGES, N+1
FROM ORG_CHART T2, ORG_TABLE T1
WHERE T2.MANAGES = T2.BOSS
AND N < 500,000)
SELECT BOSS, MANAGES, N
FROM ORG_CHART
Accessed at
Execution
ORG_LIST
SELECT * FROM ORG_LIST
WHERE BOSS = ‘BETH’
© 2014 IBM Corporation
BOSS EMP
JOHN ADAM
BETH JIM
BETH SUE
BETH MARK
BETH SAM
N
501
634
634
635
635 12
DB2 for z/OS Best Practices
IBM®
Designing Indexes
For a Workload
© 2014 IBM Corporation
13
O1 = Order By First Position
IBM®
G1 = Group By First Position
S = SELECT list
W = Local or Join WHERE/ON or
Specify actual local/join filter Specify
Group by Aggregates as: Aggr BY
Column(s)
DB2 for z/OS Best Practices
Table/Column Usage
Tran
c1
c2
c3
c4 c5
c6 c7 c8
c9
c10
c11
O1
O2
O3
S
S
SUM by
AVG by
MAX by
G1
G2
G3
C1.C2.C
BTW
2and 7 3
C1.C2.C3
C1.C2.C3
=T4.C2
=:hv
=:hv
>800
q2
q3
s
s
w
=22
o1
=:hv
=:hv
o2
=T3.C1
s
s
s
s
s
o1
=T3.C11
=T3.C1
q1
O1
O2
O3
=:hv
=:hv
>:hv
>:hv
q2
s
s
=:hv
=:hv
tran1
q1
=T2.C1
S
S
s
tran2
© 2014 IBM Corporation
s
s
s
s
s
O1
IN(list)
IN(list)
LIKE :hv
:hv
14
IBM®
DB2 for z/OS Best Practices
Final Ranking
Weighting Based on
Importance to the
Business
Weight
tran1
100
4200
q1
O1
G1
W
10
q2
9900
q3
s
4200
4200
4200
10
O2
O3
2
G3
4200
G
4200
w
4200
s
10
w
w
S
4200
4200
4200
10
w
4200
w
10
S
4200
o1
10
w
3
0
0
© 2014 IBM Corporation
4200
w
10
S
4200
o2
w
4200
4200
10
o1
9900
tran2
S
s
w
9900 9900 9900
w
9900
Frequency * Weight
15
IBM®
DB2 for z/OS Best Practices
Determining Weight
 Copy the frequency report
 Sort by “business priorities”
– Use Work Load Manager Goals for guidance
– Use Business Unit Owner Priorities
 Remove the top 30%
 Sort by Frequency
Pulls out most critical
Let’s say 5,500 is top frequent
 Pull of the top number
 Multiply by 10
Then 55,000 is the
goal
 Now you have the frequency number to beat
© 2014 IBM Corporation
16
IBM®
DB2 for z/OS Best Practices
Grand Total
 By Table, by Operation
Total
c1
c2
c3
c4
c5
c6
c7
c8
O1 55980
O2 55980
O3 55980
S 660
O1 8740
O2 3940
S 4000
S 8390
G1 4790
G2 4790
G3 4790
S 8740
S 480
W 7640
W 2020
O2 2220
W 400
W3
w 22090
Activity
O6 70
W 3920
Use this information to justify making changes to
current index design
© 2014 IBM Corporation
17
DB2 for z/OS Best Practices
IBM®
Accelerated Query Tables for Unlimited Searches
© 2014 IBM Corporation
18
IBM®
DB2 for z/OS Best Practices
Current Schema & Index
Index
Other Relational
DBMS
Schema
RI discards
© 2014 IBM Corporation
19
DB2 for z/OS Best Practices
IBM®
Index Design Improvements
 Every index except for primary and clustering was not servicing the web requests
 Limiting factor is number of new indexes due to expensive Q-rep
 Minimal index environment for B-Page searches would need 50 indexes!
 Optimal B-Page is unlimited search
© 2014 IBM Corporation
20
IBM®
DB2 for z/OS Best Practices
B-Page Web Inquiry Search
Attribute 1
________
Attribute 2
_________
Attribute 5
________
© 2014 IBM Corporation
Date 1
___________
Attribute 3
________
Attribute 4
________
Date 2
___________
21
21
IBM®
DB2 for z/OS Best Practices
Accelerated Query Table Advantage
Index
On
Expression
Indexes
Indexxxxxx
Schema
AQT
© 2014 IBM Corporation
© Sheryl M. Larsen, Inc. 2000-2013
22
22
DB2 for z/OS Best Practices
IBM®
DB2 11 The Foundation for Business Critical Analytics
DB2 for z/OS and
IBM DB2 Analytics Accelerator
OLTP Transactions
Real time data ingestion
High concurrency
Operational analytics
Standard reports
OLAP
Complex queries
23
© 2014 IBM Corporation
DB2 Native
Processing
• DB2 11 CPU savings
benefit query workloads
with or without IDAA
DB2 for z/OS Best Practices
AQTs live in the IBM DB2 Analytics Accelerator S-BladeTM
IBM®
Dual-Core FPGA*
Intel Quad-Core
*FPGA - Field Programmable Gate Array
© 2014 IBM Corporation
IBM BladeCenter Blade
24
IBM®
DB2 for z/OS Best Practices
The Key to the Speed
select DISTRICT,
PRODUCTGRP,
sum(NRX)
from
MTHLY_RX_TERR_DATA
where MONTH = '20091201'
and
MARKET = 509123
and
SPECIALTY = 'GASTRO‘
group by DISTRICT, PRODUCTGRP
Slice of table
MTHLY_RX_TERR_DATA
(compressed)
Field Programmable Gate Array
FPGA Core
Uncompress
Project
Restrict,
Visibility
Complex ∑
Joins, Aggs, etc.
sum(NRX)
group by DISTRICT,
PRODUCTGRP
select DISTRICT,
PRODUCTGRP
© 2014 IBM Corporation
CPU Core
where
and
and
MONTH = '20091201'
MARKET = 509123
SPECIALTY = 'GASTRO'
25
IBM®
DB2 for z/OS Best Practices
Optimal ODS Search - RI Index Only & AQT
Index
Other Relational
DBMS
Schema
RI discards
AQT
© 2014 IBM Corporation
© Sheryl M. Larsen, Inc. 2000-2013
26
26
DB2 for z/OS Best Practices
IBM®
Best Practice Sites
 Have tools and staff to assist in query development and index
design
– IBM’s Data Studio is FREE!
– Index design tools are extra $$,$$$
 Justify IBM DB2 Analytic Accelerator for speeding up complex
queries and allowing unlimited long searches
 Have production environments emulated in test 100% (this
may not be 100% physical)
© 2014 IBM Corporation
27
Similar pages