Course #:WA1872 DB2 for z/OS Application Performance & Tuning Training Review overall DB2 SQL Performance Considerations. Discuss the issues in Performance “problems”. Discuss the "behavior" or “mis-behavior” of the DB2 Optimizer. Explore DB2 SQL performance issues as revealed via the EXPLAIN Plan / VISUAL EXPLAIN feature. The major columns PLAN_TABLE and DSN_STATEMNT_TABLE are analyzed in detail. The results of the DB2 EXPLAIN Plan / VISUAL EXPLAIN process are reviewed to obtain insights into the Way(s) that the Optimizer chooses the access path to access DB2 data. Extensive workshops allow the students to test the performance “goodness” of several SQL statements. The Performance Guidelines are applied to COBOL Application programs. Discuss how & when the Optimizer uses Filter Factors based on Valid Statistics & Defaults What you will learn After completing this course, the student should be able to: Discuss overall DB2 Performance considerations Discuss the “tuning tools” of EXPLAIN Plan / VISUAL EXPLAIN and analyze results to analyze SQL statement and examine the results Analyze the column values in the PLAN_TABLE and the DSN_STATEMNT_TABLE Review how to Tune / Rewrite SQL queries to improve their efficiency Evaluate the efficiency of “alternative” SQL statements that product the same result sets Develop a set of Performance “guidelines” Discuss various Predicate Types and heir impact on performance Discuss the basic & intermediate access paths and how they impact performance Analyze Multi-Table and other “complex” access paths Review the various DB2 Catalog Statistics that are used by the DB2 optimizer Review different Access Path Selection Apply tuning guidelines to application programs – REOPT(VARS), Optimize for N rows, Dynamic SQL, Cursor processing Discuss the use of the following tools – VQUBE, Accounting Trace, Performance Monitors, Explain Plan / Visual Explain, or similar tool (e.g. IBM’s Performance Analyzer) Audience This course is for experienced Data Processing personnel who have used SQL and need to explore Application Performance and Tuning issues for BOTH standalone SQL and in COBOL Application programs. Prerequisites At least six months experience using DB2 SQL and COBOL Programming experience is needed. Duration Two Days. Outline of DB2 for z/OS Application Performance & Tuning Training Day 1 DB2 SQL Performance Introduction What makes up the Major elements of DB2 SQL Performance? What “causes” DB2 SQL Performance Problems? How can we “improve” DB2 SQL Performance? Filter Factors Indexes are NOT free & How to determine their cost Performance Overview with Guidelines Workshop - EXPLAIN Plan / VISUAL EXPLAIN and analyze results Stage 1 and Stage 2 Predicates Tuning Guidelines and Examples EXPLAIN Plan / VISUAL EXPLAIN with Examples – TS, MIS, NMIS, IXONLY, SORT, Part Scan WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 1 SQL RE-Coding Guidelines WorkShop – Re-Formatting SQL Statements Using EXPLAIN Plan / VISUAL EXPLAIN to improve SQL Performance WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 2 The DSN_STATEMNT_TABLE Improving SQL statements using EXPLAIN Plan / VISUAL EXPLAIN results WorkShop - EXPLAIN Plan / VISUAL EXPLAIN - DSN_STATEMNT_TABLE / PLAN_TABLE Day 2 Join Performance Analysis - The 3 Join Methods, Influencing the Join Method???? WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –Join Performance Analysis SubQuery Analysis - Single Valued, Multiple Valued, Correlated WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –SubQuery Performance Analysis UNION Analysis - The “Good“ and the “Bad“ of UNION WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –UNION Performance Analysis Application Program Performance Analysis – REOPT, Optimize for N rows, Dynamic SQL, PK & FK, Prefetch, Correlated Columns, Massive Batch Jobs, Massive Online Jobs VQUBE – Very Quick Upper Bound Estimate WorkShop - EXPLAIN Plan / VISUAL EXPLAIN – COBOL Program Performance Analysis Accounting Trace Performance Monitors Visual Explain, Performance Analyzer We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC. View Course Outline Share This Request On-Site or Customized Course Info REGISTER FOR A COURSEWARE SAMPLE x Sent First Name Last Name Email Request On-Site or Customized Course Info x Sent First Name Last Name Phone Number Company Name Email Question
Course #:WA1872 DB2 for z/OS Application Performance & Tuning Training Review overall DB2 SQL Performance Considerations. Discuss the issues in Performance “problems”. Discuss the "behavior" or “mis-behavior” of the DB2 Optimizer. Explore DB2 SQL performance issues as revealed via the EXPLAIN Plan / VISUAL EXPLAIN feature. The major columns PLAN_TABLE and DSN_STATEMNT_TABLE are analyzed in detail. The results of the DB2 EXPLAIN Plan / VISUAL EXPLAIN process are reviewed to obtain insights into the Way(s) that the Optimizer chooses the access path to access DB2 data. Extensive workshops allow the students to test the performance “goodness” of several SQL statements. The Performance Guidelines are applied to COBOL Application programs. Discuss how & when the Optimizer uses Filter Factors based on Valid Statistics & Defaults What you will learn After completing this course, the student should be able to: Discuss overall DB2 Performance considerations Discuss the “tuning tools” of EXPLAIN Plan / VISUAL EXPLAIN and analyze results to analyze SQL statement and examine the results Analyze the column values in the PLAN_TABLE and the DSN_STATEMNT_TABLE Review how to Tune / Rewrite SQL queries to improve their efficiency Evaluate the efficiency of “alternative” SQL statements that product the same result sets Develop a set of Performance “guidelines” Discuss various Predicate Types and heir impact on performance Discuss the basic & intermediate access paths and how they impact performance Analyze Multi-Table and other “complex” access paths Review the various DB2 Catalog Statistics that are used by the DB2 optimizer Review different Access Path Selection Apply tuning guidelines to application programs – REOPT(VARS), Optimize for N rows, Dynamic SQL, Cursor processing Discuss the use of the following tools – VQUBE, Accounting Trace, Performance Monitors, Explain Plan / Visual Explain, or similar tool (e.g. IBM’s Performance Analyzer) Audience This course is for experienced Data Processing personnel who have used SQL and need to explore Application Performance and Tuning issues for BOTH standalone SQL and in COBOL Application programs. Prerequisites At least six months experience using DB2 SQL and COBOL Programming experience is needed. Duration Two Days. Outline of DB2 for z/OS Application Performance & Tuning Training Day 1 DB2 SQL Performance Introduction What makes up the Major elements of DB2 SQL Performance? What “causes” DB2 SQL Performance Problems? How can we “improve” DB2 SQL Performance? Filter Factors Indexes are NOT free & How to determine their cost Performance Overview with Guidelines Workshop - EXPLAIN Plan / VISUAL EXPLAIN and analyze results Stage 1 and Stage 2 Predicates Tuning Guidelines and Examples EXPLAIN Plan / VISUAL EXPLAIN with Examples – TS, MIS, NMIS, IXONLY, SORT, Part Scan WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 1 SQL RE-Coding Guidelines WorkShop – Re-Formatting SQL Statements Using EXPLAIN Plan / VISUAL EXPLAIN to improve SQL Performance WorkShop - More EXPLAIN Plan / VISUAL EXPLAIN exercises - Part 2 The DSN_STATEMNT_TABLE Improving SQL statements using EXPLAIN Plan / VISUAL EXPLAIN results WorkShop - EXPLAIN Plan / VISUAL EXPLAIN - DSN_STATEMNT_TABLE / PLAN_TABLE Day 2 Join Performance Analysis - The 3 Join Methods, Influencing the Join Method???? WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –Join Performance Analysis SubQuery Analysis - Single Valued, Multiple Valued, Correlated WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –SubQuery Performance Analysis UNION Analysis - The “Good“ and the “Bad“ of UNION WorkShop - EXPLAIN Plan / VISUAL EXPLAIN –UNION Performance Analysis Application Program Performance Analysis – REOPT, Optimize for N rows, Dynamic SQL, PK & FK, Prefetch, Correlated Columns, Massive Batch Jobs, Massive Online Jobs VQUBE – Very Quick Upper Bound Estimate WorkShop - EXPLAIN Plan / VISUAL EXPLAIN – COBOL Program Performance Analysis Accounting Trace Performance Monitors Visual Explain, Performance Analyzer We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC. View Course Outline Share This Request On-Site or Customized Course Info REGISTER FOR A COURSEWARE SAMPLE x Sent First Name Last Name Email Request On-Site or Customized Course Info x Sent First Name Last Name Phone Number Company Name Email Question
