Fall DB2 Seminar 2006

Monday, September 25, 2006

Tuesday, September 26, 2006

Wednesday September 27, 2006

Click Here to Register

2006 Fall Seminar Attendee Area

Design Patterns: Data Access Strategies for Performance

Tink Tysor:

 The Design Patterns class opens with an identification of data access patterns. Performance starts with knowledge of your data: lifespan, cardinality, constraints, etc. Performance is also dependent upon understanding the consumers of the data and how the data is accessed. By understanding your data and what DB2 relies upon, you can remove inhibitors to high performance access. Learn how to use the key features of DB2 to speed access to your data, improve concurrency and make your SQL queries zoom. This class does not cover data modeling but does cover all the options of physical database design.
 
Topics:

Performance

Performance by Design

  1. How Can We Affect It ?
  2. Where Can We Tweak?
  3. How Did We Get Here?
  4. Do You Know This Man?
  5. Data and Databases

Codd’s 12 Rules Abridged

  1. Relational Technology
  2. The Evolution of Abstraction
  3. Evolution of Program Abstraction
  4. Evolution of Data Abstraction
  5. Current Use of Data Abstraction

E.F. Codd Highlights & Key Issues

  1. Data Dependencies
  2. Indexing Dependence
  3. Ordering Dependence
  4. Access Path Dependence
  5. Views and Order Dependence

What Matters         

  1. Data Matters
  2. Visualizing Your Data
  3. Origins of Data Visualization
  4. Buffer Pool Hit/Miss
  5. Sync I/O%
  6. Sync I/O Normalized to Getpage
  7. Index Column Counts DB2A
  8. VPath Distribution
  9. I/O Rank Distribution

Data Warehousing Principles

  1. Data Warehouse Principles - Inmon
  2. Data Warehouse vs. OLTP
  3. Data Warehouse vs. Data Mart
  4. Dimensional Data Warehouse Fundamentals
  5. The Fact Table
  6. The Dimension Tables
  7. Know Thy Data!

 

Patterns

Why Patterns?

Patterns for Data Access

  1. The Online Transaction Pattern
  2. The Web Pattern
  3. The Batch Pattern
  4. The Reporting Pattern
  5. The Data Warehouse Pattern

Patterns for Data

  1. Strata Pattern I
  2. Page Density I
  3. Strata Pattern II
  4. Audit Pattern
  5. Distributed Replication
  6. Multiple Read Replication

Data Warehouse Pattern

  1. Data Warehouse Partitioned
  2. Data Design Patterns
  3. “Locking”
  4. Strata
  5. Affinity
  6. Data Partitioning
  7. TPC-H
  8. Typical DW / BI

Infrastructure Impact

Interrelationship of Domains and Views

  1. Relationship of Domains
  2. Domains as Views

Set Notation - INTERSECT

  1. Associations
  2. Hierarchies – External
  3. Clustering and Secondary Indices
  4. Temporal Databases

Data Types Matter

  1. Primitive Data Types
  2. Dates in TIMESTAMPS
  3. CUST_ID in Subsystem 1
  4. CUST_ID in Subsystem 2

Layered Views

  1. Views Can Change
  2. Base Tables Can Change

Simple Sets

  1. Redundant SQL – “Normalization”
  2. Data Point
    Normalization / Categorization
  3. Data Point Normalization
  4. Data Model Inflexibility

Time and Relations

  1. Degree of Relations
  2. Time-varying relationships
  3. Relations, Time and History

Relations?

  1. Who Needs Them?
  2. Dumb, Simple SQL Join
  3. Do Foreign Key Constraints Matter?
  4. What Does An Primary Index Buy Us?
  5. What Does A Foreign Key Constraint Buy Us?
  6. What Does a Secondary Key Buy Us?
  7. Where Does It Matter?

UNION ALL View Impact

  1. Constraint Definition (CHECK)
  2. Constraint Definition (RI)
  3. Checking and Enforcing Constraints
  4. Bulk Data Checking - SET INTEGRITY
  5. Informational Check Constraint
  6. Enforcing Validation
  7. Informational RI Constraint

Origins of Temporal Data and Databases

  1. Time-varying relationships
  2. Relations, Time and History
  3. Temporal Databases
  4. Point-In-Time Backup
  5. Temporal Semantics

Physical Impacts

System Impacts

  1. Buffer Pool
  2. Cache Thrash
  3. Total Buffer Pools

Typical Schema & Index Design

