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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning
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
So if you think of IBM's recommendation. They would recommend you build your table as such:
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.