Objectives

In this course, participants get an overview of the different DB2 for LUW components that influence system and application performance. Query optimization and efficient concurrency scenarios are emphasized. Also the environmental elements (e.g. logical and physical design, catalog information) that have an (direct or indirect) influence on the DB2 for LUW optimizer are discussed. At the end of the course, delegates are able:

• to discus organisational an physical objects in detail;
• optimise space management;
• monitor the system and evaluate dbm/db configuration parameters;
• trace process flows and db requests
• tune application behaviour and tune data structures.

Main topics

• The DB2 for LUW structure: Agents (subagents, loggers, prefetchers, cleaners) - Memory (buffer pools)
• to identify and tune memory structures and processes;
• to define and recognize database initialisation parameters;
• to execute system evaluations by means of monitoring tools.
• Tuning components: Blocking • Buffer pools • Asynchronous I/O • Database Agents • Sorting • Logging • Lock lists, Catalog Cache, Package Cache
• Using system monitors: Snapshot monitor • Event monitor • explain facility
• The DB2 for LUW SQL optimizer: Optimizer components • query rewrite • data access components • parallellism
• Application performance tuning:

Overview and discussion of the elements that influence the application performance. Discussed elements:

• Application related components:locking • optimization class • result sets • row blocking • select statements • guidelines • compound SQL • activating databases
• Environmental elements: configuration impact • table space impact • index management
• Catalog statistics: collection of statistics • distribution statistics • manual update of statistics
• Index Advisor: Objective • function and scenario • examples