Case Studies in DB2 for zOS Performance Tuning Part 1

1
Case Studies in DB2 for z/OS Performance
TuningPart 1
Session B10 Beginner
www.mdug.org Download Presentations

• Lock Lyon
• Fifth Third Bancorp

May 9, 2007 140 p.m. 240 p.m. Platform z/OS
2
Outline

• Performance Tuning for the beginning DBA who is
  relatively new to DB2 for z/OS
• The New DB2 DBAs Differing Backgrounds,
  Different Expectations
• Approaches to Tuning
• The Three Most Important Performance Reports
• Monitoring Strategies Tuning Tactics
• Where to Begin

3
Is Performance Important?

• The DBAs Most Important Task Performance
  Monitoring (81)
• Most Important Tool Functions Monitoring
  Performance and Availability (83)
• Top DB Challenge Reducing Cost (47)
• Areas of Responsibility for DB Monitoring
  Performance and Tuning of Database (70)

4
The New DB2 DBAs

• Differing Backgrounds
• Differing Expectations
• Data Modeling and Database Design
• SQL Review
• Backup and Recovery
• Security
• Subsystem Installation and Configuration
• Performance Monitoring and Tuning

Operations Application Support Programming DBA
(other platform) Management Systems
Programming University Technical School
5
Approaches to Tuning

• or, Whats the Best Use of Your Time?
• Your Assignment Tune
• How do you judge the best strategies?

SQL Tuning behavior-based Resource Constraint
Tuning resource-based Application
Tuning application-based Object
Tuning architecture-based Systems
Tuning enterprise-based
6
What You Can Do

• or What Is Really Needed? What are the
  Symptoms?

SQL Issues ALWAYS ! Resource Constraint
Issues ALWAYS ! Ask for DASD Ask about CPU,
SLAs Application Issues Complaints Deadlocks /
Timeouts SLAs Missed Object Issues Copy/Reorg
Schedule Enterprise Data Model Systems
Issues Maintenance Philosophy
7
The DBAs Background

• Matching Your Skills to Tuning Approaches

Application Support SQL Tuning Application
Tuning Programming SQL Tuning DBA (other
platform) Resource Constraint Tuning DBMS
Tuning Systems Programmer Resource Constraint
Tuning Systems Tuning Management Object
Tuning Operations Resource Constraint Tuning
8
Before You Tune The Laws

• Lyons Laws of Database Administration
• 1 Data Recoverability
• 2 Data Availability
• 3 Data Security

9
A Checklist for the New DBA

• The Map (Pictures for Communication)
• The Mainframe Environment
• TCP/IP, JES, z/OS, CICS, DBMSs, IRLM, CPU,
  Memory, DASD, etc., etc
• Your DB2 Subsystem Profile
• Tools
• Change Control
• Survival Kit

10
MAP An Example
Otisburg CEC1 IBM z9/109 4-way z/OS 1.6
C F 0 1
SYS1 (XS01)
SYS2 (XS02)
DBG1
DBG1
DBP1
DBP2
TSOP1
MQPA
TSOP2
MQPB
CICSPA
Etc . . .
11
Subsystem Profile An Example
12
Your Tools

• TSO / ISPF / PDF
• DB2 Interactive (DB2I)
• SDSF
• JCL (For reporting)
• FTP
• SMF Processing and Reporting

13
SMF Tools Information Sources

• How Mainframe Performance Information (especially
  DB2 Information) is Exposed

CICS
DB2
SMF
others
SMF Datasets
others
Extracts
others
14
Change Control

• Formal Check-in / Check-out
• Automatic (hopefully) Backout on Error
• Coordination Across the Enterprise
• Approvals Required based on Separation of Duty

15
Your Survival Kit

• References
• Manuals, The Web, Best Practices (?!)
• Sources of HELP
• Manuals
• IBMLink
• DB2-L

16
Tuning Help in the DB2 Manuals -- the
Administration Guide

• Chapter 24. Analyzing performance data
• Chapter 25. Improving response time and
  throughput
• Chapter 26. Tuning DB2 buffer, EDM, RID, sort
  pools
