DB04 - DB2 Application Performance and Tuning
Duration3 Days (customisation)
Availability

Course Description

This course provides advanced DB2 training, and is aimed at staff requiring a detailed knowledge of the issues involved in 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 be familiar with writing SQL statements SELECT, INSERT, UPDATE and DELETE and should also have a working knowledge of Application Program construction.

Objectives

The course provides information relating to the coding of efficient SQL statements together with the wider issues that affect the performance of DB2 z/OS applications. The major part of the course focuses on ensuring that applications perform well in a production environment. The DB2 Explain facility is used extensively 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
  • choose the correct locking strategy
  • design and use Referential Integrity
  • design and use Materialized Query Tables
  • understand how Cursors are processed
  • understand and use Plans and Packages effectively
  • understand the importance of RUNSTATS
  • understand the process of optimisation
  • use optimisation hints
  • describe the various access path techniques that DB2 is able to use
  • run and understand the Explain Facility
  • use Indexable and Stage 1 predicates to write efficient SQL statements

Environment

The course runs in the following environment:

  • IBM Mainframe
  • DB2 for z/OS Versions 9, 10 or 11

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 OBJECTS
The Structure Of Db2 Objects
Database Definition
Tablespaces
Tablespace Creation Syntax
Pages
Tablespace Organisation
Page And Row Organisation
The Simple Tablespace
The Segmented Tablespace
The Partitioned Tablespace
Partition-By-Growth Tablespace
Partition-By-Range Tablespace
Maximum Number Of Partitions
Storing LOB data
The Lob Tablespace
Inline LOBS
Summary of Create Tablespace Parameters
Table Definition
Db2 Column Types
Design Tips for Columns
Null Values
Nulls - Design Tips
Implicitly Hidden Columns
Row Change Timestamps
Row Change Timestamp Selection
Row Change Timestamp Insertion
Table-Controlled Partitioning
Partition Management
Partition Management Examples
Db2 Indexes
Index Definition
Create Index Parameters
Index Organisation - The B Tree Index
Backwards Index Scan
Non-Unique Indexes
Index Clustering
Clustering With Partitioned Tables
Clustering Within Partition
Changing the Clustering Sequence
Partitioned Indexes
Creating a DPSI
Design Considerations - DPSI Problems
Index On Expression
Index On Expression Considerations
Index Design Considerations
Altering Tables and Indexes
Data Type Changes
Alter Data Type - Performance Implications
Alter Data Type - Impact Upon Indexes
Alter Data Type - Index Availability
DML PERFORMANCE REVIEW
Select Statement - Review
The Where Clause - Review
Special Operators - Examples
Sql Built-In Column Functions
Column Function Performance Notes
Using 'Distinct'
Group By Clause
Expressions / Functions in Group By
Having Clause
Order By Clause
Fetch First 'n' Rows Only Clause
The Update Statement
The Delete Statement
The Insert Statement
The Merge Statement
Merge Statement Restrictions
Select from Insert
Select from Insert Example
Select From Insert in a Cursor
Select From Insert - Order By Option
Select from Update
Select from Delete
Select from Merge
Scalar Functions
The Case Statement
Inner Joins
Outer Joins
Join Examples
Union, Intersect and Except
Union / Intersect / Except Examples
Subqueries
Subqueries Using In
Exists
Common Table Expressions
Common Table Expressions - A Complex Example
Recursive SQL
Recursive SQL Example
Recursive SQL - Controlling Depth of Recursion
Improved Insert Performance - The Append Parameter
The Truncate Statement
Truncate Examples
Truncate Restrictions
Utility Performance - Row Placement Rules
CLONE TABLES
Clone Tables - Fast Data Replacement
Why Use Clone Tables?
Creating A Clone Table
Dropping A Clone Table
Clone Table Considerations
Creating a Clone - Catalog Activity
Clone Tables - The Exchange Command
Clone Tables - Exchange Considerations
Clone Tables - Locking Considerations
PREDICATE PROCESSING
Predicate Definition
Predicate Evaluation
Predicate Evaluation Table
Predicate Evaluation Table - Notes
Indexable Predicates
Stage 1 And Stage 2 Predicates
Predicate Evaluation Sequence
Use Of And / Or
RUNSTATS
The Runstats Utility
Catalog Statistics Updated By Runstats
Runstats Considerations
Runstats - Distribution Statistics
Runstats - Historical Statistics
Volatile Tables
OPTIMIZATION
The Optimizer
The Optimize Statement
Fetch First 'n' Rows Only Clause
Filter Factors
Filter Factors With Boolean Operations
Performance / Cost Estimation
Example Of Performance / Cost Estimation
Run-Time Reoptimization
Optimization Hints
Planning to use Optimization Hints
TUNING
Db2 Explain
The Explain Statement
Plan Table Layout
Plan Table Column Definitions
The Dsn_Statemnt_Table
The Dsn_Function_Table
The Dsn_Statement_Cache_Table
The Dsn_Struct_Table
The Dsn_Predicat_Table
The Dsn_Detcost_Table
The Dsn_Sort_Table
The Dsn_Sortkey_Table
The Dsn_Filter_Table
The Dsn_Pgrange_Table
The Dsn_Pgroup_Table
The Dsn_Ptask_Table
The Dsn_Viewref_Table
The Dsn_Query_Table
The Dsn_Coldist_Table
The Dsn_Keytgtdist_Table
Explaining the Statement Cache
Interpreting Explain Output
Tablespace Scan
Non-Matching Index Scan
Matching Index Scan
Multiple Index Access
Index Only Access
Prefetch Processing
List Sequential Prefetch
SQL In List Processing - Dynamic Prefetch
Sequential Detection
Query Parallelism Techniques
Enabling Parallelism
Table Join Methods
Merge Scan Join
Nested Loop Join
Hybrid Join
Star Join
Star Join Example
IDENTITY COLUMNS AND SEQUENCES
Identity Columns
Identity Columns - Examples
Altering Identity Columns
Altering Identity Columns - Parameters
Identity Columns - Retrieving the Generated Number
Identity Columns - Data Sharing Implications
Using Identity Columns with the Load Utility
Sequences
Create Sequence Syntax
Sequence Ordering
Altering Sequences
Dropping Sequences
Using Sequences in Applications
Sequences - Considerations and Restrictions
Sequence Application Examples
Sequences and Identity Columns Comparison
JAVA DATABASE ACCESS - JDBC PERFORMANCE
Database Environments
The DB2 Universal Driver
JDBC SQL Statements
Using the Statement Class to Select Rows
Running SQL Statements
Using the Statement Class to Update Rows
Processing Result Sets
Jdbc Cursor Operations
Cursor Scroll Types
Cursor Concurrency Types
Cursor Holdibility Types
Updating Rows using the Cursor
Using the PreparedStatement Class
Making Batched Updates
Isolation Levels
DataSources
Getting Database Connections via a DataSource
JDBC vs Spring JDBC
Spring - Calling Stored Procedures
Spring - Updating
Spring - Updating With Named Parameters
Spring - Batch Updates
COBOL PROGRAMMING FOR PERFORMANCE
Development Cycle With Db2
Db2 Bind
Rebind
Binding Using Jcl
Catalog Information for Plans / Packages
Useful Bind Parameters
Package Management Enhancement
Package Switching
Deleting Old Packages
Retrieving System Registers
The Optimize Statement
Fetch First 'n' Rows Only Clause
Fetch First vs Optimize For
Non-Scrollable Cursors
Scrollable Cursors
Declaring a Scrollable Cursor
Insensitive Cursor
Sensitive Static Cursor with Insensitive Fetch
Sensitive Static Cursor with Sensitive Fetch
Sensitive Dynamic Cursor
Asensitive Cursor
Scrollable vs Non-scrollable Cursors
Updatable Cursors
Declaring a Scrollable Cursor
Fetching from a Scrollable Cursor
Absolute Fetching Examples
Relative Fetching Examples
Sensitive Fetches - Update and Delete Holes
Positioned Updates Using A Sensitive Cursor
Rowset Processing - Multi-row Fetch & Insert
Rowset Processing - Declare Cursor
Rowset Processing - Fetching Rowsets
Rowset Processing - Fetch Examples
Rowset Processing - Host Variable Arrays
Rowset Processing - Update and Delete Holes
Rowset Processing - Partial Rowsets
Rowset Processing - Positioned Update
Rowset Processing - Positioned Delete
Rowset Processing - Multi-row Insert
Rowset Processing - Multi-row Insert Syntax
Rowset Processing - Multi-Row Insert Example
Rowset Processing - Get Diagnostics
Get Diagnostics - Statement Information
Get Diagnostics - Examples
Diagnostic Information for Multi-Row Fetch
Get Diagnostics Fetch Example
Diagnostic Information for Multi-Row Insert
Get Diagnostics Insert Example
Rowset Processing - The Merge Statement
Merge Statement Example
Get Diagnostics - Merge Example
Select from Merge
LOCKING
Ims Resource Lock Manager (Irlm)
Db2 Locking Methods
The Lock Table Statement
Lock Modes
When Locks Are Acquired
When Locks Are Released
Isolation Levels
Skip Locked Data
V10 Locking Enhancement - Access To Committed Data
Keep Update Locks
Declaring Cursors With Hold
Savepoints
Savepoint Definition
Savepoints - Considerations and Restrictions
STORING XML DATA
eXtensible Markup Language Overview
Well Formed Documents
XML Data
Integration of XML Data
XML Serialization Function
XML Publishing Function
XML Data Model
XML Parser
XML Schema Repository (XSR)
XPATH
XPath Axes
XPath Functions
XPath Indexes
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
MQTs and Referential Integrity
MQTs and RI - Informational Constraints
TRIGGERS
Triggers
Trigger Parts
Before and After Triggers
Trigger Examples
Allowable Combinations
Error Handling
Trigger Cascading
Ordering of Multiple Triggers
Trigger Authorisation
Catalog Information for Triggers
Instead Of Triggers
Instead Of Triggers - Restrictions
Instead Of Triggers - Authorisation
Instead Of Triggers - Catalog Changes
Removing Triggers
REFERENTIAL INTEGRITY
What Is Referential Integrity?
Referential Constraint Rules
Constraint Names
Performance of Referential Integrity
What is Check Pending Status?
How Do I Reset Check Pending Status?
ACCESSING THE CATALOG
Catalog Contents
Catalog Table Reference
Catalog Access Examples

Course Format

Practical database set-up and SQL explain 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.