Space management

Select how you want the table space containers managed.

The space management can be changed later, by adding new table spaces to an existing database.

Select either of the following:

Low maintenance -- System-Managed Space (SMS)

The table's files are created by the database and stored in the specified directory. The operating system manages the files. If you do not specify a path for the container, the database manager will create one on the default drive.

High performance -- Database-Managed Space (DMS)

The database creates one large file (container) as specified. Data is stored in this container and managed by the database. You must specify a size and the location of a DMS container.

If you can use more than one physical drive, you can improve the performance of your database by adding a container to each drive.

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 an 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 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 64GB of regular table data, 64GB of index data and 2TB 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.

[ Top of Page | Table of Contents | Glossary | Index ]

[ DB2 List of Books | Search the DB2 Books ]