New
 

Schulung - IBM CV964G - Db2 12 for z/OS SQL Performance and Tuning

  • Live Online oder Präsenz
Download als PDF
DURCHFÜHRUNG MIT TERMIN
Nr.
30196

Dauer
3 Tage (18 Stunden)

Preis
2.400,00 € netto
2.856,00 € inkl. 19% MwSt.
TERMIN UND ORT NACH ABSPRACHE
On-demand Training
Sind Sie an diesem Thema interessiert?
Unsere Experten entwickeln Ihr individuell angepasstes Seminar!

Teilen Sie dieses Seminar

Overview

This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.

Zielgruppe

Wer sollte teilnehmen:

Zielgruppe

Audience

This course is for Db2 12 for z/OS application developers, Db2 12 for z/OS DBAs, and anyone else with a responsibility for SQL performance and tuning in a Db2 12 for z/OS environment.

Voraussetzungen

Prerequisites

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

Trainingsprogramm

Trainingsprogramm

Course Outline

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 (optional)
UNION, EXCEPT, and INTERSECT
Rules
More about the set operators
UNION ALL performance improvements

Table design (optional)
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 (optional)
Skip locked data
Currently committed data
Optimistic locking
Hot spots
Application design
Analyzing lock waits

Massive batch (optional)
Batch performance issues
Buffer pool operations
Improving performance
Benefit analysis
Massive deletes

Objective

After completing this course, students will be able 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
  • and more

Schulungsmethode

Schulungsmethode

presentation, discussion, hands-on exercises

Weitere Informationen

Termine und Orte

Sortieren nach:

  • Vom 02 Mär bis 04 Mär 2021
    München
    2.400,00 € Netto
    Noch freie Plätze
    Jetzt buchen
    Informationen zum Veranstaltungsort erhalten Sie mit der Anmeldebestätigung.
  • Vom 02 Mär bis 04 Mär 2021
    Hamburg
    2.400,00 € Netto
    Noch freie Plätze
    Jetzt buchen
    ExperTeach Gesellschaft für Netzwerkkompetenz mbH Hamburg
    Esplanade 6
    20354 Hamburg
  • Vom 02 Mär bis 04 Mär 2021
    Live Online Training
    2.400,00 € Netto
    Noch freie Plätze
    Jetzt buchen
    Live Online Training
    Live Online Training
  • Vom 17 Aug bis 19 Aug 2021
    Live Online Training
    2.400,00 € Netto
    Noch freie Plätze
    Jetzt buchen
    Live Online Training
    Live Online Training
  • Vom 17 Aug bis 19 Aug 2021
    Leinfelden-Echterdingen
    2.400,00 € Netto
    Noch freie Plätze
    Jetzt buchen
    Informationen zum Veranstaltungsort erhalten Sie mit der Anmeldebestätigung.
Schulung - IBM CV964G - Db2 12 for z/OS SQL Performance and Tuning