OLTP system running DB2 9.5 on AIX system works fine currently however there is no archiving setup hence I'm curious about the following
1. Will it take longer response time in future to fetch data from the table because of index tree growth ?

2. Any method available to 'forecast' database slowness before it happens ?
Like if index read efficiency is coming down from 5 to 20 or index level has grown from 5 levels to 6 levels ?

3. Is there is any ideal or desired database size expected to support an OLTP db supporting around 3000 users

Thank You
Tony

Why don't you put your archive data into a separate table? That way, you don't have any performance impact on the operational data.

Regarding the ideal database size, it really depends on the hardware you have. Running 3000 users on a thinkpad is a stupid idea, of course, if those users are more active. If each of the users runs one transaction per day, such a thinkpad may be mostly idle.

Probably the biggest factor is going to be buffer pool hit ratio for OLTP systems. Do some research on how to calculate this, and how to raise the bufferpool hit ratio for the data and indexes, but never use more memory than actually exists on the server (real memory, not virtual).

Through in a few reorgs when appropriate (in-place if you don't have a maintenance window) and you should be in decent shape.

Stolze: Unfortunately we do not have a archiving mechanism yet.

Marcus: Do you mean periodic calculation of BP Hit ratio would provide more insight to forecast db slowness rather than index read ratio(rows read/rows selected) and synchronous read percentage as suggested by Scott Hayes in DBI Blogs ?

I am not necessarily saying that. But I don't really track the index read ratio either at the database level, becasue I prefer to analyze every SQL statement for elapsed time using a snapshot for dynamic SQL, or an explain for static SQL. If I take care of each statement and make sure it is optimized, then the database totals will take care of themselves.

Thanks.

From db cache I can get the list of Dynamic SQLs. Any method available to get static SQL cache in DB2 9.1 ?

Thanks
Tony

You can use db2pd -static to obtain the list of packages in the cache and db2expln to analyze these packages.