Hi, I have a database with about 50 tables.
If I now wanna create some tablespaces for these tables, lets say 10 tables in each tablespace, that would be 5 tablespaces :-)

My question is now, could there be some problems when having tables with relations in different tablespaces, perfomance etc..

I have also heard that is is a good idea to create different tablespaces for the data and for the indexes.. it that a good idea ? That would then be 10 tablespaces.

Regards

Kris

this very much depends upon the size of your tables, what kind of system you have.
when you want to seperate indexes and data you have to use DMS tablespaces, which some find tiresom.
If you are new to DB2, go ahead with SMS instead, keep your life simple.
Choose the right extend size (a multiple of your stripe size), that can be important for I/O performance depending on your hardware

Still, it can be a good idea to give large tables their own tablespace, then you don't have to worry about load operations affecting access for other tables in the same tablespace.
(not sure about version 8, there have been some improvements here)

It does not matter for RI in which tablespaces your tables are.

One of the main reasons for putting objects in different tablespaces is that tablespaces are assigned to specific bufferpools. If you only have one bufferpool, or your tables and indexes are in the same bufferpool, then they can be in the same tablespace.

The other reason is to reduce disk contention on simultaneous access of mutliple objects. However, if you have a SAN or NAS disk subsystem, and/or you have a reasonable amount of bufferpool memory, then this is not very important. If may be somewhat more important if you have a large data warehouse application with a lot of table scans.