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

db2diag log analysis tool

db2diag is one file which gives clues to error that might have occurred to database.
But instead of going to db2diag file we can run a command from CLP to see the error messages and info about any specific SQL error code.

It is the log analysis tool and it allows you to search, filter, and format the DB2 diagnostic logs. One of the simple examples is that I wanted to see when errors or warnings were produced on my test system. I could do that by calling:

db2diag -l Error,Warning -fmt "%ts %level %db"

sample output:
2010-02-09-13.46.24.828000 Error XXLIC_DB
2010-02-09-13.46.24.828000 Error XXLIC_DB
2010-02-09-13.46.25.046000 Warning XXLIC_DB
2010-02-09-13.46.25.062000 Warning XXLIC_DB
2010-02-09-13.46.25.093000 Warning XXLIC_DB

The output is much quicker to digest than the diagnostic itself.

what I have been upto recently...

This is the first entry in 2010..must say being regular with blog is something I need to prioritize as this is not only a way to share the knowledge and experience which many can find useful but can be very handy to place important stuff which I can come back to refer....
for the past month I have been traveling to many database sites and making migration, version upgrade and other fine tuning tasks...
Troubleshooting is one thing that we come across often so I m going do dedicate one post for that.

www.db2commands.com - DB2 HADR and HACMP Commands var cssNode = document.createElement('link'); cssNode.type = 'text/c

www.db2commands.com - DB2 HADR and HACMP Commands var cssNode = document.createElement('link'); cssNode.type = 'text/c

SQL Bible

SQL Bible

This is a very good place to learn and explore SQL Pragramming.

Tryst with Warehousing

I have been working on IBM Infosphere Warehouse 9.5 for some time now.
Let me admit that I struggled to start with this as I didn't have any official training with Warehouse all I knew about it from my one semester course in Warehouse which I studied during my MCA class and a very senior professor took the class and I was hooked to this subject however I had no idea one day I would be working on it.
I think Its a cool technology and one of the most sought after IT field as actionable information is key to have a competitive edge for any organization.
I have to say I don't find too many contents on internet particularly on IBM DWH.However redbooks and some article on developerworks are quite good it still won't help you much if you r a fresher in DWH.
I remember in college I read Paul raj Poonia book which was quite good in theory but as for the hands-on you need something more to be able to work on it.
I hope I could enhance my skill on working with DWH even without training, but it requires a lot of work.
I m ready to roll up my sleeves and lay my hands dirty!

hugs,
Rakesh