DB2 LUW Application Design, Performance and Tuning

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 Details
INSTANCE CONFIGURATION
What is an Instance?
Setting up Instances
Configuring Instances
Implementing Changes
Attaching to an Instance
DEFINING EFFICIENT SYSTEMS
The Structure of UDB Objects
Definition of DB2 Objects - DDL
Database Definition
Database Creation using the GUI
Database Definition Tasks
Database Placement
Database Connectivity
Catalog Tables
Database Configuration
Database Directories
Tablespace Organisation
Table Placement
Data Placement Considerations
Containers, Pages and Extents
SMS Tablespaces
DMS Tablespaces
DMS Minimum Space Requirements
DMS Maximum Space
Creating a Tablespace using the GUI
Displaying Tablespace Information
Altering a Tablespace
Dropping a Tablespace
SMS vs DMS Considerations
Create Bufferpool
Page and Row Organisation
Table Definition
Table / Column Names
Copying Table Definitions
Table Authority
Data Types
Null Values
User Defined Default Values
Lob Data Options
Lob Data Manipulation
User Defined Distinct Types
Data Capture
The Alter Table Statement
The Rename Table Statement
Listing Table / Tablespace Information
Schema Definition
Indexes
Index Organisation - The B Tree Index
Index Definition
Index Clustering
Index Design Considerations
Views Definition
Read Only Views
View Restrictions
Views - Check Options
Creating a View of Two Tables
Overview of Triggers
Trigger Definition
The Drop Statements
DATA MANIPULATION LANGUAGE EFFICIENCY ISSUES
Select Statements
The Where Clause
Special Operators
Special Operators - Examples
SQL Built-In Column Functions
Column Function Performance Notes
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
DB2 Join
Inner Joins
Outer Joins
Outer Join - Where Clause
Nested Table Expression
SQL Union / Intersect / Except
Subqueries
APPLICATION PROGRAMMING
DB2 Environments
Database Engine Access
Development Cycle for Embedded SQL
SQL Statement Format - C
SQL Statements used in Application Programs
Cursor Performance
Declare Cursor
Open Cursor
Fetch a Row
Row Update
Row Deletion
Close Cursor
With Hold Option
Fetch First Clause
The Optimize Statement
Handling Nulls
Sample C Program
Handling Large Objects
Declaring Lob Variables
Example using Lob Variables
Using Lob Locators
Example using Lob Locators
Lob File References
Example using File References
Lob Limitations
Java Development
Dynamic SQL - What is JDBC?
What are JDBC Drivers?
JDBC Driver Types
JDBC SQL Statements
JDBC Statements
The JDBC API
SQLJ Programming - Development Cycle
SQLJ Support
Host Variable Declaration
SQLJ Cursor Selects
Positioned Updates using Cursors
Positioned Update Restrictions
JDBC vs. SQLJ
Isolation Levels
Fetch First Clause
The Optimize Statement
Handling Nulls
Table Names In Application Programs
Sql Error Codes
Precompiling a Program
Binding a Package
Levels of Optimisation
Identifying a Collection Within a Program
APPLICATION PERFORMANCE
The DB2 Optimizer
Default Catalog Statistics
Levels of Optimisation
Operational Utilities
Rebinding
The Runstats Utility
The Reorgchk Utility
The Reorg Utility
Other Application Performance Issues
Data Sorts
Concurrent Application Tuning
Asynchronous Page Cleaner
Blocking Data
ACCESS PATHS
Tablespace Scan
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Indexable Predicates
Use of And / Or
Table Join Methods
Merge Scan Join
Nested Loop Join
Hash Join
MONITORING
Error Logging
Database Monitoring
Snapshot Monitoring
Alerts
Monitoring Commands
Turning Monitoring Switches On
Event Monitoring
The Create Event Monitor Command
DB2 Trace Facility
Explain
Explain Facility Tools
The DB2 Explain Function
Explain Tables
The DB2EXPLN Applet
The Visual Explain Tool
REFERENTIAL INTEGRITY
What is Referential Integrity?
The Primary Key
The Foreign Key
Referential Constraint Rules
Constraint Names
More Complex Referential Structures
Check Pending / Set Constraints
Running Set Constraints via the GUI
TABLE CHECK CONSTRAINTS
Overview of Check Constraints
Constraint Syntax
Allowable Constraints
When Are Constraints Enforced
Check Pending
Running Set Constraints via the GUI
Catalog Information
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
The QUIESCE Utility
UDB TOOLS
The Control Centre
The Command Centre
The Access Plan Panel
The Journal
The Alert Centre
Tool Settings
Course Environment The lectures apply to DB2 running in a Linux, Unix or Windows environment.

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.