Well,

Performance Engineering for Databases... it is not just about identifying and tuning the issues... or removing the bottlenecks... More than that it is avoiding those bottleneck to occur... More of guessing the issues beforehand rather than identifying it latter...

So it involves following...

Having a more scalable Physical design...

A more robust logical schema design...

A more performing application design...

Let me discuss in brief

To start with Physical Design...

PHYSICAL DESING

With DB2 you need to ensure following things in physical design

1) You have tablespaces distributed on different disks

2) With my personal experience I suggest usage of ASM spread over several disks

3) Always have indexes and Data on saperate disks

4) It is good to categorize table and indexes as below

i) Online Transaction Data

ii) Configuraiton Data

iii) Static data for your application to work and load

Place tables of above category in three different tablespaces and their indexes in three

different

5) Always create separate bufferpools for configuration & static data and transaction data

and indexes

6) Always create tablespaces with NO FS option to avoid double caching

7) Always better to use STMM for better memory management

8) Set a proper value for TRANSFERRATE and OVERHEAD for tablespaces, as they tell the optimizer about the time taken for seek and transfer of data from disk. A proper value can at times help the optimizer choose index scan over tables scan (say in a situation when table data may not be too scattered but index leaves are)

SCHEMA DESING

1) Avoid having foreign keys if your application takes care of integrity

2) Always index the columns used for

i) WHERE clause filter

ii) JOIN predicates

iii) SORT operation (ORDER BY, MAX(), MIN() etc)

3) Always keep the STATISTICS updated for all the tables to ensure index scans

4) If the cardinality of any table changes abruptly, consider making the table VOLATILE. this will help you ensure and index scan

APPLICATION DESIGN

1) Always use ANSI JOIN operator for joining tables. This avoids any accidental cross product

2) Try not to use too many sort operations in your query eg multiple MAX etc. instead see if you can use RANKING or windowing functions

3) Avoid doing JOINs with VIEWs, instead join with tables, this will avoid JOINing with any unnecessary underlaying table of view.

4) Avoid self joins and prefer case statements over self joins if possible

5) Use FETCH FIRST N rows to optimize selection for few rows if using a packaged application set OPTIMIZEFORNROWS in db2cli.ini.

6) As a thumb of rule, use parmeterized queries. When working with a packaged application you can set statement concentrator db cfg to force parameterization

7) When working with reporting tools like Crystal Report, use a single query to fetch the data. Never use links and joins provided by tool. The tool might be doing a full fetch and a join locally. This has an impact on IO and network as well.

8) Have a better concurrency in application

CONCURRENCY CONTROL

To improve the concurrency of the application use following:

1) Use Cursor Stability Isolation Level

2) Use LOCKSIZE row for all the tables

3) Use optimistic locking by using ROW CHANGE TIMESTAMP expression

4) Set following parameter

i) db2set db2_skipinterted=ON

ii) db2set db2_skipdeleted=ON

iii) db2set db2_evaluncommitted=ON

iv) db cfg cur_commit

5) Avoid long transaction and ensure the operations are committed regularly

I will wind up this post with a note that setting this right will always help you avoiding any performance issues in future....

I will try to come up with some SQL Tuning Tips...