Target Audience
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.
Course 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
Course Environment
The course applies to DB2 running in a Linux, Unix or Windows environment.
Course Details
- DB2 INSTANCES
- What Is An Instance?
- Setting Up Instances
- DATABASE DEFINITION
- The Structure Of DB2 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
- 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
- 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
- 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
- PERFORMANCE UTILITIES
- 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
- 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
- DB2 ACCESS PATHS
- 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
|