Db2 12 for z/OS SQL Performance and Tuning

Learn how to prevent SQL performance problems and how to improve the performance of existing SQL.

    Nov 16 2020

    November 16 - 18, 2020 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 11/16/2020 - 11/18/2020 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    November 16 - 18, 2020 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Dec 14 2020

    December 14 - 16, 2020 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 12/14/2020 - 12/16/2020 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    December 14 - 16, 2020 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Jan 11 2021

    January 11 - 13, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 01/11/2021 - 01/13/2021 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    January 11 - 13, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Feb 8 2021

    February 8 - 10, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 02/08/2021 - 02/10/2021 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    February 8 - 10, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Mar 8 2021

    March 8 - 10, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 03/08/2021 - 03/10/2021 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    March 8 - 10, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

    Apr 5 2021

    April 5 - 7, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live

    Date: 04/05/2021 - 04/07/2021 (Monday - Wednesday) | 9:00 AM - 5:00 PM (EST)
    Location: ONLINE (Virtual Classroom Live)
    Delivery Format: VIRTUAL CLASSROOM LIVE Request Quote & Enroll

    Success! Your message has been sent to us.
    Error! There was an error sending your message.

    REQUEST MORE INFO:


    Db2 12 for z/OS SQL Performance and Tuning

    April 5 - 7, 2021 | 9:00 AM - 5:00 PM (EST) | Virtual Classroom Live


    How Did You Hear of Global IT Training?

    Join Our Email List?

Introduction to SQL performance and tuning

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

Performance analysis tools

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

Index basics

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

Access paths

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

More on indexes

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

Tuning methodology and index cost

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

Index design

  • Approach
  • Designing indexes

Advanced access paths

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

Multiple table access

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

Subqueries

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

Set operations

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

Table design

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

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

Locking issues

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

More locking issues

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

Massive batch

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

After completing this course, you will be able to:

  • Understand and design better indexes
  • Determine how to work with the optimizer (avoid pitfalls, provide guidance)
  • 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, Db2 12 for z/OS, and application programming

Ready to Advance Your Career?

CONTACT US NOW!