Technologies / Data & Analytics

IBM Db2 12 for z/OS SQL Performance and Tuning

Learn how to improve SQL performance in DB2 12 for z/OS. EITT training covers index optimization, multi-table access, subquery and lock management.

Why choose this training?

Effective use of modern tools and methodologies, technical competencies have become a critical asset. Learn how to improve SQL performance in DB2 12 for z/OS. EITT training covers index optimization, multi-table access, subquery and lock management.

Upon completion, participants will be able to: Understand and design better indexes, Determine how to work with the optimizer (avoid pitfalls, provide guidance), Optimize access to multiple tables, Work with subqueries. These competencies directly translate into higher effectiveness in IT projects.

What makes our approach unique?

The EITT approach is based on hands-on experience and practical exercises. Over 3 days of intensive training, participants work on real-world examples and scenarios, ensuring not only theoretical understanding but practical application skills.

With over 2,500 trainings in our portfolio and a 4.8/5 participant rating, EITT is a trusted partner in IT competency development for organizations of all sizes. Our trainers are experienced practitioners who share up-to-date knowledge and proven solutions.

Looking for training tailored to your team’s needs? Contact us — we’ll prepare a program customized to your requirements.

Benefits

Understand and design better indexes
Determine how to work with the optimizer (avoid pitfalls, provide guidance)
Optimize access to multiple tables
Work with subqueries
Avoid blockage problems
Use billing traces and other tools to locate performance problems in existing SQL

Prerequisites

Knowledge of SQL, Db2 12 for z/OS and application programming

Training program

A simple example
Visualize the problem
Performance analysis tools
Components of reaction time
Time estimates from VQUBE3
SQL EXPLAIN
Accounting trail
Bubble chart
Performance thresholds
Index basics
Indexes
Index structure
Index I/O estimation
Index clustering
Index page breakdown
Access paths
Classification
Matching versus screening
Variants
Access to hashing
Initial download
Disclaimer
More about indexes
Include index
Index by expression
Random index
Partitioning and partitioning, NPSI and DPSI
Screening the range of sites
Features and limitations
Tuning methodology and index cost
Methodology
Index cost: Disk space
Index cost: Maintenance
Tools and indexes
Modifying and creating indexes
Avoiding sorting
Index design
Approach
Index design
Advanced access paths
Initial download
Initial list download
Access to multiple indexes
Adaptive index at runtime
Access to multiple tables
Methods of joining
Types of bonding
Index design for linking
Predicting the order of the table
Sub-questions
Correlated sub-queries
Uncorrelated sub-queries
ORDER BY and FETCH FIRST with subqueries
Global query optimization
Virtual tables
Explain subqueries
Set of operations
UNION, EXCEPT and INTERSECT
Rules
More about harvest operators
UNION ALL performance improvements
Table design
Number of tables
Clustering sequence
Denormalization
Materializable Query Tables (MQT)
Time tables
Tables with archiving enabled
Working with the optimizer
Indexable and non-indexable predicates
Boolean and non-boolean predicates
Stage 1 and Stage 2
Filtering agents
Helping the optimizer
Pagination
Blocking problems
ACID test
Reasons for serialization
Serialization mechanisms
Blocking transactions
Promotion, escalation and avoidance of blockade
More blocking problems
Skip locked data
Currently approved data
Optimistic blocking
Hot spots
Application design
Analyzing the blockade wait
A huge party
Batch performance problems
Buffer pool operations
Improving performance
Benefit analysis
Mass removal

Delivery Methods

Online

Convenience of participating from anywhere
Interactive live sessions with trainer
Materials available for 30 days
No travel costs

On-site

Direct contact with trainer and group
Intensive hands-on workshops
Networking with other participants
Full focus on learning

Frequently asked questions

What are the prerequisites for this training?

For IBM Db2 12 for z/OS SQL Performance and Tuning we recommend: Knowledge of SQL, Db2 12 for z/OS and application programming.

What is the format and duration of this training?

The training lasts 3 days and is available in online and on-site format. Sessions run from 9:00 AM to 4:00 PM. We can also customize the schedule to fit your team's needs.