Db2 Database (LUW) Application Design, Performance and Tuning

This course provides advanced Db2 Database (LUW) training, and isdesigned for all those 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.

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.


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


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



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.


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.


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.

© RSM Technology 2022