Space management

There are a number of trade-offs to consider when determining which type of table space you should use to store your data.

Advantages of a system-managed space (SMS) table space:

• Space is not allocated by the system until it is required.
• Creating a database requires less initial work since you do not have to predefine the containers.

Advantages of a database-managed space (DMS) table space:

• The size of a table space can be increased by adding containers, using the ALTER TABLESPACE statement.
• A table can be split across multiple table spaces based on the type of data being stored:
  • Long field and LOB data
  • Indexes
  • Regular table data

  You might want to separate your table data for performance reasons, or to increase the amount of data stored for a table. For example, you could have a table with 64 GB of regular table data, 64 GB of index data and 2 TB of long data.

• The location of the data on the disk can be controlled, if the operating system allows this.
• If all table data is in a single table space, a table space can be dropped and redefined with less overhead than it would take to drop and redefine a table.
• In general, a well-tuned set of DMS table spaces will outperform SMS table spaces.

In general, small personal databases are easiest to manage with SMS table spaces. On the other hand, for large, growing databases you will probably only want to use SMS table spaces for the temporary table spaces, and separate DMS table spaces, with multiple containers, for each table. In addition, long fields and indexes would be stored in their own table spaces.