Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

DB2 Data Warehousing Performance And Tuning Workshop

Course:   DWHWPT
Duration:   4 Days
Level:   II
On our website at:   http://www.verhoef-training.com/courses/DWHWPT.html
 
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:

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.

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.