DB2 UDB Training - UD05 - DB2 UDB Application Design, Performance and Tuning

UD05 - DB2 UDB Application Design, Performance and Tuning
Duration	3 Days
Availability	Check course price and availability

Target Audience

This course provides advanced DB2 UDB training, and is aimed at staff requiring a detailed knowledge of the issues involved in designing, 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 have a working knowledge of using DB2 UDB on a Linux, Unix or Windows platform.

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 Environment

The lectures apply to DB2 running in a Linux, Unix or Windows environment.

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 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.