db2 monitoring

db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"
db2 "get snapshot for tablespaces|tables| on db db_name"

and to see which switches are active:
use
db2 "get dbm monitor switches"

Note:don't use double quotes while running the command from CLP

the following links provide good explanation for monitoring database:

http://space.itpub.net/?uid-47598-action-viewspace-itemid-330882

http://www.performancewiki.com/db2-event-monitors.html

http://it.toolbox.com/blogs/db2luw/using-the-snapshot-monitor-and-monitoring-via-sql-5631

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

DB2 SQL Practice for tuning database performance

1) Avoid distinct where ever possible. Check whether distinct is
required or not. No distinct when PK or UK are retrieved.

2) One can consider usage of union where OR condition exits &
eliminate distincts.

3) Conditions which are likely to fail should be kept first in a set
of conditions separated by AND.

4) Always use aliases.

5) Do not involve columns in an expression.
select * from emp where salary/12 >= 4000;
The query should be:
select * from emp where salary >= 4000 * 12;
i.e. Avoid using Arithmetic within SQL statements.Arithmetic in a SQL
statement will cause DB2 to avoid the use of an index.

6) Try to avoid usage of in-built or user defined functions.
select * from employee where substr(name,1,1) = 'G';
The query should be:
select * from employee where name = 'G%';

7) Avoid datatype mismatch since it may lead to implicit/explicit casting.
select * from emp where sal = '1000';
The query should be:
select * from emp where sal = 1000;

8) Substitute unnecessary group by & having with where clause.
select avg(salary) as avgsalary, dept from employee group by dept
having dept = 'information systems';
The query should be:
select avg(salary) as avgsalary, dept from employee where dept 'information systems';

Courtsey:Toolbox for IT