UD05 - DB2 for LUW - Application Design, Performance and Tuning - 3 Days

Course Description

This course provides advanced DB2 for LUW 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 on a Linux, Unix or Windows platform.

Objectives

The course provides information relating to the coding of efficient SQL statements together with the wider issues that affect the performance of DB2 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
  • write efficient SQL statements
  • write efficient program related SQL
  • understand DB2 locking issues
  • understand the performance of Datbase Integrity
  • 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

Environment

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

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

DB2 INSTANCE PERFORMANCE ISSUES
Configuring Instances for Performance

DATABASE PERFORMANCE ISSUES
Data Placement - SMS or DMS?
Automatic Storage Databases
Containers, Pages And Extents
Automatic Database Configuration
Bufferpool Issues
Page and Row Organisation

TABLE / INDEX PERFORMANCE ISSUES
Db2 Column Types
Null Values
Null and Default Compression
Compression - Row Format
Index Clustering
Multidimensional Clustering

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

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 PERFORMANCE
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 IBM Data Studio
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

DB2 TOOLS
IBM Data Studio
IBM Data Studio Web Console

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.

Course Enquiry