Optimal Schema & Index Design

Exploiting Constraints For Query Optimization

  1. Exploiting RI for query Optimization
  2. UNION ALL branch elimination
  3. Exploiting RI for query Optimization
  4. Group By Pushdown Through RI Joins
  5. Fetch First n Row (Truncated Sort) Pushdown
  6. Exploiting RI When Matching MQTs
  7. Redundant Join Elimination     

Application Considerations

  1. Query Versus Program
  2. Coding Practices
  3. Improper Application of SQL Features
  4. Between Clauses for DB2 z/OS
  5. Mismatch Data Type
  6. Over Use of LEFT JOIN
  7. Practical Uses of GLOBAL TEMPs
  8. Misuse of GLOBAL TEMP TABLES

Manual Tuning through Query Rewrite A Typical Data Warehouse Query

  1. Before and After Manual Tuning
  2. Tuning Technique - X2QBOpt
  3. Infrastructure Improvements

Non-Optimal Index Design

  1. Four Points of Filtering

  2. Variations of Access Techniques

    1. DB2 z/OS Favorite Access: List Prefetch
    2. DB2 z/OS Favorite Join: Nested Loop Join
    3. DB2 LUW Favorite Access - Table Scan
    4. DB2 LUW Favorite Join - Hash Join

  3. Optimizing The Index Design

    1. Choose the Workload
    2. Map It By Table
    3. Research Frequencies
    4. Research Priorities
    5. Determining Weight
    6. Final Ranking
    7. Grand Total
    8. Map It Deeper

  4. Materialized Query Tables

    1. To Build or not to Build
    2. Table Pivoting Syntax
    3. MQT Solution
    4. Query Speed Up

Conclusion - Physical environment impacts workload performance

 


 

 

 

 

DB2 V8 for z/OS Transition for Developers (z/OS)

 

Get the ins, outs, and implementation information regarding the new features within DB2 V8 for z/OS. Learn about the advantages and potential impact of these new features as they relate to Application-oriented topics.

 

How top do a DB2 z/OS Performance Review

The How to do a DB2 Performance Review class will discuss the research, process and impact of doing a performance review of your DB2 environments, systems, and applications.  An overall system, architectural, database and performance tuning review can dramatically reduce costs and improve system and application performance, availability and scalability.  By analyzing many processing performance conditions and detailing various recommendations you can quickly improve database application performance, throughput and response time.  This class will detail activities to evaluate production z/OS, UNIX and Windows DB2 environments. This class will detail problem areas and the improvements that can be made in system configurations, database designs and batch, online and Websphere applications.  These techniques at many consulting clients have dramatically reduced daily CPU consumption and saved clients thousands and sometimes millions of dollars in CPU costs.

Topics:

Chapter 1:  DB2 Performance Review Process Overview

  1. Architecture review
  2. Systems, applications, processes
  3. System Components
  4. DB2 z/OS & LUW processes
  5. Applications through the ages
  6. Batch Characteristics
  7. Online Characteristics
  8. Distributed Java Characteristics

Chapter 2:  DB2 Component Architectures

  1. DB2 System processes
  2. Cache Components
  3. Data Storage Architectures

Chapter 3:  System Performance Characteristics

  1. CPU Usage
  2. I/O Types/Characteristics
  3. Locking Considerations

Chapter 4:  System Settings & Configurations

  1. Performance Parameters
  2. z/OS unique advantages
  3. LUW unique settings

Chapter 5:  Database Performance Review

  1. Tablespace Review
  2. Design Options
  3. Answer Optimization
  4. Aggregates Design

Chapter 6:  Tablespace Definitions

  1. Tablespace Page Considerations
  2. Tablespace Types
  3. Partitioning Alternatives
  4. Multi-Dimensional Cluster MDC
  5. Index Keys Considerations
  6. Key Designs for Partitioning

Chapter 7:  Application Performance Keys

  1. Batch Characteristics
  2. Online Profiles
  3. Distributed Java applications
  4. Index Keys Considerations      
  5. Key Designs for Partitioning

Chapter 8:  SQL access Paths, UNION ALL Views and MQTs

  1. SQL Access Paths Overview
  2. LUW Partitioning         
  3. UNION ALL Views       
  4. Leveraging LUW partitioning     
  5. Leveraging MQTs        

Figure 1: DB2 z/OS Architecture & Memory Model

Figure 2: DB2 LUW Architecture & Memory Model 

Figure 3: SQL Access Path Chart