DB2 Version 9.7 for Linux, UNIX, and Windows

Creating automatic storage databases

All databases are created as automatic storage databases unless you specify otherwise. When you create a database with automatic storage, you establish one or more initial storage paths for it. As the database grows, the database manager creates, extends and adds containers across those storage paths.

About this task

When you create an automatic storage database, you associate one or more storage paths with the database that are used by automatic storage table spaces. Compared to other types of table spaces, automatic storage table spaces reduce the maintenance tasks you must perform.

Restrictions

Storage paths cannot be specified using relative path names; you must use absolute path names. The storage path can be 175 characters long.
On Windows operating systems, the database path must be a drive letter only, unless the DB2_CREATE_DB_ON_PATH registry variable is set to YES.
If you do not specify a database path using the DBPATH ON clause of the CREATE DATABASE command, the database manager uses the first storage path specified for the ON clause for the database path. (On Windows operating systems, if this clause is specified as a path, and if the DB2_CREATE_DB_ON_PATH registry variable is not set to YES, you receive a SQL1052N error message.) If no ON clause is specified, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter). This will also be used as the location for the single storage path associated with the database.
For partitioned databases, you must use the same set of storage paths on each database partition (unless you use database partition expressions).
Database partition expressions are not valid in database paths, whether you specify them explicitly by using the DBPATH ON clause of the CREATE DATABASE command, or implicitly by using a database partition expression in the first storage path.
You cannot disable automatic storage for a database if it has been created with automatic storage.
An automatic storage database must have at least one storage path associated with it.

Before you begin

The DB2® database must be running. Use the db2start to start the database manager.

Procedure

To create a database with automatic storage:

Formulate a CREATE DATABASE command. By default, new databases are created as automatic storage databases unless you specify otherwise. You can also include the AUTOMATIC STORAGE YES clause on the create database command. For example:
```
CREATE DATABASE DATAB1
CREATE DATABASE DATAB1 AUTOMATIC STORAGE YES 
```
are equivalent to one another.
Run the CREATE DATABASE command.

Example

Example 1: Creating an automatic storage database on a UNIX or Linux operating system:

To create a database named TESTDB1 on path /DPATH1 using /DATA1 and /DATA2 as the storage paths, use the following command:

   CREATE DATABASE TESTDB1 ON '/DATA1','/DATA2' DBPATH ON '/DPATH1'

Example 2: Creating an automatic storage database on a Windows operating system, specifying both storage and database paths:

To create a database named TESTDB2 on drive D:, with storage on E:\DATA, use the following command:

   CREATE DATABASE TESTDB2 ON 'E:\DATA' DBPATH ON 'D:'

Example 3: Creating an automatic storage database on a Windows operating system, specifying only a storage path:

To create a database names TESTDB3 with storage on drive F:, use the following command:

   CREATE DATABASE TESTDB3 AUTOMATIC STORAGE YES ON 'F:'

In this example, F: is used as both the storage path and the database path.

If you specify a directory name such as F:\DATA for the storage path, the command fails, because:

When DBPATH is not specified, the storage path - in this case, F:\DATA - is used as the database path
On Windows, the database path can only be a drive letter (unless you change the default for the DB2_CREATE_DB_ON_PATH registry variable from NO to YES).

If you want to specify a directory as the storage path on Windows operating systems, you must also include the DBPATH ON drive clause, as shown in Example 2.

Example 4: Creating an automatic storage database on a UNIX or Linux operating system without specifying a database path:

To create a database names TESTDB4 with storage on /DATA1 and /DATA2, use the following command:

   CREATE DATABASE TESTDB4 ON '/DATA1','/DATA2'

In this example, /DATA1 and /DATA2 are used as the storage paths and /DATA1 is the database path.

What to do next

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