Problem solve Get help with specific problems with your technologies, process and projects.

Two camps of opinion on tablespace allocation and index location

I'm a Database Team Leader on a project to convert from VSAM to DB2. Everyone is new at DB2 and right now, I have 2 camps of opinion on Tablespace allocation and Index location.

- One camp wants to create each table in its own tablespace including the Indexes, claiming different bufferpools will avoid performance contention.

- The other camp wants to group multiple tables in one Tablespace and segregate Indexes into an aggregated Index tablespace, claiming this is the better way to avoid performance contention.

Please give us your tie-breaking vote.

Camp one is right, but only sort of. Let me re-state that. Follow camp one for tables, but ignore the index recommendation. DB2 handles indexes by creating an index space for each index - and it does that automatically, you cannot change it.

As for tables, the basic rule of thumb is to put each table into its own tablespace. This is done because DB2 utilities operate at the tablespace level - not the table level. So, if you run a LOAD utility on a table with the REPLACE option all of the data in the tablespace where that table is defined will be replaced - even other tables (if other tables exist in that tablespace). So, one table per tablespace is just about an industry standard.

In some instances, for small, static tables where utilities will not be run on them, you might consider putting several of them into a single tablespace. But I would not recommend this for you since you are new to DB2 and might have difficulty determining which ones should be done this way.

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.