Audience
The course is ideal for Systems Administrators, Developers, Database Administrators and technical personnel who are involved in all stages of designing, planning, implementing and maintaining a Db2 for LUW Database, but wish to ensure that they are using the language efficiently. The course is delivered at the latest release level, although previous releases can be covered on request.
Prerequisites
Delegates are expected to have a reasonable working knowledge of Db2 on the appropriate LUW platform (i.e. Linux, UNIX or Windows).
Duration
2 days. Hands on.
Course Objectives
The course is designed to instruct those delegates attending the course how to develop and implement Db2 applications in an efficient and effective manner.
Course Content
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