Single Tablespace or Separate Tablespaces

Hi,

Does having separate DB2 tablespaces for data and indexes make any performance difference on a single disk/partitioned system? Or is it better to use a single tablespace to store both data and indexes?

Regards,
Raveen Sundram

re: Single Tablespace or Separate Tablespaces

It can do. If different tablespaces are used then different parameters can be assigned. E.g.Different buffer pool, page size, extent size etc. Bufferpool could be the main reason as the pages will be cahced in separate memory areas with this approach. Also, if the tables are large using a 32K page size for the indexspace can improve performance as more leaf pages can be contained in the pages (if LARGE tablespaces used) and hence the number of index levels reduced.

re: Single Tablespace or Separate Tablespaces

Hi cburnett,

Thanks for the reply.

My largest record length/size or total column size is 7K but the average is about 4K.

I've declared 3 x 16K tablspaces - 1 for DATA, 1 for INDEXES and 1 for LONG DATA, with a 16K extend size. But all these tablespaces are attached to 1 x 16K bufferpool.

Is this optimum? Or should I have seperate bufferpools for each tablespace?

Thanks

Regards,
Raveen Sundram

re: Single Tablespace or Separate Tablespaces

It's difficult to know without details of the workload. If the indexes and the data are being probed randonmly then having these in the same bufferpool is probably appropriate. If you are doing table or index scans then they may be better separated (so that the scan doesn't roll out the recently used pages).

I'm assuming here you are on V9. If you are, then there is a good reason for defining the LONG data separately: FILE SYSTEM CACHING. The default with this version is to bypass the OS file system cache which is usually more efficient. However LOB values are read directly and do not use the bufferpools. Therefore accepting the default means physical I/O in this case. This would be apporpriate if LOBs are read infrequently (better to give the memory to the data/index bufferpool(s), however if LOBs are accessed frquently, then specifying FILE SYSTEM CACHING for this tablespace would be recommended.