DB2 for z/OS SQL Performance and Tuning

Course Overview

The course has been designed, to consolidate on the students knowledge of SQL, understand all aspects of EXPLAIN, be aware of efficiency, and to understand the new facilities available up to UDB/390 version 8. The course will contain numerous practical exercises to consolidate the theory.

Audience

IT personnel and developers who have been exposed to using DB2 under z/OS and wish to understand more of the SQL performance and tuning aspects of the product.

Prerequisites

Those attending the course should have used DB2 in the past, and preferably attended our DB2 SQL Programming course.

Course Outline

• System architecture
• DBA
• EDM pool
• Data transfer
• Storage
• Tablespace
• Global Tables
• Tables
• Log Manager
• Traces
• Catalog and History Tables
• Directory
• Advanced SQL
• Coding efficient SQL
• Scalar functions
• DATE/TIME functions
• Column functions
• Special registers
• FETCH only
• CASE
• Table joins
• SUBSELECTS
• Correlated SUBSELECTS
• UNIONS
• Joins
• Nested tables
• Indexes and performance
• Page
• Record identifier
• Indexes
• Type1/2
• Splits
• Explain & Filter factor
• Optimizer
• EXPLAIN
• DSN_STATEMNT_TABLE
• Catalog statistics
• Single column/ Multiple columns
• Filter factor
• Access paths
• Single table access
• Multiple table access
• Types of access paths
• Coding efficient SQL
• Data Handling
• Modification of SQL by DB2
• Coding additional predicates
• Using indexes
• Coding efficient Joins
• SUBQUERIES
• VIEWS
• PLAN analysis
• Influencing Access Paths
• Online Performance Guidelines
• Coding techniques
• Efficient Browse
• Multiple columns indexes
• Online updates
• Batch performance guidelines
• Locking & Performance
• Understanding locking
• Contention
• Locking in a shared environment
• Global deadlocks
• Packages/Plans
• Terms/terminology
• BIND OPTIONS
• Data structures
• Triggers
• Stored Proceedures

DB2 for z/OS SQL Performance and Tuning / DB2PSQ (DB2PSQGB)