DB2 Data Warehousing Performance And Tuning Workshop
Course: DWHWPT
Duration: 4 Days
Level: II
Course Summary
This presentation and hands-on workshop emphasises how to
optimise data warehouse design in both non-partitioned
and partitioned environments.
Topics central to this presentation include:
- Giga- and Tera-byte table considerations
- Partitioning and parallelism
- I/O design
- High performance and BI queries
- DB/DBM configuration parameters
- How to exploit the DB2 optimizer
- Materialized Views/Materialized Query Tables (MQTs)
- Multidimensional Clustering (MDC)
« Hide The Details
Topics Covered In This Course
REVIEW OF THE BASICS
- Data warehouse vs. data mart
- Data modelling options
- Data mining
- Metadata
- OLAP vs. OLTP
- ETL (Extract, Transform, Load)
- Operational vs. historical data
SYSTEM ENVIRONMENT CONSIDERATIONS
- I/O placement considerations
- Log considerations
- Connection considerations
- Buffer pool considerations
- Locking considerations
- Package cache considerations
- Catalog cache considerations
- Sort considerations
- Other memory considerations
- Miscellaneous considerations
PERFORMANCE AND MONITORING TOOLS
- Primary performance parameters (e.g., DB and DBM CFG)
- Facilities (e.g., DB2 Design Advisor)
- Explain/ Visual Explain
- db2diag.log
- DB2 Admin. Notification log
- Health Monitor and Health Center
APPLICATION DESIGN
- Table design (e.g., sizing)
- Index design
- Table space design
- SQL design
- Locking and concurrency design
- Integrity constraints
DB2 UDB'S MATERIALIZED VIEWS
- Materialized view overview
- Materialized view CREATE considerations
- Materialized view maintenance considerations
- Loading base tables (LOAD utility)
- Materialized view ALTER considerations
- Materialized view DROP considerations
- Materialized view matching considerations
- Materialized view design considerations
- Materialized view tuning considerations
- Refresh optimization
- Materialized view limitations
DB2 UDB ENVIRONMENT CONSIDERATIONS
- Memory
- Buffer pools
- Package cache
- Sorts
- Logging
- I/O placement
- RAID considerations
COMMAND AND UTILITY OPTIONS
- EXPORT
- IMPORT
- LOAD (e.g., MASS LOAD, multipartition)
- REORG
- RESTORE
- RUNSTATS
PARTITIONING AND PERFORMANCE
- Partition scope (i.e., number)
- Partition groups
- Partitioning maps
- Partitioning keys
DB2 OPTIMIZER (COMPILER)
- Query performance
- Inter-partition communication
- Join strategies
- Parallelism
DB2 UDB'S STATISTICS, ANALYTIC, AND OLAP FUNCTIONS
- Statistics and analytic functions
- OLAP functions
MULTIDIMENSIONAL CLUSTERING (MDC)
- Advantages of MDC
- Logical and physical organization of data
- Indexes and performance
- Clusters and indexes
- Issues with single dimension indexes
- Multidimensional clustering (MDC) and its benefits
- Creating MDC tables
- How MDC works logically and physically
- Reading data from an MDC table
- Writing data to an MDC table
- MDC tuning
What You Can Expect
Upon completion of this presentation, the participant should
be able to design, optimise and implement large-scale
data warehouses to meet a variety of BI (Business Intelligence).
Who Should Take This Course
DB2 BI and data warehouse developers, database administrators
and system administrators.
Recommended Prerequisites
Completion of our course DB2 Data Warehouse Fundamentals
or equivalent knowledge.
Training Style
Lecture and hands-on.
« Hide The Details
Related Courses
| Code |
Course Title |
Duration |
Level |
|
DWHASP |
Data Warehousing (DW) – Advanced SQL Queries & Performance |
2 Days |
I |
Details |
ORDWA |
Oracle Database 11g: Administer a Data Warehouse |
5 Days |
II |
Details |
OWHPSQ |
Advanced PL/SQL (and SQL) for Data Warehousing ETL |
4 Days |
III |
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.
|
