Hi.
I want to know what is better ..To have one tablespace with many tables or for each table one tablespace.... I would like to know which is the performance of the distribution of tablespaces in DB2 . I use DB2 7.1
in AIX..

Now i have almost 140 Tables distributed in 53 Tablespaces..
And i have a tablespace only for indexes is this correct? for the performance of de database..

Thank you very much...

That depends on many factors, including how much buffer pool memory is allocated, for example. If there is lots of buffer pool space allocated, there is not much synchronous physical I/O going on.

In theory it does help to have indexes on a seperate disk drive or disk array than the tables, so having different tablespaces for them makes that possible if you set it up that way.

You do not need to have each table in it's own tablespace. However, tablespaces have their own page size, bufferpool, extents, and prefetch size, which could differ among the tables you have.

Backup and recovery of related objects at the tablespace level is also a consideration.

In general conditions :
If there is enough buffer pool , I rather to have 1 table space for the tables and 1 table space for my indexes. In this case you will have less hassle for administration and ...
However it depends to your application, sometimes it is better to have your indexes in 2 different table spaces because you want to synch 2 or more different hard disk to return your results back .

I've found that tables that have a high insert/delete rate are best served in their own TS. We did this for a customer and got a pretty good TPS improvement.

Every environment/application is different and after following basic guidelines, you'll have to start pursuing your 'gut' feelings.

If you are using LOAD Utility, you may want to put those tables into their own tablespace ...

Cheers
Sathyaram