07 June,2012 by Jack Vamvas
Question: I manage a large DB2 data warehouse. The datawarehouse supports multiple ETL processes . Sometimes , some SQL queries fail. The large SQL queries are attempting to extract a large amount of data and are complex.
What DB2 parameters can I consider changing to fix this problem?
Answer: Firstly , ensure the indexes are maintained correctly. Are statistics up to date? Are fragmentation levels low? Are REORG on tables running regularly?
For large complex queries , I normally look at the SQL statement heap (STMTHEAP) , default application heap (APPLHEAPSZ), and the package cache size (PCKCACHESZ)
The DB2 STMTHEAP defines the SQL compiler work space size for the SQL statement compilation. If I see errors in the log – relating to complex queries , I increase the value until the error disappears
The DB2 APPLHEAPSZ is the max number of private memory pages for SQL requests , as used by the DBM. A larger SQL statement requires larger amounts of heap space. If I see error log messages about application heap constriction , I increase the APPLHEAPSZ.
DB2 PCKCACHESZ caches sql statements (static and dynamic). If error log messages appear about storage restriction on the PCKCACHESZ heap , I increase the size