DB2 11 for z OS Application Performance and Tuning CV963G

This intermediate course is designed for DB2 for z/OS application developers, DB2 for z/OS DBAs, and anyone else who is responsible for application performance and tuning in a DB2 for z/OS environment.

• Design better indexes
• Determine how to live with the optimizer (avoid pitfalls, help when necessary)
• Avoid locking problems
• Use accounting trace information to find significant performance problems in an operational application

You should have:

• Familiarity with DB2 for z/OS application programming and SQL.

• Introduction to Application Performance and Tuning
• List common causes of application performance problems
• Evalutate different approaches for detecting the problems
• Describe possible solutions
• Performance Analysis Tools
• Understand components of local response time (LRT)
• Identify touch random (TR), touch sequential (TS), and fetch (F) time costs
• Utilize VQUBE3 to estimate local response time (LRT)
• Locate necessary time values in an accounting trace report
• Draw and interpret a bubble chart
• Towards Better Indexes
• Understand DB2 index structure and usage
• Evalute the cost of creating a new index or modifying an existing index
• Design the best possible index for a single table query
• Describe prefetch operations and multi-index access
• Multiple Table Access
• Identify various join methods and join types
• Predict table join order
• Design the best indexes for joining tables
• Optimmize correlated and non-correlated subqueries
• Utilize UNION, INTERSECT, and EXCEPT operations
• Towards Better Tables
• Evaluate clustering alternatives
• Understand basic rules of nomalization
• Consider conditions for denormalization
• Define materialized query tables
• Learning to Live with the Optimizer
• Describe the limitations related to dangerous predicates
• Identify situations when the optimizer needs help with filter factor estimates
• Massive Batch
• Detect performance problems with massive batch jobs
• Make batch jobs run faster
• Locking Issues
• Describe DB2 serialization
• Understand transaction locking
• Avoid locking problems in application design
• Course Summary
• Summarize the topics covered in this course