General Tips for tuning DB2 database

performance related db parms

what your hard disk nature , no of CPU, BPs

what is your hardware environment , physical disks, No of CPU, physical disk
properties , seek time , RPM.

get snapshot for bufferpool , check hit ratio, ( physical , logical reads,
if physical read are more , increase BP size)

also see package lookup , insert activity , if insert activity more increase
packagecachesize (PCKCACHESZ).

create national BP , allocate at highly accessed tablespaces

calculate tablespace transfer rate , over head based on physical disk seek
time , RPM

update dbm parm

Enable intra-partition parallelism (INTRA_PARALLEL) = YES ( need to
turned for data ware house environments
db2 update dbm cfg using INTRA_PARALLEL YES

DB parms

Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 ( no CPU for
your server)
Number of I/O servers (NUM_IOSERVERS) = 3( no physical
disks)

db2 update db cfg for dbanme using NUM_IOCLEANERS no CPU for your server
NUM_IOSERVERS no physical disks

any application in DB or server side 20% of tuning can be done , rest 80%
tuning need to done in application side, may application design, logic ,
flow , way of SQL writing.

SQL performance tips :-

avoided cantusion join, always make optimizer to use nested loop join.

join large table with small tables, no not in SQL, make better use of index,
good key for index

run db2advis ( indexe advisor to find indexes for SQL)

db2advis -d dbname -i sqlinputfile -o indexeDDL

Courtsey:Toolbox for IT