Hi !
A collegue of mine had created several tables in juste one tablespace.
I'm wondering what is the best solution
1/ One table space per table
2/ Or one tablespace for several tables ?
Could i have the advantages and inconvenients of these propositions ?
If you tell me that the best solution is the solution 2:
How could i correct it ?
a/ I must create new tablespace for each table
b/ I must alter my table (could you join sample) ?
c/ What about data if i change the tablespace of each table ? (should i unload data before alter my table ?)

Thanks a lot
Cheers

for large tables it is recommended to place each in its own tablespace - dms if possible and you could also split off data and index to their own ts
this requires export table - drop-create table - load table
maintenance is easier for individual table/ts
also for backup of ts - you backup only 1 table
for small tables : place tables with same behavior in same ts.
more ts = more monitoring for space usage....

In my experience it doesn't matter wether you
have large and small tables grouped together
in physical storage i.e. tablespaces.
If they don't have notably different specs i group
my tables physically according to the front end
systems, i.e. logical divisions.

Many tablespaces signifies lots of maintenance
and DMS TS can always be expanded - which
is oftenmost the case

Again pros and cons - but grab a manual

I understand that for large tables which are frequently used, using one tab/tbsp impacts the caching algorithm on the disk subsystem.

However, having a large number of tablespaces also impacts performance since at any point in time, the db can have so many file handlers open (maxfilop). If it exceeds this number, then to access data, the db will have to close a handle and open another one for access.

Then there's the restore angle. All tables in a tablespace can only be restored to the same point in time - therefore tables grouped in a tbsp need to have similar recovery requirements.

On DB2 for z/OS, it is usually recommended that you have one table per tablespace. In some situations it is OK to have more than one table per tablespace, but make sure the tablespace is segmented in those cases.

On DB2 for Lnux, UNIX, Windows, you should generally have many tables in a tablespace. The decision about how many tables per tablespace is often determined by the bufferpool configuration, since you assign tablesspaces to bufferpools. If you plan on having 2 bufferpools (one for small tables and all indexes, and another for large tables) then you will need at least 2 different tablespaces.