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

DB2 INSTANCES: What Is An Instance?; Setting Up Instances; The Db2set Command; Configuring Instances; Updating the Database Manager Configuration; Database Manager Configuration Parameters; Implementing Changes; Automatic Database Manager / Database Configuration; Working With Multiple Instances
DATABASE DEFINITION: The Structure Of Udb 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; Database Configuration; Updating the Configuration; Summary Of Database Configuration Parameters; Automatic Database Configuration; Database Directories; Create Bufferpool; Online Bufferpool Maintenance; Block Based Bufferpools - Prefetch Enhancements
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; Alter Tablespace Command; Altering Dms Containers; Containers, Stripes and Stripe Sets; Creating A Tablespace Using The Gui; Displaying Tablespace Information; Tablespace States; Dropping A Tablespace; Page And Row Organisation
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; Renaming Indexes; Multidimensional Clustering; Defining Multidimensional Clustering; Defining Multidimensional Clustering Indexes; Using Generated Columns with MDC; Db2look
PARTITIONING: Introduction and Terminology; The DB2Nodes.cfg File; Example Configurations; Creating Parallel Databases; Database Partition Groups; Creating a Database Partition Group; Listing Nodes and Database Partition Groups; Creating Partitioned Tablespaces; Creating Partitioned Tables; Hash Partitioning; Partitioning Maps; The Redistribute Database Partition Group Command; Range Partitioned Tables
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
APPLICATION PROGRAMMING PERFORMANCE ISSUES: Db2 Environments; Development Cycle For Embedded Sql in C; SQLJ Programming - Development Cycle; JDBC Drivers - The Universal Driver; Single Row Selects; Including External Sql into C Programs; Sql Include; Sql Communication Area; Host Variable Declaration in C Programs; Cursor Selects; Declare Cursor; Open Cursor; Fetch A Row; Row Update; Row Deletion; Close Cursor; With Hold Option; Fetch First Clause; The Optimize Statement; Precompiling A Program; Binding A Program; Levels Of Optimisation; Run Time Reoptimization - Bind Option REOPT
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; Savepoints; Savepoints - Considerations and Restrictions; The Quiesce Utility; SHARE
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
DATA INTEGRITY: Referential Integrity; The Primary Key; The Foreign Key; Referential Integrity Rules; Referential Integrity Constraint Names; Self Referencing Referential Structures; Referential Integrity Performance; Check Constraints; Check Constraint Syntax; Allowable Check Constraints; When Are Check Constraints Enforced; Integrity Pending (aka Check Pending) State; The Set Integrity Command; Running Set Integrity Via The Gui; Informational Constraints
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
SYSTEM PERFORMANCE: Database Performance Configuration Parameters; Data Sorts; Concurrent Application Tuning; Asynchronous Page Cleaner; Blocking Data; The Database Configuration Advisor
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
UDB TOOLS: The Control Center; Control Center Enhancements; Command Editor; Storage Management Tool; The Journal; The Development Center; Sql Assist Enhancements; Replication Center; Additional Wizards and Gui Tools

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.