DB2: Is it good to create the index table space to use 32 KB data page

Question

I found our DB2 server configs all the index into one table space. And it then defines the table space to use 32kb data page. Is this good? How to judge it? Any advice is welcome.

Answer 1

From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning LOB type data as well as deprecated types like LONG VARCHAR) into their own tablespaces.

Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on your needs. For example, table data is "more" important than indexes (as indexes have no purpose without data in the tables). So if your indexes are in a separate tablespace and end up corrupted or needing a restore, you only have to restore the indexes, not the whole table (of course you may need to run reorgs and runstats to make sure your indexes are up to date).

Regarding LOB's - unless they are declared INLINE, they are not stored directly with the table data anyway (and INLINE only works for LOB's of a certain size). Hopefully you don't have too much update activity occuring on your LOB (as that can be a little costly seeing as LOB's are never cached in a bufferpool and always retreived from disk). So having them in another tablespace can help there as well. You have a separate space to store the data and if the table data is corrupted/needing a restore, but the LOB's are fine, again, you could restore just the table data without losing your LOBs.

So if you think of IBM's recommendation. They would recommend you build your table as such:

CREATE TABLE MY_TABLE (
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
TEXT VARCHAR(100),
...
primary key (ID))
IN TS_DAT
INDEX in TS_IND
LONG in TS_LOB; --or TS_LONG if you wish.

As for size....I would recommend that you only put your tables and indexes and long/LOB data in the right sized page. If your indexes don't require a 32K tablespace, why put them there? It just wastes pages (a lot of space on the page will be ununsed) and thus waste disk. If you need to later move the indexes or the tables or the long/lob to a higher page size tablespace, it isn't too hard to do that later. (Of course you may need to have a maintenance window to do it in, but again, I think...even though disk is "cheap" so to say...why waste money?).

We used to put the tables in whatever size they needed and put the indexes only in a 4K unless they needed larger, figuring we could always change them later. But we recently have started to make sure the indexes are in the same tablespace size as the table data and LOB data. Reason being is you cannot change a tablespace after a table is created. To move the data you essentially have to create a new table and move all the data from one to the other.

Same goes for a few LOB's we have in other tables. I figured out their size and put them in an appropriately sized tablespace.

I have found the following pages on Space requirements for storage very helpful for understanding what sizes for tables and LOB's determine the required page size (and thus tablespace). The Space requirements for indexes link is helpful for indexes. Note: this is for DB2 9.7. If you need a different version, trying changing the number in the link, or start from IBM's main page.