DB2 Version 10.1 for Linux, UNIX, and Windows

Converting a nonautomatic storage database to use automatic storage

You can convert an existing nonautomatic storage database to use automatic storage by using the CREATE STOGROUP statement to define the default storage group within a database.

Before you begin

You must have a storage location that you can identify with a path (for Windows operating systems, a path or a drive letter) available to use as a storage path for your automatic storage table spaces.

Restrictions

Once you have created a storage group, you cannot drop all storage groups for a database.
Only DMS table spaces can be converted to use automatic storage.

Note: Although, you can create a database specifying the AUTOMATIC STORAGE NO clause, the AUTOMATIC STORAGE clause is deprecated and might be removed from a future release.

About this task

Databases that are created specifying the AUTOMATIC STORAGE NO clause of the CREATE DATABASE command do not have storage groups associated with them. Instead, storage is associated with the table spaces for the database. When you define a storage group for a database, existing table spaces are not automatically converted to use automatic storage. By default, only future table spaces that you create are automatic storage table spaces. You must use the ALTER TABLESPACE statement to convert existing table spaces to use automatic storage.

Procedure

You can convert an existing database to an automatic storage database by using the CREATE STOGROUP statement to create a storage group within it.

To create a storage group within a database, use the following statement:

   CREATE STOGROUP sg ON storagePath

where sg is the storage group and storagePath is the path you want to use for automatic storage table spaces.

Example

Example 1: Converting a database on UNIX or Linux operating systems

Assume that the database EMPLOYEE is a nonautomatic storage database, and that /data1/as and /data2/as are the paths you want to use for automatic storage table spaces. To convert EMPLOYEE to an automatic storage database, create a storage group with /data1/as and /data2/as as paths:

   CREATE STOGROUP sg ON '/data1/as', '/data2/as'

Example 2: Converting a database on Windows operating systems

Assume that the database SALES is a nonautomatic storage database, and that F:\DB2DATA and G: are the paths you want to use for automatic storage table spaces. To convert SALES to an automatic storage database, create a storage group with F:\DB2DATA and G: as paths:

   CREATE STOGROUP sg ON 'F:\DB2DATA', 'G:'

What to do next

If you have existing DMS table spaces that you want to convert to use automatic storage, use the ALTER TABLESPACE statement with the MANAGED BY AUTOMATIC STORAGE clause to change them. If you do not specify the USING STOGROUP clause, then the table space uses the storage paths in the designated default storage group.

Once you have created a storage group you can create automatic storage table spaces in which to store tables, indexes and other database objects by using the CREATE TABLESPACE statement.