How to Increase IBM DB2 Performance

The main goal of database performance tuning is to make sure that database is running as efficiently as possible. The procedure includes standard steps that are common for most DBMS such as database table normalization, clustering and partitioning across multiple drives. This article focuses on issues and workarounds that are specific for IBM DB2.

There are many reasons of unsatisfied DB2 database performance including:

• Insufficient hardware capacities (low memory, weak CPU)
• Non-optimized OS configuration (dispatching priority, data sharing, swap)
• Issues in DB2 configuration
• Too many calls to DB2 from applications
• Bad coding practice in SQL queries
• Poor index design

DB2 configuration

• Tune the DB2 Catalog
  • drop unused objects
  • reorganize tablespaces and indexes
  • rebuild existing indexes
  • add new indexes when it is necessary
  • change data set placement
  • move DB2 Catalog to fast disk
  • implement data set shadowing
• Change log buffers via SET LOG commands
• Change bufferpool sizes and/or thresholds and modify associated hiperpools via ALTER BUFFERPOOL commands
• Modify DSNZPARMs
  • change the number of concurrent users
  • change lock escalation
  • increase EDM pool storage

Applications

• Try to reduce number of inserts, updates, deletes, open cursors and fetches per execution
• Take benefits of multi-row processing such as merge, select from insert/update/delete, joins
• Modify locking strategies
• Reduce calls to DB2 database (move some logic inside the database)
• Reduce network requests in client/server applications
• Use RUNSTATS to monitor the state of DB2 objects, tables, indexes, etc

Queries

• Order data by primary key or indexed column(s)
• Make sure most predicates match an available index
• Remove all scalar functions from predicates where it is possible (e.g. WHERE YEAR(birth_date)=1980 can be replaced by WHERE birth_date BETWEEN '1980-01-01' AND '1980-12-31')
• Use EXPLAIN statement to examine access paths for SELECT parts of the query

Have questions? Contact us