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.