> Formations > IBM Db2 12 for z/OS SQL Performance and Tuning
Publication date : 01/03/2024
Course : IBM Db2 12 for z/OS SQL Performance and Tuningprevent SQL performance problems
Practical course - 3d
- 21h00 - Ref. IBZ
|
Download in PDF format
Share this course by email
 | Understanding and designing better indexes |
| Determine how to work with the optimizer (avoid pitfalls, provide tips) |
| Optimizing multi-table access |
| Working with subqueries |
| Avoid locking problems |
| Use accounting trails and other tools to locate performance problems in existing SQL |
Intended audience
Développeurs d'applications Db2 12 z/OS, administrateurs de bases de données Db2 12 for z/OS, toute personne responsable des performances et du réglage SQL dans un environnement Db2 12 for z/OS.
Prerequisites
Familiarity with SQL, Db2 12 for z/OS and Db2 12 for z/OS application programming.
Certification
Official course without certification.
Comment passer votre examen ?
Comment passer votre examen ?
Practical details
Teaching methods
Training in French. Official course material in digital format and in English. Good understanding of written English.
Course schedule
1 Introduction to SQL performance and tuning
- Performance problems.
- A simple example.
- Visualize the problem.
- Summary.
2 Performance analysis tools
- Response time components.
- Time estimates with VQUBE3.
- SQL EXPLAIN.
- Accounting trace.
- Bubble chart.
- Performance thresholds.
3 Index basics
- Indexes.
- Index structure.
- I/O index estimation.
- Clustering index.
- Index page splits.
4 Access roads
- Classification.
- Matching versus screening.
- Variations.
- Hash access.
- Prior research.
5 More about indexes
- Include an index.
- Index on an expression.
- Random index.
- Partitioned and partitioning.
- NPSI and DPSI.
- Page range selection.
- Features and limitations.
6 Setting methodology and index cost
- Methodology.
- Index cost: disk space.
- Cost of index: maintenance.
- Utilities and index.
- Index creation and modification.
- Avoid sorting.
7 Index design
- Approach.
- Index design.
8 Advanced access paths
- Preloading.
- Picking by list.
- Access to multiple indexes.
- Adaptive execution index.
9 Access to multiple tables
- Joining methods.
- Joint types.
- Conception d’index pour les jointures.
- Table order prediction.
10 Subqueries
- Correlated sub-surveys.
- Uncorrelated sub-surveys.
- Order by and Fetch First with subqueries.
- Global query optimization.
- Virtual tables.
- Explanation of subqueries
11 Define operations (optional)
- Union, Except and Intersect.
- Rules.
- Find out more about overall operators.
- Improving Union All's performance.
12 Table design (optional)
- Clustering sequence.
- Time tables.
- Denormalization.
- Archived tables.
- Materialized Query Tables (MQT).
13 Working with the optimizer
- Indexable and non-indexable predicates.
- Boolean and non-Boolean predicates.
- Step 1 vs. step 2.
- Filter factors.
- Help the optimizer.
- Pagination.
14 Locking problems
- The ACID test.
- Reasons for serialization.
- Serialization mechanisms.
- Transaction locking.
15 Other locking problems (optional)
- Ignore locked data.
- Data currently validated.
- Optimized locking.
- Application design.
- Analysis of locking expectations.
16 Solid lot (optional)
- Batch performance problems.
- Buffer pool operations.
- Improved performance.
- Benefits analysis.
- Massive cuts.
PARTICIPANTS
Développeurs d'applications Db2 12 z/OS, administrateurs de bases de données Db2 12 for z/OS, toute personne responsable des performances et du réglage SQL dans un environnement Db2 12 for z/OS.
PREREQUISITES
Familiarity with SQL, Db2 12 for z/OS and Db2 12 for z/OS application programming.
TRAINER QUALIFICATIONS
The experts who lead the training courses are specialists in the subjects covered. They are approved by the publisher and certified for the course. They have also been validated by our teaching teams in terms of both professional knowledge and teaching skills for each course they teach. They have at least three to ten years of experience in their field and hold or have held positions of responsibility in companies.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training course.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you have specific accessibility requirements? Contact Ms FOSSE, disability advisor, at the following address: psh-accueil@orsys.fr so that we can assess your request and its feasibility.
Développeurs d'applications Db2 12 z/OS, administrateurs de bases de données Db2 12 for z/OS, toute personne responsable des performances et du réglage SQL dans un environnement Db2 12 for z/OS.
PREREQUISITES
Familiarity with SQL, Db2 12 for z/OS and Db2 12 for z/OS application programming.
TRAINER QUALIFICATIONS
The experts who lead the training courses are specialists in the subjects covered. They are approved by the publisher and certified for the course. They have also been validated by our teaching teams in terms of both professional knowledge and teaching skills for each course they teach. They have at least three to ten years of experience in their field and hold or have held positions of responsibility in companies.
ASSESSMENT TERMS
Assessment of targeted skills prior to training.
Assessment by the participant, at the end of the training course, of the skills acquired during the training course.
Validation by the trainer of the participant's learning outcomes, specifying the tools used: multiple-choice questions, role-playing exercises, etc.
At the end of each training course, ITTCERT provides participants with a course evaluation questionnaire, which is then analysed by our teaching teams. Participants also complete an official evaluation of the publisher.
An attendance sheet for each half-day of attendance is provided at the end of the training course, along with a certificate of completion if the participant has attended the entire session.
Assessment of targeted skills prior to training.
Assessment by the participant, at the end of the training course, of the skills acquired during the training course.
Validation by the trainer of the participant's learning outcomes, specifying the tools used: multiple-choice questions, role-playing exercises, etc.
At the end of each training course, ITTCERT provides participants with a course evaluation questionnaire, which is then analysed by our teaching teams. Participants also complete an official evaluation of the publisher.
An attendance sheet for each half-day of attendance is provided at the end of the training course, along with a certificate of completion if the participant has attended the entire session.
TEACHING AIDS AND TECHNICAL RESOURCES
The teaching resources used are the publisher's official materials and practical exercises.
The teaching resources used are the publisher's official materials and practical exercises.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training course.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you have specific accessibility requirements? Contact Ms FOSSE, disability advisor, at the following address: psh-accueil@orsys.fr so that we can assess your request and its feasibility.
Dates and locations
Select your location or opt for the remote class then choose your date.
Remote class
Dernières places
Date garantie en présentiel ou à distance
Session garantie