Db2 12 for z/OS SQL Performance and Tuning

Db2 12 for z/OS SQL Performance and TuningCV964GIBIBMIB-CV964G1After completing this course, students will be able to: <ul> <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><ul> <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>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.Introduction to SQL performance and tuning <ul> <li>Performance issues</li><li>Simple example</li><li>Visualizing the problem</li><li>Summary</li></ul> Performance analysis tools <ul> <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> Index basics <ul> <li>Indexes</li><li>Index structure</li><li>Estimating index I/Os</li><li>Clustering index</li><li>Index page splits</li></ul> Access paths <ul> <li>Classification</li><li>Matching versus Screening</li><li>Variations</li><li>Hash access</li><li>Prefetch</li><li>Caveat</li></ul> More on indexes <ul> <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> Tuning methodology and index cost <ul> <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> Index design <ul> <li>Approach</li><li>Designing indexes</li></ul> Advanced access paths <ul> <li>Prefetch</li><li>List prefetch</li><li>Multiple index access</li><li>Runtime adaptive index</li></ul> Multiple table access <ul> <li>Join methods</li><li>Join types</li><li>Designing indexes for joins</li><li>Predicting table order</li></ul> Subqueries <ul> <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> Set operations (optional) <ul> <li>UNION, EXCEPT, and INTERSECT</li><li>Rules</li><li>More about the set operators</li><li>UNION ALL performance improvements</li></ul> Table design (optional) <ul> <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> Working with the optimizer <ul> <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> Locking issues <ul> <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> More locking issues (optional) <ul> <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> Massive batch (optional) <ul> <li>Batch performance issues</li><li>Buffer pool operations</li><li>Improving performance</li><li>Benefit analysis</li><li>Massive deletes</li></ul>Introduction to SQL performance and tuning <ul> <li>Performance issues</li><li>Simple example</li><li>Visualizing the problem</li><li>Summary</li></ul> Performance analysis tools <ul> <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> Index basics <ul> <li>Indexes</li><li>Index structure</li><li>Estimating index I/Os</li><li>Clustering index</li><li>Index page splits</li></ul> Access paths <ul> <li>Classification</li><li>Matching versus Screening</li><li>Variations</li><li>Hash access</li><li>Prefetch</li><li>Caveat</li></ul> More on indexes <ul> <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> Tuning methodology and index cost <ul> <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> Index design <ul> <li>Approach</li><li>Designing indexes</li></ul> Advanced access paths <ul> <li>Prefetch</li><li>List prefetch</li><li>Multiple index access</li><li>Runtime adaptive index</li></ul> Multiple table access <ul> <li>Join methods</li><li>Join types</li><li>Designing indexes for joins</li><li>Predicting table order</li></ul> Subqueries <ul> <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> Set operations (optional) <ul> <li>UNION, EXCEPT, and INTERSECT</li><li>Rules</li><li>More about the set operators</li><li>UNION ALL performance improvements</li></ul> Table design (optional) <ul> <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> Working with the optimizer <ul> <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> Locking issues <ul> <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> More locking issues (optional) <ul> <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> Massive batch (optional) <ul> <li>Batch performance issues</li><li>Buffer pool operations</li><li>Improving performance</li><li>Benefit analysis</li><li>Massive deletes</li></ul>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- Familiarity with SQL - Familiarity with Db2 12 for z/OS - Familiarity with Db2 12 for z/OS application programmingThis 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.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 - Tandom 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 deletesIntroduction 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 - Tandom 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 deletes3 jours2400.002250.002550.00