• Chapter 27. Improving resource utilization
• Chapter 29. Tuning your queries
• Chapter 33. Using EXPLAIN to improve SQL
  performance

17
The Performance Tuning Process

• Data Gathering
• Analysis and Selection of Options
• Proof of Concept
• Testing and Cost/Benefit Analysis
• Change Control
• Backout Planning

18
The Three Most Important Performance Reports

• The Accounting Summary
• Explains
• Health Checks

19
Example Accounting Summary
CONNECT OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ---------
------- ------- ------- ------- -------
------- . . . gtgt IMSA 1 0
17.1M 0.00 5664.3K 0.00 BAT0281B 0
87 8547.4K 1.00 1.00 0.00
SQL
Application
CONNECT CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ LOCK SUS PLANNAME CLASS1
CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF
LOCKOUT --------- -------------- --------------
-------- -------- -------- IMSA
3533.067789 3038.956769 4590.8K 296.2K
1.00 BAT0281B 1538.578498 1235.005886
257.2K 122.0K 0
CPU Elapsed Time
I/O Lock
20
Example Explain Report
--------------------- ACCESS PATH SUMMARY
--------- QB PLN MX EMJP AC I MT MJ P ACCESS
JOIN SRN SRC NO. NO. OP METR CS X CL CL M DG
ID DG ID UJOGUJOG --- --- -- ---- -- - -- -- -
------------ -------- 1 1 0 E0 R N 0
0 0 0 0 0 NNNNNNNN TBLPROD.HISTORY
STMT COST
1,126,919.0 1 2 0 E2 R N 0 1 0 0
0 0 NYNNNYNN TBLPROD.HEADER 1
3 0 E1 I N 0 0 0 0 0 0 NNNNNNNN
TBLPROD.CUSTOMER NDXPROD.CUSTOMER2
1 4 0 E1 I N 1 0 0 0 0 0
NNNNNNNN TBLPROD.STATS
NDXPROD.STATS3
21
Example Health Checks

• T.1 Tablespace Partitions with relocated rows
• T.2 System tablespaces in over nn extents
• T.3 User tablespaces in over nn extents
• T.5 Tablespaces over 1 GB in size
• T.6 Tablespaces with no recent Image Copy
• T.7 Tablespace partitions with COMPRESS YES but
  PAGESAVE lt 9
• T.8 Tablespace partitions with PERCACTIVE lt 5
• T.9A Small" Tablespace partitions with COMPRESS
  YES
• T.9B "Large" Tablespace partitions with COMPRESS
  NO
• T.9C Tablespaces with COMPRESS YES and short rows
  (lt 20 Bytes)
• T.10 Tablespaces assigned to the wrong
  bufferpool

22
CASE STUDIES
23
SQL Tuning

• Behavior-based

What You Analyze Using What Tool(s) First Steps
Access Paths Object Statistics User Training --
Dynamic SQL
EXPLAIN (basic) EXPLAIN (Tool) Catalog Mgmt
Reports or Queries Statistics Display
Monitoring Tool
Top 50 SQLs Report Production Explains and
Review SQL Coding Standards
24
SQL Tuning Case Study 1
--------------------- ACCESS PATH SUMMARY
--------- QB PLN MX EMJP AC I MT MJ P ACCESS
JOIN SRN SRC NO. NO. OP METR CS X CL CL M DG
ID DG ID UJOGUJOG --- --- -- ---- -- - -- -- -
------------ -------- 1 1 0 E0 R N 0
0 0 0 0 0 NNNNNNNN TBLPROD.HISTORY
STMT COST
1,126,919.0 1 2 0 E2 R N 0 1 0 0
0 0 NYNNNYNN TBLPROD.HEADER 1
3 0 E1 I N 0 0 0 0 0 0 NNNNNNNN
TBLPROD.CUSTOMER NDXPROD.CUSTOMER2
1 4 0 E1 I N 1 0 0 0 0 0
NNNNNNNN TBLPROD.STATS
NDXPROD.STATS3
25
SQL Tuning Case Study 2

• --------------------- ACCESS PATH SUMMARY
  ---------
