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
Related Q&A from Craig S. Mullins
Our expert suggests that the best way to pass the SAP BW exam is to have hands-on job experience. But that's not all he has to say about ... Continue Reading
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.