DB2 Performance and Tuning for UNIX and Windows
Course: DB2PTUW
Duration: 3 Days
Level: II
On our website at:
http://www.verhoef-training.com/courses/DB2PTUW.html
Course Summary
This course is designed to review many aspects of performance and tuning of DB2 version 9.7, for Linux, UNIX and Windows (LUW). DB2 commands and tools created by IBM for DB2, will be used to support workshop activities. Performance and tuning varies with type of user to be supported. We will review this topic for support of SQL users, programmers, data designers and database administrators.
Topics Covered In This Course
Creating tables and tablespaces
- Review concepts of SMS and DMS tablespaces
- show table options like identity columns and generated columns
- introduction to data row compression
- basic design using data range partitioning
Advanced SQL techniques
- how to use different outer join techniques
- using OLAP functions
- review Nested and common table expressions
- using recursion with common table expressions
SQL performance
- Hints and tips for effectively using SQL
Data Access and EXPLAIN
- Review of Data access techniques
- How to use various explain tools and interpret results
The DB2 Optimizer
- Review of strategy to improve optimization strategy
- Design of indexes for optimum performance
Event and snapshot Monitors
- How to use event monitors
- options for creating and using event monitors
Multi Dimensional Clustering (MDC)
- the terminology of multi dimensional data.
- Know why and how to create a table that is clustered in multiple dimensions.
- Create generated columns to be used for dimension columns.
- Understand the concept of monotonicity associated with generated dimension columns.
Advanced table options
- Using table range partitions
- table row compression
- advanced options for REORG, RUNSTATS utilities
- Supporting identity columns in utilities
Configuring for self tuning of DB2
- automatic maintenance with utilities
- Self tuning memory
- database global memory
- automatic tuning
DB2 Operational Model
- Review the architectural concepts for DB2
- look at the behavior of DB2, with respect to I/O operations
- identify important cache and monitoring elements
The Health Monitor and Activity Monitor
- Using commands and the Health Center to monitor DB2 ???health???
- Monitor DB2 memory resource usage with the Activity Monitor
- Various tools, like DB2PD will be used to monitor memory utilization
- Analyzing performance with snapshot functions and administrative views
DB2 Configuration Parameters
- review important configuration parameters for the a database and the database manager
- Introduce auto configuration
- identify the concepts of the Design Advisor
What You Can Expect
After completing this course the student should be able to:
- Code SQL to improve performance.
- Use various DB2 EXPLAIN tools to evaluate performance expectations.
- Design application programs to improve performance and review with EXPLAIN tools.
- Use the tool DB2BATCH to benchmark applications.
- Use various tools and snapshot functions and administrative views to evaluate DB2 performance and operational behaviour of applications
- Improve performance through parallelism
- Review data placement to improve performance.
- Understand row compression and range partitioning on tables to improve performance
- Consider Multi-Dimensional Clustering as a design option, by analysing data and application needs.
- Tune registry variables and configuration parameters to effect performance.
- Use DB2 monitor tools to assist in performance tuning.
- Use performance improving options for DB2 utilities
- Configure DB2 for self tuning
- Use advanced features of utilities including support for identity columns.
- Set DB2 for automatic maintenance.
- Use and analyse benefits of advanced table design like row compression.
Who Should Take This Course
Database administrators and those senior support persons, who are responsible for tuning DB2 for performance or analyzing application performance.
Recommended Prerequisites
Relevant Operating Systems experience (Linux, UNIX or Windows) along with knowledge of relational database theory and SQL. Previous experience of using DB2 that includes either some database administration or application support.
Related Courses
| Code |
Course Title |
Duration |
Level |
|
DB2UDBI |
DB2 for LUW (Linux, UNIX, Windows) |
2 Days |
I |
Details |
DB2LUW |
DB2 LUW (Linux, UNIX, Windows) FOR PROGRAMMERS |
4 Days |
I |
Details |
DB2U |
DB2 SQL Application Programming |
5 Days |
I |
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.
