Database Tuning for complex sql queries

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