Distributed DBA: Automatic storage databases

Let DB2 decide where your table spaces live

Learn how database storage is simplified and optimized when you use automatic storage databases in DB2 for Linux, UNIX, and Windows.

This article was originally published in IBM Data magazine. This content is part of the IBM Data Management magazine.

Share:

Roger E. Sanders, Senior Consultant Corporate Systems Engineer, EMC Corporation

Roger SandersRoger E. Sanders (roger_e_sanders@yahoo.com), a senior consultant corporate systems engineer at EMC Corporation, is the author of 21 books on DB2 for Linux, UNIX, and Windows and a recipient of the 2011 IBM Information Champion award. His latest book is titled From Idea to Print: How to Write a Technical Article or Book and Get It Published.



01 August 2011

Also available in Chinese

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
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]>>

where:

  • 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 execute a CREATE TABLESPACE statement that looks like this:

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 executing the ALTER DATABASE statement. The syntax for this statement is:

Listing 3. Syntax for ALTER DATABASE command
ALTER DATABASE [DatabaseName]
&[ADD | DROP] STORAGE ON [StoragePath ,...]

where:

  • 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 like this:

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
Storage configuration for an automatic storage database

Conclusion

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.

Resources

Learn

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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=742716
ArticleTitle=Distributed DBA: Automatic storage databases
publish-date=08012011