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:
Listing 1. Syntax for CREATE DATABASE command
CREATE [DATABASE | DB] [DatabaseName] <AUTOMATIC STORAGE YES> <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:
Listing 2. Sample code for CREATE DATABASE command
CREATE DB my_db AUTOMATIC STORAGE YES ON /mnt /db_data1, /mnt /db_data2, /mnt /db_data3
And if you want to create a table space named MY_SPACE1 in this database, simply
CREATE TABLESPACE statement that looks like
CREATE TABLESPACE my_space1
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
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
ALTER DATABASE statement. The syntax for
this statement is:
Listing 3. Syntax for ALTER DATABASE command
ALTER DATABASE [DatabaseName] &[ADD | DROP] STORAGE ON [StoragePath ,...]
- DatabaseNameidentifies 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
Listing 4. Sample code for ALTER DATABASE command
ALTER DATABASE test_db ADD STORAGE ON /mnt/db_data1,/mnt/db_data2
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.
Finally, there’s an important caveat you need to remember: 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.
- Get more information about automatic storage in DB2 for Linux, UNIX, and Windows in the Information Center.
- In the DB2 best practices document on Database storage, get guidelines and recommendations for database storage on DB2 for Linux, UNIX, and Windows, including automatic storage.
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
Get products and technologies
- Download a free trial version of DB2 for Linux, UNIX, and Windows.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.