UD09 - DB2 for LUW - SQL Performance and Tuning - 2 Days

Course Description

This course provides advanced DB2 for LUW training. It is aimed at staff requiring a detailed knowledge of the issues involved in writing, monitoring and tuning SQL queries that run in a DB2 for LUW environment.

Pre-requisites

The student should be familiar with writing SQL statements SELECT, INSERT, UPDATE and DELETE.

Objectives

The course provides information relating to the coding of efficient SQL statements. The major part of the course focuses on ensuring that SQL performs well in a production environment. The DB2 Explain facilities 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 the importance of RUNSTATS, REORGCHK and REORG
  • understand the process of optimisation
  • describe the various access path techniques that DB2 is able to use
  • write efficient SQL statements
  • run and understand the Visual Explain Facility
  • run and understand the command line Explain Facilities

Environment

The course applies 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

DATA MANIPULATION LANGUAGE PERFORMANCE ISSUES
Select Statements
The Where Clause
Special Operators
Special Operators - Examples
Sql Built-In Column Functions
Using xxDistinctxx
Group By Clause
Having Clause
Order By Clause
Fetch First xxnxx 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 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

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

Course Enquiry