DB2 for LUW Performance and Tuning

Course Overview

The course is designed to instruct those delegates attending the course how to develop and implement DB2 applications using SQL in an efficient and effective manner.

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 up to DB2 9.

Prerequisites

A reasonable working knowledge of DB2 on the appropriate platform (ie. Linux, UNIX or Windows).

Course Outline

Introduction to DB2

• The DB2 family
• The Big Picture
• Basic Terms
• DB2 System Catalog
• Attributes
• Consolidation of DDL with regard to efficiency up to and including DB2 9
• Consolidation of operating system commands including db2look
• Practical Exercise

Indexing

• Cluster Ratio
• Effect on sorting
• MDC
• Design Advisor
• Practical Exercises

Volatile

• RUNSTATS
• REORG
• Access Strategies
• Practical Exercise

Explain

• Visual Explain
• Practical Exercise

Complex SQL with regard to efficiency

• Table joins
• EQ JOIN
• SUBSELECT
• CORRELATED SUBSELECT
• UNION
• Practical exercise

Left/Right/Full Joins

• In Line Tables
• CASE
• Remainder of DML statements
• UPDATE/DELETE/INSERT/MERGE
• CTE's
• Embedded SQL
• Static and Dynamic SQL
• Cursors
• Development cycle
• Precompile
• DBRMs
• BIND
• PLANS/PACKAGES
• Execution of executable program
• Block Fetches

TRIGGERS

• STORED PROCEDURES
• Utilities
• BACKUP
• RESTORE
• LOAD
• IMPORT/EXPORT
• Introduction to Automatic Computing
• Configuration Parameters
• Collecting Catalog Statistics
• Activity Monitor
• Memory Visualization
• Health Center
• Remote Administration
• DB2 Administration Server
• Configuration Assistant
• Instance Attachment
• Connection

DB2 for LUW Performance and Tuning / DB2UPSQ (DB2UPSQGB)