Database Tuning for complex sql queries

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

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning for complex sql queries

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright