Performance Tuning

Performance tuning means making DB2 do less work for the same result: fewer pages read, fewer sorts, fewer locks held. Most tuning wins come from statistics, indexes and SQL habits, not from exotic settings.

RUNSTATS and REORG

• The optimizer can only pick good access paths if it knows the data. RUNSTATS collects table and index statistics into the catalog.
• Run RUNSTATS after big loads, deletes or updates, and on a schedule for busy tables. Stale statistics are the number one cause of sudden slowdowns.
• REORG reorganizes tablespace pages back into cluster order and reclaims free space after heavy delete activity.
• Always run RUNSTATS after REORG, because the data layout changed and the old statistics no longer describe it.
• Example JCL:-
  //RUNSTATS EXEC DSNUPROC,SYSTEM=DB2T //SYSIN DD * RUNSTATS TABLESPACE DBONE.TSONE TABLE(ALL) INDEX(ALL) /*

Index design basics

• Proper indexes are good for performance in large databases. Index the columns used in WHERE, JOIN and ORDER BY clauses.
• Put the most selective column first in a composite index so MATCHCOLS is high.
• Consider covering indexes that include all SELECT columns, giving you INDEXONLY = Y access.
• Do not over-index. Every index slows down INSERT, UPDATE and DELETE and costs disk space.

SQL coding habits that matter

• COMMIT often in batch programs so locks are released and the log does not grow without bound.
• Select only the columns you need. SELECT * reads extra pages and blocks index-only access.
• Write sargable predicates: compare indexed columns directly instead of wrapping them in functions.
• Use host variables instead of literals in repeated SQL so DB2 can reuse the prepared access path.
• Filter early with WHERE before joining, so joins work on small row sets.