EncartaLabs

DB2 for z/OS - SQL Performance and Tuning

( Duration: 3 Days )

The DB2 for z/OS - SQL Performance and Tuning training course is designed to teach the attendees how to prevent application performance problems and to improve the performance of existing SSQL. Attendees will learn about indexes, table design, locking, and other issues relevant to application performance.

By attending DB2 for z/OS - SQL Performance and Tuning workshop, attendees will learn to:

  • Understand and design better indexes
  • Determine how to work with the optimizer (avoid pitfalls, provide guidence)
  • Optimize multi-table access
  • Work with subqueries
  • Avoid locking problems
  • Use accounting traces and other tools to locate performance problems in existing SQL

  • Familiarity with SQL
  • Familiarity with Db2 12 for z/OS
  • Familiarity with Db2 12 for z/OS application programming

This course is designed for DB2 for z/OS application developers, DB2 for z/OS DBAs

COURSE AGENDA

1

Introduction to SQL performance and tuning

  • Performance issues
  • Simple example
  • Visualizing the problem
  • Summary
2

Performance analysis tools

  • Components of response time
  • Time estimates with VQUBE3
  • SQL EXPLAIN
  • The accounting trace
  • The bubble chart
  • Performance thresholds
3

Index basics

  • Indexes
  • Index structure
  • Estimating index I/Os
  • Clustering index
  • Index page splits
4

Access paths

  • Classification
  • Matching versus Screening
  • Variations
  • Hash access
  • Prefetch
  • Caveat
5

More on indexes

  • Include index
  • Index on expression
  • Random index
  • Partitioned and partitioning, NPSI and DPSI
  • Page range screening
  • Features and limitations
6

Tuning methodology and index cost

  • Methodology
  • Index cost: Disk space
  • Index cost: Maintenance
  • Utilities and indexes
  • Modifying and creating indexes
  • Avoiding sorts
7

Index design

  • Approach
  • Designing indexes
8

Advanced access paths

  • Prefetch
  • List prefetch
  • Multiple index access
  • Runtime adaptive index
9

Multiple table access

  • Join methods
  • Join types
  • Designing indexes for joins
  • Predicting table order
10

Subqueries

  • Correlated subqueries
  • Non-correlated subqueries
  • ORDER BY and FETCH FIRST with subqueries
  • Global query optimization
  • Virtual tables
  • Explain for subqueries
11

Set operations (optional)

  • UNION, EXCEPT, and INTERSECT
  • Rules
  • More about the set operators
  • UNION ALL performance improvements
12

Table design (optional)

  • Number of tables
  • Clustering sequence
  • Denormalization
  • Materialized query tables (MQTs)
  • Temporal tables
  • Archive enabled tables
13

Working with the optimizer

  • Indexable versus non-indexable predicates
  • Boolean versus non-Boolean predicates
  • Stage 1 versus stage 2
  • Filter factors
  • Helping the optimizer
  • Pagination
14

Locking issues

  • The ACID test
  • Reasons for serialization
  • Serialization mechanisms
  • Transaction locking
  • Lock promotion, escalation, and avoidance
15

More locking issues (optional)

  • Skip locked data
  • Currently committed data
  • Optimistic locking
  • Hot spots
  • Application design
  • Analyzing lock waits
16

Massive batch (optional)

  • Batch performance issues
  • Buffer pool operations
  • Improving performance
  • Benefit analysis
  • Massive deletes

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top