• QB PLN MX EMJP AC I MT MJ P ACCESS JOIN SRN
  SRC
• NO. NO. OP METR CS X CL CL M DG ID DG ID
  UJOGUJOG
• --- --- -- ---- -- - -- -- - ------------
  --------
• 1 1 0 E0 R N 0 0 0 0 0 0
  NNYNNNNN
• TBLPROD.STAGING_WRK
• STMT COST 0.04

Statistics SYSIBM.SYSTABLES.CARD
18 SYSIBM.SYSTABLES.CARDF
18 SYSIBM.SYSTABLESPACE.NACTIVE
5 SYSIBM.SYSTABLEPART.PERCACTIVE 40
SELECT COUNT() FROM PROD.STAGING_WRK WITH UR
-------------------- 2158557
26
Resource Constraint Tuning

• Resource-based

What You Analyze Using What Tool(s) First Steps
Memory (Pools) CPU I/O Subsystem Appl.
Throughput Data Availability
Perf Reports (SMF) Resource Msrmt Facility
(RMF)
Top 50 Reports CPU Time(s) Sync I/Os
Elapsed Time(s)
27
Resource Tuning Case Study
ACCOUNTING REPORT TOP (20 ONLY INDB2PT)
LEVEL(SUMMARY)   PRIMAUTH OCCURS ROLLBK
SELECTS INSERTS UPDATES DELETES CLASS1 EL.TIME
CLASS2 EL.TIME GETPAGES SYN.READ PLANNAME
DISTRS COMMIT FETCHES OPENS CLOSES PREPARE
CLASS1 CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF
---------- ------- ------- ------- -------
------- ------- -------------- --------------
-------- --------   PRODDM15 1 0
0.00 0.00 0.00 0.00 11653.512404
11517.386423 53177.4K 35644.00 DSNTIB71
0 1 9127.3K 1.00 1.00 3.00
4139.881114 4112.572460 11302.6K 738.7K  
PRODDL85 1 0 0.00 0.00
0.00 0.00 52443.799259 52430.205904
151.5M 4790.8K DSNUTIL 0 7712
0.00 0.00 0.00 0.00 2110.985557
2105.067317 41126.9K 39098.00   PRODDX07
1 0 4237.7K 4248.0K 4214.0K 0.00
13005.808801 12420.802578 43838.9K 3445.3K
CEF001BP 0 5745 0.00 0.00 0.00
0.00 2124.313339 1628.946885 13421.6K
2405.00
28
Resource Tuning Case Study
CONNECT OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ---------
------- ------- ------- ------- -------
------- . . . gtgt IMSA 1 0
17.1M 0.00 5664.3K 0.00 BAT0281B 0
87 8547.4K 1.00 1.00 0.00
SQL
Application
CONNECT CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ LOCK SUS PLANNAME CLASS1
CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF
LOCKOUT --------- -------------- --------------
-------- -------- -------- IMSA
3533.067789 3038.956769 4590.8K 296.2K
1.00 BAT0281B 1538.578498 1235.005886
257.2K 122.0K 0
CPU Elapsed Time
I/O Lock
29
Resource Tuning Case Study
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- -------
-------   PRODDM15 1 0 0.00
0.00 0.00 0.00 DSNTIB71 0 1
9127.3K 1.00 1.00 3.00 
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
------- ------- ------- ------- ------- -------
-------------- -------------- --------
--------   PRODDM15 1 0 0.00
0.00 0.00 0.00 11653.512404
11517.386423 53177.4K 35644.00 DSNTIB71
0 1 9127.3K 1.00 1.00 3.00
4139.881114 4112.572460 11302.6K 738.7K  
PRODDL85 1 0 0.00 0.00
0.00 0.00 52443.799259 52430.205904
151.5M 4790.8K DSNUTIL 0 7712
0.00 0.00 0.00 0.00 2110.985557
2105.067317 41126.9K 39098.00   PRODDX07
1 0 4237.7K 4248.0K 4214.0K 0.00
13005.808801 12420.802578 43838.9K 3445.3K
CEF001BP 0 5745 0.00 0.00 0.00
0.00 2124.313339 1628.946885 13421.6K
2405.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- --------
--------   PRODDM15 11653.512404
11517.386423 53177.4K 35644.00 DSNTIB71
4139.881114 4112.572460 11302.6K 738.7K
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- ------- -------
PRODDL85 1 0 0.00 0.00
0.00 0.00 DSNUTIL 0 7712 0.00
0.00 0.00 0.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- -------- --------
PRODDL85 52443.799259 52430.205904 151.5M
4790.8K DSNUTIL 2110.985557
2105.067317 41126.9K 39098.00
PRIMAUTH OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ----------
------- ------- ------- ------- ------- -------
PRODDX07 1 0 4237.7K 4248.0K
4214.0K 0.00 CEF001BP 0 5745
0.00 0.00 0.00 0.00
PRIMAUTH CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ PLANNAME CLASS1 CPUTIME
CLASS2 CPUTIME BUF.UPDT TOT.PREF ----------
-------------- -------------- -------- --------
PRODDX07 13005.808801 12420.802578 43838.9K
3445.3K CEF001BP 2124.313339
1628.946885 13421.6K 2405.00
30
Application Tuning

