Hello,
Our DB2 version is 8.2 on Windows 2003. Some table contains over 70million rows.

In our system all the TABLES and INDEXES are in two seperate TABLESPACES and all 4K pages.

One of the IndexSpaces which contain only INDEXES is nearly full (over 92% full) and not allow to add more containers. So I have to create new 8K or 32k page tablespace for this INDEXES and recreate the indexes.

Now the question is, If I create the bigger TABLESPACE for INDEXES and recreate the INDEXES will be enough? OR Do I must create the bigger size tablespace for both TABLES and INDEXES?

Please advise me.


Thanks, Gunas.

Since you can only specify an index tablespace at the table creation time, you will have to re-create the table entirely. It might make sense then to place its data into a tablespace with a larger page size as well.

I am assuming he will drop the old tablespace for indexes and recreate it with a larger page size. In that case, I think he can just recreate the index tablespace without recreating the table and without changing the tablespace for the table data.

Thanks Marcus.
The tablespace which contain only tables has more than 50 % free space. So really I like to avoid to drop and recreate this tablespace as there are more than 100 tables in that tablespace.
So I thought to drop and recreate only the index tablespace with 16K page then recreate the indexes.
Can someone advise me that this is correct approach? May I see any performance issues due to different page size?
Thanks..

why can't you re-size existing or add new containers by using ALTER TABLESPACE command?

Depending on how the table and its indexes are used it might be a good idea to create separate tablespace and indexspace for this big table. When I worked for IBM we had a rule that every table had its own tablespace (it was db2 v7 on z/OS).

Having one tablespace for each table is only advisable for DB2 z/OS, not LUW.

The OP already has a separate tablespace for the tables and the indexes.

Actually it appears that Nick is correct and you cannot drop a tablespace that is defined as the index tablespace, even if there are no indexes in it, so long as the table data is in a different tablespace (and you don't drop the table). I just tried it and got SQL0282N.

Would not it make sense to have a separate TS and IndexSpace for a large heavily used table/index to improve perfomance in this case? I mean no other tables in the same TS and not other indexes in the same IS. Even if it's on Windows. Thanks

Putting a table, even if it is "heavily used", into a separate tablespace does not magically improve performance, so the general answer is "no".

A lot depends on whether you have an OLTP or data warehouse application. Most OLTP applications have a bufferpool hit ratio of at least 90% (many have 98%). Even with an data warehouse applicaiton, it would only help if DB2 was accessing two tables on disk (not in a bufferpool) at exactly the same time. But in terms of performance it is usually better to have a tablespace spread accross multiple devices (for example, by having multiple containers on different disks or arrays), rather than worrying about having a lot of different tablspaces.

Certainly there are some administrative reasons for have multiple tablespaces (which are mostly different reasons than for z/OS). Chief among the reasons in LUW is that bufferpool assignments are at the tablespace level.