{"course":{"productid":19868,"modality":6,"active":true,"language":"fr","title":"Db2 12 for z\/OS SQL Performance and Tuning","productcode":"CV964G","vendorcode":"IB","vendorname":"IBM","fullproductcode":"IB-CV964G","courseware":{"has_ekit":true,"has_printkit":false,"language":"en"},"url":"https:\/\/portal.flane.ch\/course\/ibm-cv964g","objective":"<p>After completing this course, students will be able to:\n<\/p>\n<ul>\n<li>Understand and design better indexes<\/li><li>Determine how to work with the optimizer (avoid pitfalls, provide guidence)<\/li><li>Optimize multi-table access<\/li><li>Work with subqueries<\/li><li>Avoid locking problems<\/li><li>Use accounting traces and other tools to locate performance problems in existing SQL<\/li><li>and more<\/li><\/ul>","essentials":"<ul>\n\n\t<li>Familiarity with SQL<\/li><li>Familiarity with Db2 12 for z\/OS<\/li><li>Familiarity with Db2 12 for z\/OS application programming<\/li><\/ul>","audience":"<p>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.<\/p>","contents":"<p>Introduction to SQL performance and tuning\n<\/p>\n<ul>\n<li>Performance issues<\/li><li>Simple example<\/li><li>Visualizing the problem<\/li><li>Summary<\/li><\/ul><p>\nPerformance analysis tools\n<\/p>\n<ul>\n<li>Components of response time<\/li><li>Time estimates with VQUBE3<\/li><li>SQL EXPLAIN<\/li><li>The accounting trace<\/li><li>The bubble chart<\/li><li>Performance thresholds<\/li><\/ul><p>\nIndex basics\n<\/p>\n<ul>\n<li>Indexes<\/li><li>Index structure<\/li><li>Estimating index I\/Os<\/li><li>Clustering index<\/li><li>Index page splits<\/li><\/ul><p>\nAccess paths\n<\/p>\n<ul>\n<li>Classification<\/li><li>Matching versus Screening<\/li><li>Variations<\/li><li>Hash access<\/li><li>Prefetch<\/li><li>Caveat<\/li><\/ul><p>\nMore on indexes\n<\/p>\n<ul>\n<li>Include index<\/li><li>Index on expression<\/li><li>Tandom index<\/li><li>Partitioned and partitioning, NPSI and DPSI<\/li><li>Page range screening<\/li><li>Features and limitations<\/li><\/ul><p>\nTuning methodology and index cost\n<\/p>\n<ul>\n<li>Methodology<\/li><li>Index cost: Disk space<\/li><li>Index cost: Maintenance<\/li><li>Utilities and indexes<\/li><li>Modifying and creating indexes<\/li><li>Avoiding sorts<\/li><\/ul><p>\nIndex design\n<\/p>\n<ul>\n<li>Approach<\/li><li>Designing indexes<\/li><\/ul><p>\nAdvanced access paths\n<\/p>\n<ul>\n<li>Prefetch<\/li><li>List prefetch<\/li><li>Multiple index access<\/li><li>Runtime adaptive index<\/li><\/ul><p>\nMultiple table access\n<\/p>\n<ul>\n<li>Join methods<\/li><li>Join types<\/li><li>Designing indexes for joins<\/li><li>Predicting table order<\/li><\/ul><p>\nSubqueries\n<\/p>\n<ul>\n<li>Correlated subqueries<\/li><li>Non-correlated subqueries<\/li><li>ORDER BY and FETCH FIRST with subqueries<\/li><li>Global query optimization<\/li><li>Virtual tables<\/li><li>Explain for subqueries<\/li><\/ul><p>\nSet operations (optional)\n<\/p>\n<ul>\n<li>UNION, EXCEPT, and INTERSECT<\/li><li>Rules<\/li><li>More about the set operators<\/li><li>UNION ALL performance improvements<\/li><\/ul><p>\nTable design (optional)\n<\/p>\n<ul>\n<li>Number of tables<\/li><li>Clustering sequence<\/li><li>Denormalization<\/li><li>Materialized query tables (MQTs)<\/li><li>Temporal tables<\/li><li>Archive enabled tables<\/li><\/ul><p>\nWorking with the optimizer\n<\/p>\n<ul>\n<li>Indexable versus non-indexable predicates<\/li><li>Boolean versus non-Boolean predicates<\/li><li>Stage 1 versus stage 2<\/li><li>Filter factors<\/li><li>Helping the optimizer<\/li><li>Pagination<\/li><\/ul><p>\nLocking issues\n<\/p>\n<ul>\n<li>The ACID test<\/li><li>Reasons for serialization<\/li><li>Serialization mechanisms<\/li><li>Transaction locking<\/li><li>Lock promotion, escalation, and avoidance<\/li><\/ul><p>\nMore locking issues (optional)\n<\/p>\n<ul>\n<li>Skip locked data<\/li><li>Currently committed data<\/li><li>Optimistic locking<\/li><li>Hot spots<\/li><li>Application design<\/li><li>Analyzing lock waits<\/li><\/ul><p>\nMassive batch (optional)\n<\/p>\n<ul>\n<li>Batch performance issues<\/li><li>Buffer pool operations<\/li><li>Improving performance<\/li><li>Benefit analysis<\/li><li>Massive deletes<\/li><\/ul>","outline":"<p>Introduction to SQL performance and tuning\n<\/p>\n<ul>\n<li>Performance issues<\/li><li>Simple example<\/li><li>Visualizing the problem<\/li><li>Summary<\/li><\/ul><p>\nPerformance analysis tools\n<\/p>\n<ul>\n<li>Components of response time<\/li><li>Time estimates with VQUBE3<\/li><li>SQL EXPLAIN<\/li><li>The accounting trace<\/li><li>The bubble chart<\/li><li>Performance thresholds<\/li><\/ul><p>\nIndex basics\n<\/p>\n<ul>\n<li>Indexes<\/li><li>Index structure<\/li><li>Estimating index I\/Os<\/li><li>Clustering index<\/li><li>Index page splits<\/li><\/ul><p>\nAccess paths\n<\/p>\n<ul>\n<li>Classification<\/li><li>Matching versus Screening<\/li><li>Variations<\/li><li>Hash access<\/li><li>Prefetch<\/li><li>Caveat<\/li><\/ul><p>\nMore on indexes\n<\/p>\n<ul>\n<li>Include index<\/li><li>Index on expression<\/li><li>Tandom index<\/li><li>Partitioned and partitioning, NPSI and DPSI<\/li><li>Page range screening<\/li><li>Features and limitations<\/li><\/ul><p>\nTuning methodology and index cost\n<\/p>\n<ul>\n<li>Methodology<\/li><li>Index cost: Disk space<\/li><li>Index cost: Maintenance<\/li><li>Utilities and indexes<\/li><li>Modifying and creating indexes<\/li><li>Avoiding sorts<\/li><\/ul><p>\nIndex design\n<\/p>\n<ul>\n<li>Approach<\/li><li>Designing indexes<\/li><\/ul><p>\nAdvanced access paths\n<\/p>\n<ul>\n<li>Prefetch<\/li><li>List prefetch<\/li><li>Multiple index access<\/li><li>Runtime adaptive index<\/li><\/ul><p>\nMultiple table access\n<\/p>\n<ul>\n<li>Join methods<\/li><li>Join types<\/li><li>Designing indexes for joins<\/li><li>Predicting table order<\/li><\/ul><p>\nSubqueries\n<\/p>\n<ul>\n<li>Correlated subqueries<\/li><li>Non-correlated subqueries<\/li><li>ORDER BY and FETCH FIRST with subqueries<\/li><li>Global query optimization<\/li><li>Virtual tables<\/li><li>Explain for subqueries<\/li><\/ul><p>\nSet operations (optional)\n<\/p>\n<ul>\n<li>UNION, EXCEPT, and INTERSECT<\/li><li>Rules<\/li><li>More about the set operators<\/li><li>UNION ALL performance improvements<\/li><\/ul><p>\nTable design (optional)\n<\/p>\n<ul>\n<li>Number of tables<\/li><li>Clustering sequence<\/li><li>Denormalization<\/li><li>Materialized query tables (MQTs)<\/li><li>Temporal tables<\/li><li>Archive enabled tables<\/li><\/ul><p>\nWorking with the optimizer\n<\/p>\n<ul>\n<li>Indexable versus non-indexable predicates<\/li><li>Boolean versus non-Boolean predicates<\/li><li>Stage 1 versus stage 2<\/li><li>Filter factors<\/li><li>Helping the optimizer<\/li><li>Pagination<\/li><\/ul><p>\nLocking issues\n<\/p>\n<ul>\n<li>The ACID test<\/li><li>Reasons for serialization<\/li><li>Serialization mechanisms<\/li><li>Transaction locking<\/li><li>Lock promotion, escalation, and avoidance<\/li><\/ul><p>\nMore locking issues (optional)\n<\/p>\n<ul>\n<li>Skip locked data<\/li><li>Currently committed data<\/li><li>Optimistic locking<\/li><li>Hot spots<\/li><li>Application design<\/li><li>Analyzing lock waits<\/li><\/ul><p>\nMassive batch (optional)\n<\/p>\n<ul>\n<li>Batch performance issues<\/li><li>Buffer pool operations<\/li><li>Improving performance<\/li><li>Benefit analysis<\/li><li>Massive deletes<\/li><\/ul>","summary":"<p>This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.<\/p>","objective_plain":"After completing this course, students will be able to:\n\n\n\n- Understand and design better indexes\n- Determine how to work with the optimizer (avoid pitfalls, provide guidence)\n- Optimize multi-table access\n- Work with subqueries\n- Avoid locking problems\n- Use accounting traces and other tools to locate performance problems in existing SQL\n- and more","essentials_plain":"- Familiarity with SQL\n- Familiarity with Db2 12 for z\/OS\n- Familiarity with Db2 12 for z\/OS application programming","audience_plain":"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.","contents_plain":"Introduction to SQL performance and tuning\n\n\n\n- Performance issues\n- Simple example\n- Visualizing the problem\n- Summary\n\nPerformance analysis tools\n\n\n\n- Components of response time\n- Time estimates with VQUBE3\n- SQL EXPLAIN\n- The accounting trace\n- The bubble chart\n- Performance thresholds\n\nIndex basics\n\n\n\n- Indexes\n- Index structure\n- Estimating index I\/Os\n- Clustering index\n- Index page splits\n\nAccess paths\n\n\n\n- Classification\n- Matching versus Screening\n- Variations\n- Hash access\n- Prefetch\n- Caveat\n\nMore on indexes\n\n\n\n- Include index\n- Index on expression\n- Tandom index\n- Partitioned and partitioning, NPSI and DPSI\n- Page range screening\n- Features and limitations\n\nTuning methodology and index cost\n\n\n\n- Methodology\n- Index cost: Disk space\n- Index cost: Maintenance\n- Utilities and indexes\n- Modifying and creating indexes\n- Avoiding sorts\n\nIndex design\n\n\n\n- Approach\n- Designing indexes\n\nAdvanced access paths\n\n\n\n- Prefetch\n- List prefetch\n- Multiple index access\n- Runtime adaptive index\n\nMultiple table access\n\n\n\n- Join methods\n- Join types\n- Designing indexes for joins\n- Predicting table order\n\nSubqueries\n\n\n\n- Correlated subqueries\n- Non-correlated subqueries\n- ORDER BY and FETCH FIRST with subqueries\n- Global query optimization\n- Virtual tables\n- Explain for subqueries\n\nSet operations (optional)\n\n\n\n- UNION, EXCEPT, and INTERSECT\n- Rules\n- More about the set operators\n- UNION ALL performance improvements\n\nTable design (optional)\n\n\n\n- Number of tables\n- Clustering sequence\n- Denormalization\n- Materialized query tables (MQTs)\n- Temporal tables\n- Archive enabled tables\n\nWorking with the optimizer\n\n\n\n- Indexable versus non-indexable predicates\n- Boolean versus non-Boolean predicates\n- Stage 1 versus stage 2\n- Filter factors\n- Helping the optimizer\n- Pagination\n\nLocking issues\n\n\n\n- The ACID test\n- Reasons for serialization\n- Serialization mechanisms\n- Transaction locking\n- Lock promotion, escalation, and avoidance\n\nMore locking issues (optional)\n\n\n\n- Skip locked data\n- Currently committed data\n- Optimistic locking\n- Hot spots\n- Application design\n- Analyzing lock waits\n\nMassive batch (optional)\n\n\n\n- Batch performance issues\n- Buffer pool operations\n- Improving performance\n- Benefit analysis\n- Massive deletes","outline_plain":"Introduction to SQL performance and tuning\n\n\n\n- Performance issues\n- Simple example\n- Visualizing the problem\n- Summary\n\nPerformance analysis tools\n\n\n\n- Components of response time\n- Time estimates with VQUBE3\n- SQL EXPLAIN\n- The accounting trace\n- The bubble chart\n- Performance thresholds\n\nIndex basics\n\n\n\n- Indexes\n- Index structure\n- Estimating index I\/Os\n- Clustering index\n- Index page splits\n\nAccess paths\n\n\n\n- Classification\n- Matching versus Screening\n- Variations\n- Hash access\n- Prefetch\n- Caveat\n\nMore on indexes\n\n\n\n- Include index\n- Index on expression\n- Tandom index\n- Partitioned and partitioning, NPSI and DPSI\n- Page range screening\n- Features and limitations\n\nTuning methodology and index cost\n\n\n\n- Methodology\n- Index cost: Disk space\n- Index cost: Maintenance\n- Utilities and indexes\n- Modifying and creating indexes\n- Avoiding sorts\n\nIndex design\n\n\n\n- Approach\n- Designing indexes\n\nAdvanced access paths\n\n\n\n- Prefetch\n- List prefetch\n- Multiple index access\n- Runtime adaptive index\n\nMultiple table access\n\n\n\n- Join methods\n- Join types\n- Designing indexes for joins\n- Predicting table order\n\nSubqueries\n\n\n\n- Correlated subqueries\n- Non-correlated subqueries\n- ORDER BY and FETCH FIRST with subqueries\n- Global query optimization\n- Virtual tables\n- Explain for subqueries\n\nSet operations (optional)\n\n\n\n- UNION, EXCEPT, and INTERSECT\n- Rules\n- More about the set operators\n- UNION ALL performance improvements\n\nTable design (optional)\n\n\n\n- Number of tables\n- Clustering sequence\n- Denormalization\n- Materialized query tables (MQTs)\n- Temporal tables\n- Archive enabled tables\n\nWorking with the optimizer\n\n\n\n- Indexable versus non-indexable predicates\n- Boolean versus non-Boolean predicates\n- Stage 1 versus stage 2\n- Filter factors\n- Helping the optimizer\n- Pagination\n\nLocking issues\n\n\n\n- The ACID test\n- Reasons for serialization\n- Serialization mechanisms\n- Transaction locking\n- Lock promotion, escalation, and avoidance\n\nMore locking issues (optional)\n\n\n\n- Skip locked data\n- Currently committed data\n- Optimistic locking\n- Hot spots\n- Application design\n- Analyzing lock waits\n\nMassive batch (optional)\n\n\n\n- Batch performance issues\n- Buffer pool operations\n- Improving performance\n- Benefit analysis\n- Massive deletes","summary_plain":"This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.","skill_level":"Beginner","version":"1","duration":{"unit":"d","value":3,"formatted":"3 jours"},"pricelist":{"List Price":{"CH":{"country":"CH","currency":"CHF","taxrate":8.1,"price":2400},"FR":{"country":"FR","currency":"EUR","taxrate":19.6,"price":2250},"DE":{"country":"DE","currency":"EUR","taxrate":19,"price":2550}}},"lastchanged":"2025-07-29T12:17:56+02:00","parenturl":"https:\/\/portal.flane.ch\/swisscom\/fr\/json-courses","nexturl_course_schedule":"https:\/\/portal.flane.ch\/swisscom\/fr\/json-course-schedule\/19868","source_lang":"fr","source":"https:\/\/portal.flane.ch\/swisscom\/fr\/json-course\/ibm-cv964g"}}