{"course":{"productid":19868,"modality":6,"active":true,"language":"en","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","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>This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.<\/p>","outline":"<p>Introduction to SQL performance and tuning&bull; Performance issues&bull; Simple example&bull; Visualizing the problem&bull; SummaryPerformance analysis tools&bull; Components of response time&bull; Time estimates with VQUBE3&bull; SQL EXPLAIN&bull; The accounting trace&bull; The bubble chart&bull; Performance thresholdsIndex basics&bull; Indexes&bull; Index structure&bull; Estimating index I\/Os&bull; Clustering index&bull; Index page splitsAccess paths&bull; Classification&bull; Matching versus Screening&bull; Variations&bull; Hash access&bull; Prefetch&bull; CaveatMore on indexes&bull; Include index&bull; Index on expression&bull; Random index&bull; Partitioned and partitioning, NPSI and DPSI&bull; Page range screening&bull; Features and limitationsTuning methodology and index cost&bull; Methodology&bull; Index cost: Disk space&bull; Index cost: Maintenance&bull; Utilities and indexes&bull; Modifying and creating indexes&bull; Avoiding sortsIndex design&bull; Approach&bull; Designing indexesAdvanced access paths&bull; Prefetch&bull; List prefetch&bull; Multiple index access&bull; Runtime adaptive indexMultiple table access&bull; Join methods&bull; Join types&bull; Designing indexes for joins&bull; Predicting table orderSubqueries&bull; Correlated subqueries&bull; Non-correlated subqueries&bull; ORDER BY and FETCH FIRST with subqueries&bull; Global query optimization&bull; Virtual tables&bull; Explain for subqueriesSet operations (optional)&bull; UNION, EXCEPT, and INTERSECT&bull; Rules&bull; More about the set operators&bull; UNION ALL performance improvementsTable design (optional)&bull; Number of tables&bull; Clustering sequence&bull; Denormalization&bull; Materialized query tables (MQTs)&bull; Temporal tables&bull; Archive enabled tablesWorking with the optimizer&bull; Indexable versus non-indexable predicates&bull; Boolean versus non-Boolean predicates&bull; Stage 1 versus stage 2&bull; Filter factors&bull; Helping the optimizer&bull; PaginationLocking issues&bull; The ACID test&bull; Reasons for serialization&bull; Serialization mechanisms&bull; Transaction locking&bull; Lock promotion, escalation, and avoidanceMore locking issues (optional)&bull; Skip locked data&bull; Currently committed data&bull; Optimistic locking&bull; Hot spots&bull; Application design&bull; Analyzing lock waitsMassive batch (optional)&bull; Batch performance issues&bull; Buffer pool operations&bull; Improving performance&bull; Benefit analysis&bull; Massive deletes<\/p>","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":"This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.","outline_plain":"Introduction to SQL performance and tuning\u2022 Performance issues\u2022 Simple example\u2022 Visualizing the problem\u2022 SummaryPerformance analysis tools\u2022 Components of response time\u2022 Time estimates with VQUBE3\u2022 SQL EXPLAIN\u2022 The accounting trace\u2022 The bubble chart\u2022 Performance thresholdsIndex basics\u2022 Indexes\u2022 Index structure\u2022 Estimating index I\/Os\u2022 Clustering index\u2022 Index page splitsAccess paths\u2022 Classification\u2022 Matching versus Screening\u2022 Variations\u2022 Hash access\u2022 Prefetch\u2022 CaveatMore on indexes\u2022 Include index\u2022 Index on expression\u2022 Random index\u2022 Partitioned and partitioning, NPSI and DPSI\u2022 Page range screening\u2022 Features and limitationsTuning methodology and index cost\u2022 Methodology\u2022 Index cost: Disk space\u2022 Index cost: Maintenance\u2022 Utilities and indexes\u2022 Modifying and creating indexes\u2022 Avoiding sortsIndex design\u2022 Approach\u2022 Designing indexesAdvanced access paths\u2022 Prefetch\u2022 List prefetch\u2022 Multiple index access\u2022 Runtime adaptive indexMultiple table access\u2022 Join methods\u2022 Join types\u2022 Designing indexes for joins\u2022 Predicting table orderSubqueries\u2022 Correlated subqueries\u2022 Non-correlated subqueries\u2022 ORDER BY and FETCH FIRST with subqueries\u2022 Global query optimization\u2022 Virtual tables\u2022 Explain for subqueriesSet operations (optional)\u2022 UNION, EXCEPT, and INTERSECT\u2022 Rules\u2022 More about the set operators\u2022 UNION ALL performance improvementsTable design (optional)\u2022 Number of tables\u2022 Clustering sequence\u2022 Denormalization\u2022 Materialized query tables (MQTs)\u2022 Temporal tables\u2022 Archive enabled tablesWorking with the optimizer\u2022 Indexable versus non-indexable predicates\u2022 Boolean versus non-Boolean predicates\u2022 Stage 1 versus stage 2\u2022 Filter factors\u2022 Helping the optimizer\u2022 PaginationLocking issues\u2022 The ACID test\u2022 Reasons for serialization\u2022 Serialization mechanisms\u2022 Transaction locking\u2022 Lock promotion, escalation, and avoidanceMore locking issues (optional)\u2022 Skip locked data\u2022 Currently committed data\u2022 Optimistic locking\u2022 Hot spots\u2022 Application design\u2022 Analyzing lock waitsMassive batch (optional)\u2022 Batch performance issues\u2022 Buffer pool operations\u2022 Improving performance\u2022 Benefit analysis\u2022 Massive deletes","skill_level":"Beginner","version":"1","duration":{"unit":"d","value":3,"formatted":"3 days"},"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\/en\/json-courses","nexturl_course_schedule":"https:\/\/portal.flane.ch\/swisscom\/en\/json-course-schedule\/19868","source_lang":"en","source":"https:\/\/portal.flane.ch\/swisscom\/en\/json-course\/ibm-cv964g"}}