DB2 UDB/390 Performance and Tuning
Course: DB2PT
Duration: 3 Days
Level: I
On our website at:
http://www.verhoef-training.com/courses/DB2PT.html
Course Summary
The Course is designed to instruct those delegates how to develop and maintain existing applications in an efficient manner, up to and including DB2 10.
The course will look at existing SQL to see if it is performing poorly, and to identify why it could perform poorly, and how to reconstruct so that it could perform better.
The course will contain practical exercises to consolidate the theory sessions.
Topics Covered In This Course
Introduction to DB2 versions
System architecture & started tasks
Stage 1/ Stage 2 predicates
Data transfer
Storage
- Database including default in DB2 9
- Tablespace
- Universal, Simple, Segmented, Partitioned
- Non logged table spaces
- Tables
- Cloning Tables
- Reordered Row Format
- Data types including XML BIGINT DECFLOAT
- Implicitly Hidden Columns
UDT
VIEWS
MQT
Catalog(Including changes in 10)
HISTORY TABLES
Directory
Advanced SQL
- Sorting
- GROUP BY, ORDER BY, DISTINCT
- How to detect and how to avoid
- Small Sorts
- Consolidation of functions
- OPTIMIZE FOR
- Skip Lock Data
- Access to Committed data
- FETCH only
- Table joins
- SUBSELECTS
- Correlated SUBSELECTS
- UNIONS
- Vs 4 Joins
- Nested tables
- CTE's
- EXCEPT/INTERSECT keyword
- TRUNCATE TABLE statement
- MERGE and SELECT FROM MERGE statements
- INSERT performance improvements DB2 10
Indexes and performance
- Page
- Record identifier
- Indexes
- Type 2
- Splits
- Indexes version 9 changes
- Larger Index Page Sizes
- Last Used Index Flag
- Index Key Randomization
- Index Compression
- Index On Expression
- Index Page Split
- Index Lookaside
- Member Cluster
Cluster Ratio
Explain & Filter factor
- Optimizer
- EXPLAIN
- Catalog statistics
- Single column/ Multiple columns
Filter factor
Visual Explain
Optimization Center
Administration Tools
DSN_STATEMENT TABLE
Third party tools
Access paths
- Single table access
- Multiple table access
- Types of 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
- Skip Locked Data
- Isolation Level
Packages/Plans
- Terms/terminology
- BIND OPTIONS
- Data structures
Triggers
Stored procedures
Consolidation of Version 8/9/10 differences
CTE's and Recursive SQL
XML
- Introduction
- Objective Creation
- Xpath Functions
- Performance Issues
- XML Version 10
Recommended Prerequisites
Previous DB2 knowledge is required.
Related Courses
Code |
Course Title |
Duration |
Level |
|
DB2AU |
DB2 Administration for Linux, Unix and Windows |
5 Days |
I |
Details |
DB2M |
Advanced DB2 Application Programming |
3 Days |
II |
Details |
DB2M9 |
DB2 UDB/390 Version 9 Transition |
2 Days |
II |
Details |
Every student attending a Verhoef Training class will receive
a certificate good for $100 toward their next public class taken
within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a
single student in any of our public or web-based classes.
Contact your account manager or our sales office for details.