UD05 - DB2 UDB Application Design, Performance and Tuning
Duration3 Days
AvailabilityCheck course price and availability

Target Audience

This course provides advanced DB2 UDB training, and is aimed at staff requiring a detailed knowledge of the issues involved in designing, writing, monitoring and tuning DB2 SQL queries and applications to perform efficiently. In addition, the course covers some wider areas that can affect application performance, such as Cursor Operations, Locking and Referential Integrity.

Pre-requisites

The student should have a working knowledge of using DB2 UDB on a Linux, Unix or Windows platform.

Course Objectives

The course provides information relating to the coding of efficient SQL statements together with the wider issues that affect the performance of DB2 UDB Java or C applications. The major part of the course focuses on ensuring that applications perform well in a production environment. The DB2 Explain tools are used to demonstrate how SQL performance may be monitored and tuned. On completion of this course the student will be able to:

  •     understand the performance issues involved in constructing a system
  •     implement an efficient tablespace, table and index design
  •     understand DB2 locking issues
  •     design and use Referential Integrity
  •     design and use Table Check Constraints
  •     understand how Cursors are processed
  •     understand the importance of RUNSTATS
  •     understand the process of optimisation
  •     describe the various access path techniques that DB2 is able to use
  •     run and understand the various Explain tools
  •     monitor database / application performance
  •     write efficient SQL statements

Course Environment

The lectures apply to DB2 running in a Linux, Unix or Windows environment.

Course Details

