- Tel: +44 (0)1275 859666
DB02 - DB2 z/OS SQL Performance and Tuning - 2 Days
Course Description
Customisation
For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.
Course Details
- DB2 OBJECTS
- The Structure Of Db2 Objects
- Database Definition
- Tablespaces
- Tablespace Creation Syntax
- Pages
- Tablespace Organisation
- Page And Row Organisation
- The Simple Tablespace
- The Segmented Tablespace
- The Partitioned Tablespace
- Partition-By-Growth Tablespace
- Partition-By-Range Tablespace
- Maximum Number Of Partitions
- The Lob Tablespace
- Table Definition
- Db2 Column Types
- Design Tips for Columns
- Null Values
- Nulls - Design Tips
- Implicitly Hidden Columns
- Row Change Timestamps
- Partition Management
- Db2 Indexes
- Index Definition
- Create Index Parameters
- Index Organisation - The B Tree Index
- Backwards Index Scan
- Non-Unique Indexes
- Index Clustering
- Clustering With Partitioned Tables
- Clustering Within Partition
- Changing the Clustering Sequence
- Partitioned Indexes
- Creating a DPSI
- Design Considerations - DPSI Problems
- Index On Expression
- Database Definition
- DML PERFORMANCE REVIEW
- Select Statement - Review
- The Where Clause - Review
- Special Operators - Examples
- Sql Built-In Column Functions
- Column Function Performance Notes
- Using 'Distinct'
- Group By Clause
- Expressions / Functions in Group By
- Having Clause
- Order By Clause
- Fetch First 'n' Rows Only Clause
- The Update Statement
- The Delete Statement
- The Insert Statement
- The Merge Statement
- Merge Statement Restrictions
- Select from Insert
- Select from Insert Example
- Select From Insert in a Cursor
- Select From Insert - Order By Option
- Select from Update
- Select from Delete
- Select from Merge
- Scalar Functions
- The Case Statement
- Inner Joins
- Outer Joins
- Join Examples
- Union, Intersect and Except
- Union / Intersect / Except Examples
- Subqueries
- Subqueries Using In
- Exists
- Common Table Expressions
- Common Table Expressions - A Complex Example
- Recursive SQL
- Recursive SQL Example
- Recursive SQL - Controlling Depth of Recursion
- Improved Insert Performance - The Append Parameter
- The Truncate Statement
- Truncate Examples
- Truncate Restrictions
- Utility Performance - Row Placement Rules
- The Where Clause - Review
- PREDICATE PROCESSING
- Predicate Definition
- Predicate Evaluation
- Predicate Evaluation Table
- Predicate Evaluation Table - Notes
- Indexable Predicates
- Stage 1 And Stage 2 Predicates
- Predicate Evaluation Sequence
- Use Of And / Or
- Predicate Evaluation
- RUNSTATS
- The Runstats Utility
- Catalog Statistics Updated By Runstats
- Runstats Considerations
- Runstats - Distribution Statistics
- Runstats - Historical Statistics
- Volatile Tables
- Catalog Statistics Updated By Runstats
- OPTIMIZATION
- The Optimizer
- The Optimize Statement
- Fetch First 'n' Rows Only Clause
- Filter Factors
- Filter Factors With Boolean Operations
- Performance / Cost Estimation
- Example Of Performance / Cost Estimation
- Run-Time Reoptimization
- Optimization Hints
- Planning to use Optimization Hints
- The Optimize Statement
- TUNING
- Db2 Explain
- The Explain Statement
- Plan Table Layout
- Plan Table Column Definitions
- The Dsn_Statemnt_Table
- Explaining the Statement Cache
- Interpreting Explain Output
- Tablespace Scan
- Non-Matching Index Scan
- Matching Index Scan
- Multiple Index Access
- Index Only Access
- Prefetch Processing
- List Sequential Prefetch
- SQL In List Processing - Dynamic Prefetch
- Sequential Detection
- Query Parallelism Techniques
- Enabling Parallelism
- Table Join Methods
- Merge Scan Join
- Nested Loop Join
- Hybrid Join
- Star Join
- Star Join Example
- The Explain Statement