Automatic Storage Databases

Let DB2 decide where table spaces live

DB2 for LUW Offering Manager, IBM

Table spaces have long been used to provide a layer of indirection between database objects (tables, indexes, views, and so forth) and the storage containers (directories, files, or raw logical volumes/disk partitions) where data physically resides. And DBAs have often been responsible for creating any table spaces needed, monitoring table space storage consumption, and providing additional capacity to table spaces in danger of running out of space. However, beginning with DB2 8.2.2, DBAs have been able to offload a significant amount of this work to the DB2 database manager by creating what are known as “automatic storage” databases.

In this column, I’ll introduce you to the concept of automatic storage and I’ll show you how to construct a simple automatic storage database. I’ll also show you how to use a feature introduced in DB2 9.7 to convert an existing “traditional” database into one that uses automatic storage.

How database storage has traditionally been managed

DB2 supports two very different types of table spaces: system managed space (SMS) and database managed space (DMS). With SMS table spaces, directories are used as storage containers and the server operating system’s file manager is responsible for controlling how the space is utilized; DB2 creates files in the directories for objects that have been assigned to the table space and the file system is responsible for managing their growth. With DMS table spaces, files or raw logical volumes/disk partitions are used as storage containers instead, and DB2 is responsible for controlling how the space in those containers is employed.

When a database is first created, three table spaces are created by default. These table spaces are as follows:

  • A large table space named SYSCATSPACE, which is used to store the system catalog tables and views associated with the database
  • A large table space named USERSPACE1, which is used to store all user-defined objects along with table data, index data, large object (LOB) data, and long value data
  • A system temporary table space named TEMPSPACE1, which is used as a temporary storage area for certain operations

By default, these table spaces are SMS table spaces that use separate subdirectories on a single file system as storage for the database. (Additional table spaces can be created by executing the CREATE TABLESPACE statement.) Figure 1 shows the underlying storage for a database containing the three default table spaces, plus a DMS table space named MY_SPACE1 that uses two different files (residing on two separate file systems) for its storage.

When a table space spans multiple containers, data is written in a round-robin fashion—in groups of pages called extents—to each container assigned to that table space. This approach helps balance data across all containers used. Thus, the DMS table space MY_SPACE1 shown in Figure 1 uses two file containers since this is the way such a table space would most likely be created.

Figure 1: Storage configuration for a traditional DB2 database

How automatic storage works

Instead of associating storage containers with individual SMS and DMS table spaces, you associate storage paths with the database as a whole when you create an automatic storage database. Containers for the default table spaces, as well as any other table spaces that are defined, are then automatically created on each storage path used. The table spaces themselves take on the characteristics of auto-resizing DMS file table spaces (for permanent data) or SMS table spaces (for temporary data). And as the database grows, the DB2 database manager automatically extends the appropriate containers (or creates new ones) to meet the database’s storage needs. Figure 2 shows the underlying storage for an automatic storage database containing the three default table spaces and one user-defined one named MY_SPACE1.

Creating an automatic storage database

A database can be configured to use automatic storage when it is first created, and with DB2 9.1 and later, new databases are created using automatic storage by default. The storage paths that are to be associated with the database are provided as part of the CREATE DATABASE command used to create the database; the syntax for the simplest form of this command looks something like this:

CREATE [DATABASE | DB] [DatabaseName]
<ON [StoragePath ,...]
<DBPATH [DBPath]>>


  • DatabaseName identifies the unique name that is to be assigned to the database to be created.
  • StoragePath identifies one or more storage paths that are to be used to hold table space containers for each table space defined in the database.
  • DBPath identifies the location where the database metadata and transaction logs associated with the database are to be physically stored. (If this parameter is not specified, these files will be stored in the first storage path specified in the StoragePath parameter.)

Thus, if you wanted to create a database named MY_DB that uses automatic storage, stripes its data across three file systems, and stores its metadata and transaction logs on the first file system specified, you would execute a command that looks like this:

ON /mnt/db_data1, /mnt/db_data2,

And if you want to create a table space named MY_SPACE1 in this database, simply execute a CREATE TABLESPACE statement that looks like this:


The resulting table space will have a container on each storage path used by the database. If a DMS table space is created, 32 MB of storage space will be allocated for the table space initially; to determine the size of each container used, divide this number by the number of file systems the database spans.

Converting an existing database to an automatic storage database

Beginning with DB2 9.7, you can modify an existing database—even a database that was not created as an automatic storage database—to use automatic storage simply by executing the ALTER DATABASE statement. The syntax for this statement is:

[ADD | DROP] STORAGE ON [StoragePath ,...]


  • DatabaseName identifies the name of the database that is to be altered.
  • StoragePath identifies one or more storage paths that are to be used to hold table space data for each table space defined in the database—if storage paths are to be added. If storage paths are to be dropped, this parameter identifies one or more storage paths that are to be removed from the collection of storage paths that are used to hold data for automatic storage table spaces.

Thus, to modify an existing database named TEST_DB so that it uses automatic storage, you would execute an ALTER DATABASE command that looks like this:

ADD STORAGE ON /mnt/db_data1,

When executed, this statement has the effect of both adding two new storage paths to the existing database, as well as enabling the database for automatic storage; any future table spaces created will use automatic storage by default. However, existing nonautomatic storage table spaces will not automatically be converted. Instead, you must use the ALTER TABLESPACE… MANAGED BY AUTOMATIC STORAGE statement to make the conversions yourself. Only DMS table spaces can be converted; the conversion of SMS table spaces is not allowed.

There’s an important caveat to keep in mind: once a database has been created or converted to use automatic storage, it cannot be converted to a nonautomatic storage database.

Figure 2: Storage configuration for an automatic storage database

The primary reason for the introduction of the automatic storage model was to simplify the management of database storage, while retaining the performance characteristics typically found when DMS table spaces are used. (DMS table spaces tend to be faster, in terms of input/output operations per second or IOPS, than SMS table spaces.) There may be situations where a DBA must retain full control over how the storage for a particular table space is provided, but in most cases, databases will benefit from the use of automatic storage.