• Application-based

What You Analyze Using What Tool(s) First Steps
OnLine Trans. Prcsng. Data Warehouse ERP
CICS Deadlocks, Timeouts Bind Parameters Master
JESLog Units of Work Commit Frequency Referential
Integrity Batch Cycle Anomalies
Perf Reports (SMF) xxxxMSTR JESLog UoW
Standards Data Model
Top 50 Reports Batch Jobs Transactions
Stored Procedures Review Service Level
Documentation
31
Application Tuning Case Study
CONNECT OCCURS ROLLBK SELECTS INSERTS
UPDATES DELETES PLANNAME DISTRS COMMIT
FETCHES OPENS CLOSES PREPARE ---------
------- ------- ------- ------- -------
------- . . . gtgt IMSA 1 0
17.1M 0.00 5664.3K 0.00 BAT0281B 0
87 8547.4K 1.00 1.00 0.00
SQL
Application
CONNECT CLASS1 EL.TIME CLASS2 EL.TIME
GETPAGES SYN.READ LOCK SUS PLANNAME CLASS1
CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF
LOCKOUT --------- -------------- --------------
-------- -------- -------- IMSA
3533.067789 3038.956769 4590.8K 296.2K
1.00 BAT0281B 1538.578498 1235.005886
257.2K 122.0K 0
CPU Elapsed Time
I/O Lock
32
Object Tuning

• Architecture-based

What You Analyze Using What Tool(s) First Steps
Index Management Partitioning Object Config.
Parms Dataset Placement Stored Procs, UDFs Data
Modeling Referential Integrity
Data Model Advisors
Cross Reference of Objects by
Package Standards for Object Creation
33
Object Tuning Case Studies

• First Implementation of DBMS-enforced RI
• Delete Rules
• SQLCodes, Error-checking Logic
• Reduction in Reorg Requirements
• MaxRows, FreeSpace, PctFree
• Compress
• Forced Partitioning
• Index-Only Reorgs
• Index Re-Design for Multi-Company

34
Systems Tuning

• Enterprise-based

What You Analyze Using What Tool(s) First Steps
TCP/IP Virtual Pools Logging Recovery Maintenance
ZParms IRLM z/OS
Perf Reports (SMF) Resource Reports (RMF) SMP/E
Reports
D/R Planning Recovery Jobs and Backup
Jobs Software Maintenance Strategy
35
Systems Tuning Case Studies

• Access Path Regression after Maintenance
• Tight Service Level Agreement
• Objects Assigned to the Wrong Buffer Pool

36
Where to Begin

• Top n Reports
• Setup
• Initial Review
• Automation
• Health Checks
• Determine Relevance
• Execute, Review Automate
• Tool Review and Education

37
Lock Lyon
Session B10 Case Studies in DB2 for z/OS
Performance Tuning

• Fifth Third Bancorp
• llyon_at_power-net.net

www.mdug.org Download Presentations