Subscribe to Newsletter

Print

DB2 UDB (Universal Database System) Performance & Tuning Training Course


(This course is offered only on-demand to a private batch and can be customized as per the business requirements)

Overview

The course is designed to provide participants with the knowledge of the DB2 internal architecture in order to tune a database and monitor a DB2 system. There is an examination/demonstration of the DB2 tools used for implementing performance trade-offs. The focus of the course is to manage database design and teach how to code applications, which will improve overall performance. Both system design - CPU and memory - and database design - catalogs, database objects, and locking and concurrency - will be covered.

Objectives

At the end of DB2 UDB Performance & Tuning training course, participants will be able to:

  • Optimize I/O and storage for performance
  • Optimize DB2 for mixed workloads
  • Manage catalog and directory
  • Manage referential integrity for related tables
  • Monitor and optimize memory usage
  • Specify effective locking options

Suggested Audience

  • Application Programmers
  • Database Designers

Duration - 4 Days

Prerequisites - Basic experience in DB2 databases.

Syllabus

1. Performance and Monitoring
  • Performance concepts
  • Command line: snapshot monitoring
  • Configuration/administration
 2. I/O and Storage Management
  • Data set allocation and placement
  • I/O subsystem / I/O / I/O scheduling priority
  • Cache considerations
  • Tuning I/O and caching
  • Compression
  • Logging
3. CPU
  • CPU and SQL design issues
  • Number of SQL statements
  • Number of rows retrieved
  • Number of columns retrieved
  • Unnecessary repetitive processes
  • Referential integrity
  • Sorting
  • Physical design issues
  • Application design issues
  • SQL Tuning for CPU
  • Mixed workloads
 4. Memory
  • Virtual storage
  • Buffer pool
  • I/O requests and paging
  • Internal thresholds
  • The hit ratio
  • RID pool
  • Statistics to monitor
5. Catalog and Directory
  • Catalog
  • SKCT: skeleton cursor table
  • ODBC catalog
  • Reducing catalog contention
  • Updating catalog statistics: manually
  • Key correlation statistics
  • Catalog queries
  • Queries to use with EXPLAIN output
  • Displaying multicolumn cardinalities
 6. Physical Database Objects
  • Guidelines for optimal design
  • Table spaces
  • Partitioning
  • Table space compression
  • Free space
  • Data-set closure
7. Locksize
  • Lock escalation
  • Selective partition locking
  • Normalization/denormalization
  • Splitting tables based on usage
  • VARCHAR columns
  • Determining the row length
  • When to use declarative RI
  • Table check constraints
  • Indexes
 8. General Application Design
  • Program structure
  • Putting the logic in the SQL
  • Retrieving only what is needed
  • I/O modules
  • Cursors
  • Plans and packages
  • When to rebind packages
  • Commit strategies
  • Explain tool for analyzing SQL statements
  • Visual explain
9. Locking and Concurrency
  • Programming for concurrency
  • Row-level locking
  • Unique generated numbers and identity columns
  • Releasing locks
  • Deadlocks and retry logic
  • Lock escalation
  • Monitoring time-outs and deadlocks
  • Lock wait time