DB2 Administration › Optimize Query Performance

Optimize Query Performance

If report generation is slow, the most probable cause is DB2 performance. Environmental factors can affect DB2 query response time adversely.

The performance of the same DB2 application can vary widely between different environments. The application program cannot control or predict many factors that impact DB2 performance, no matter how good its internal practices.

These factors include but are not limited to the following:

• Database size and data distribution
• Database reorganizations
• Physical CPU and DASD hardware capabilities
• DB2 catalog statistics
• DB2 subsystem activity
• DB2 and JDBC driver versions
• DB2 maintenance levels
• DB2 dynamic SQL parameters
• DB2 sort and work table characteristics
• DB2 buffer pool characteristics
• WLM (Workload Manager) performance enclaves and settings

Inefficient SQL query processing affects DB2 resource usage. The DB2 optimizer determines the access path for each SQL query based on variable conditions including table sizes, column cardinalities, and physical CPU capacity.