DB2 INSTANCES
What Is An Instance?
Setting Up Instances
The Db2set Command
Configuring Instances
Updating the Database Manager Configuration
Database Manager Configuration Parameters
Implementing Changes
Automatic Database Manager / Database Configuration
Working With Multiple Instances
DATABASE DEFINITION
The Structure Of Udb Objects
Database Definition
Default Tablespaces
Database Placement
Automatic Storage Databases
Database Creation Using The Gui
Database Definition Tasks
Altering a Database
Database Connectivity - Connect vs Activate
Catalog Tables
Database Configuration
Updating the Configuration
Summary Of Database Configuration Parameters
Automatic Database Configuration
Database Directories
Create Bufferpool
Online Bufferpool Maintenance
Block Based Bufferpools - Prefetch Enhancements
TABLESPACE DEFINITION
Tablespace Organisation
Data Placement - SMS or DMS?
Data Placement Considerations
Containers, Pages And Extents
Create Tablespace Command
Create Tablespace Parameters
Sms Tablespace Examples
Dms Tablespaces
Automatic Storage Tablespaces
Dms Minimum Space Requirements
Dms Maximum Space
Alter Tablespace Command
Altering Dms Containers
Containers, Stripes and Stripe Sets
Creating A Tablespace Using The Gui
Displaying Tablespace Information
Tablespace States
Dropping A Tablespace
Page And Row Organisation
TABLE / INDEX DEFINITION
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Db2 Column Types
Null Values
Null and Default Compression
Compression - Row Format
Has Compression Been Switched On?
Lob Data Options
The Alter Table Statement
Advanced GUI Alter Table Functionality
The Rename Table Statement
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables - Comparisons
Listing Table / Tablespace Information
Schema Definition
View Definition
Creating A View Of Two Tables
Read Only Views
View Restrictions
Views - Check Options
Alias Definition
Indexes
Index Organisation - The B Tree Index
Index Clustering
Index Definition
Renaming Indexes
Multidimensional Clustering
Defining Multidimensional Clustering
Defining Multidimensional Clustering Indexes
Using Generated Columns with MDC
Db2look
PARTITIONING
Introduction and Terminology
The DB2Nodes.cfg File
Example Configurations
Creating Parallel Databases
Database Partition Groups
Creating a Database Partition Group
Listing Nodes and Database Partition Groups
Creating Partitioned Tablespaces
Creating Partitioned Tables
Hash Partitioning
Partitioning Maps
The Redistribute Database Partition Group Command
Range Partitioned Tables
DATA MANIPULATION LANGUAGE PERFORMANCE ISSUES
Select Statements
The Where Clause
Special Operators
Special Operators - Examples
Sql Built-In Column Functions
Using 'Distinct'
Group By Clause
Having Clause
Order By Clause
Fetch First 'n' Rows Only Clause
The Update Statement
The Delete Statement
The Insert Statement
Scalar Functions
Function Examples
The Case Statement
Joins
Sql Union
Subqueries
Common Table Expression Example
Writing a Common Table Expression
Subqueries Using In
Exists
Common Table Expressions
Common Table Expression Example
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
APPLICATION PROGRAMMING PERFORMANCE ISSUES
Db2 Environments
Development Cycle For Embedded Sql in C
SQLJ Programming - Development Cycle
JDBC Drivers - The Universal Driver
Single Row Selects
Including External Sql into C Programs
Sql Include
Sql Communication Area
Host Variable Declaration in C Programs
Cursor Selects
Declare Cursor
Open Cursor
Fetch A Row
Row Update
Row Deletion
Close Cursor
With Hold Option
Fetch First Clause
The Optimize Statement
Precompiling A Program
Binding A Program
Levels Of Optimisation
Run Time Reoptimization - Bind Option REOPT
LOCKING
Implications Of Concurrent Processing
Database Manager Locks
Objects Of Locks
Lock Modes
Lock Example
Lock Compatibility
Isolation Levels
Lock Escalation
The Lock Table Statement
Commit Points
Lock Wait And Deadlocks
Savepoints
Savepoints - Considerations and Restrictions
The Quiesce Utility
SHARE
MATERIALIZED QUERY TABLES
What Are Materialized Query Tables?
MQT Features
Creating an MQT
Create MQT Example
Altering an MQT
Alter MQT Example
MQT Fullselect Features / Restrictions
Refresh Table
Populating User Maintained MQTs
Automatic Query Rewrite using MQT
Enabling Automatic Query Rewrite
Enabling Automatic Query Rewrite - DDL Options
Enabling Automatic Query Rewrite - Special Registers
AQR - Using Both Registers
Enabling Automatic Query Rewrite - System Properties
Enabling Automatic Query Rewrite - Query Properties
Registering Existing Tables as MQT
Maintenance of MQTs using Staging Tables
DATA INTEGRITY
Referential Integrity
The Primary Key
The Foreign Key
Referential Integrity Rules
Referential Integrity Constraint Names
Self Referencing Referential Structures
Referential Integrity Performance
Check Constraints
Check Constraint Syntax
Allowable Check Constraints
When Are Check Constraints Enforced
Integrity Pending (aka Check Pending) State
The Set Integrity Command
Running Set Integrity Via The Gui
Informational Constraints
APPLICATION PERFORMANCE
The Db2 Optimizer
Levels Of Optimisation
Operational Utilities
Rebinding
The Runstats Utility
Runstats Parameters
Runstats - Sampling Options
Runstats - Statistics Profiling
Runstats - Throttling
Runstats Profiling Examples
Automatic Statistics Collection
Automatic Statistics Profile Generation
The Reorgchk Utility
The Reorg Utility
Offline / Online Table Reorg
Index Reorg
SYSTEM PERFORMANCE
Database Performance Configuration Parameters
Data Sorts
Concurrent Application Tuning
Asynchronous Page Cleaner
Blocking Data
The Database Configuration Advisor
MONITORING
Error Logging
Database Monitoring
Snapshot Monitor
Turning Monitoring Switches On
Snapshot Commands
Taking a Snapshot using Sql
SQL Snapshot Functions
Event Monitors
The Create Event Monitor Command
Event Monitor Example
Activating Monitors
Formatting File Monitor Output
Event Monitors - Writing to SQL tables
The Activity Monitor
Health Monitoring
Health Indicator Configuration
Recommendation Advisor
SQL PERFORMANCE AND TUNING
SQL Explain Tools
Explain Tables
The Db2 Explain Bind Option
The Db2expln Tool
The DynExpln Tool
Interpreting DB2Expln and Dynexpln Output
The Db2advis Tool - Index Advisor
The Design Advisor
The Visual Explain Tool
The Explain Operator Details Window
Visual Explain Operators
Visual Explain - The Table Statistics Window
Visual Explain - The Column Statistics Window
The Index Statistics Window
The Explainable Statements Window
Access Paths - Tablespace Scan (Relational Scan)
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Table Join Methods
Merge Scan Join
Nested Loop Join
Hash Join
UDB TOOLS
The Control Center
Control Center Enhancements
Command Editor
Storage Management Tool
The Journal
The Development Center
Sql Assist Enhancements
Replication Center
Additional Wizards and Gui Tools

Course Format

Practical database set-up and application performance sessions make up a large part of the course, allowing delegates to demonstrate and reinforce the lectures given. During these sessions the delegate will run many explains on example queries and programs. Delegates are also invited to bring along their own SQL statements to be used as case studies during the course The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.