IB-Learning: practical, hands-on training for software developers, software testers, business analysts and project managers

Spotlight On

• Curriculums
  • Business Analysis
  • Business Skills
  • Mainframe
  • Relational Database Management Systems
  • Software Testing
  • Windows Desktop
  • WebSphere MQ
  • Project Management
  • IT Service Management
  
  
• Class Schedules
• Facility Rentals
• Special Offers!

DB2 Advanced SQL and Application Development for Performance - D215 ( 5 Days )

Course Outline

Abstract/Overview

This 5-day, hands-on course examines DB2 for OS/390 UDB performance issues from the application programmer"s perspective. The course discusses the factors effecting DB2 performance and includes practical tips and techniques for designing and implementing efficient DB2 applications. The course includes an examination of advanced SQL and an in-depth study of efficient coding techniques. Hands-on exercises ensure an effective learning experience.

Audience - Who Should Attend?

The course is primarily intended for application designers, programmer analysts, and programmers who design and/or code DB2 for OS/390 UDB-based applications.

Prerequisite

Familiarity with the SQL language and DB2 application programming, or equivalent knowledge, is assumed. Ideally, students have taken Information Balance's "DB2 Application Programming Workshop", a 5-day introduction to DB2 programming.

Objective

• Understand the major factors contributing to DB2 performance.
• Learn about database design (including tablespace, table, and index design) and its effects on application performance.
• Have a comprehensive understanding of how application design effects performance.
• Understand the impact of concurrency on performance.
• Learn to code complex SQL statements involving DB2 data conversion, summarization, and multiple table access.
• Be able to judge the efficiency of SQL statements and tune ineffective queries.
• Understand how the Optimizer works and gain an in-depth knowledge of DB2 Access Paths.
• Become fully conversant in the use of the Explain facility including Optimizer Hints.
• Learn to use utilities as part of the performance tuning process.

Content

Introduction

• What is Performance?
• The Past, Present, and Future

• Performance Factors
• Tuning Process, Tools, Techniques
• Introduction to EXPLAIN

• Effects of Tablespace Design
  • Shared, Non-Shared
  • Simple, Segmented, Partitioned
  • Free Space
• Effects of Table Design
  • Normalization/Denormalization
  • DB2 Data Types, Nulls
• Effects of Index Design
  • Index Structure, Levels, Types
  • When and When Not to Index

• On-Line Design
  • Static, Dynamic SQL
  • Prompt, List, Object Dialoges
• Batch Design
  • Restartability
  • Sequential, List Prefetch
  • Utility Alternatives

• The Performance Dilemma
• DB2 Locking Strategy
  • Lock Size, Mode, Duration
• Improving Concurrency
• Avoiding Deadlocks

• Column Functions
  • GROUP BY and HAVING Clauses
• Scalar Functions

• Simple, Correlated Subselects
• Intersection, Difference

• Union, Union All
• Cartesian, Inner, Outer Joins
• Table Expressions

• Single Index Access
• Multiple Index Access
• Join Strategies

• What is it?
• The PLAN_TABLE
• Invoking the EXPLAIN Facility
• Interpreting EXPLAIN Output
• Optimizer Hints

• The Optimizer
• Processing SQL
• SQL Cost
• Filter Factor

• Using Predicates
  • Index Usage
  • Not, Between, Like
  • Arithmetic Expressions
  • Scalar and Column Functions
  • Predicate Evaluation Order
• Effective Multi-table Access
  • Inner and Outer Joins
  • Subselect vs. Join
  • Correlated Subselects
  • NOT IN vs. NOT EXISTS
• Avoiding Sorts

• LOAD
• COPY, MERGECOPY
• RUNSTATS
• REORG