Db2 Database (LUW) Application Design, Performance and Tuning

What you will learn

On successful completion of this course you will be able to:

• understand the performance issues involved in constructing a system
• implement an efficient tablespace, table and index design
• write efficient SQL statements
• write efficient program related SQL
• understand Db2 locking issues
• understand the performance of Datbase Integrity
• 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.

Who Should Attend

Db2 Database applications programmers.

Prerequisites

A working knowledge of using Db2 on a Linux, UNIX or Windows platform.

Duration

3 days

Fee (per attendee)

£1750 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

UD05

Contents

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

Partitionning

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 Lamguage 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 foreEmbedded SQL in C; SQLJ Programming - Development Cycle; JDBC Drivers - The Universal Driver; Single Row Selects; Including Eeternal 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 optimization; Run Time reoptimization - Bind option REOPT; LOCKING; Implications of concurrent processing; Database Manager Locks; Objects ff 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.

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 Performance

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 IBM Data Studio; 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 Tunning

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.

Db2 Tools

IBM Data Studio; IBM Data Studio web console.