You create a database using the CREATE DATABASE command.
To create a database from a client application, call the sqlecrea API.
All databases are created with the default storage group IBMSTOGROUP,
unless you specify otherwise. Automatic storage managed table spaces
use storage groups for their storage definitions.
Before you begin
The DB2® database
manager must be running. Use the db2start command
to start the database manager.
It is important to plan your
database, keeping in mind the contents, layout, potential growth,
and how it will be used before you create it. After it has been created
and populated with data, changes can be made.
The following
database privileges are automatically granted to PUBLIC: CREATETAB,
BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT on the system catalog
views. However, if the RESTRICTIVE option is
present, no privileges are automatically granted to PUBLIC. For more
information about the RESTRICTIVE option, see
the CREATE DATABASE command.
Restrictions
- Storage paths cannot be specified using relative path names; you
must use absolute path names. The storage path can be up to 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).
The path is also 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.
- A storage group must have at least one storage path associated
with it.
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
When you create a database, each of the following tasks
are done for you:
- Setting up of all the system catalog tables that are needed by
the database
- Allocation of the database recovery log
- Creation of the database configuration file and the default values
are set
- Binding of the database utilities to the database
Procedure
- To create a database from a client application, call the sqlecrea API.
- To create a database using the command line processor,
issue the CREATE DATABASE command.
For
example, the following command creates a database called PERSON1,
in the default location, with the associated comment "Personnel DB
for BSchiefer Co".
CREATE DATABASE person1
WITH "Personnel DB for BSchiefer Co"
- To create a database using IBM® Data Studio,
right-click the instance on which you want to create the database
and select the task assistant to the create it.
Example
Example 1: Creating a 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 defined to the default storage group IBMSTOGROUP,
use the following command:
CREATE DATABASE TESTDB1 ON '/DATA1','/DATA2' DBPATH ON '/DPATH1'
Example
2: Creating a 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:'
In
this example,
E:\DATA is used as both the storage
path defined to the default storage group IBMSTOGROUP and the database
path.
Example 3: Creating a database on a Windows operating system, specifying only
a storage path:
To create a database named TESTDB3 with
storage on drive
F:, use the following command:
CREATE DATABASE TESTDB3 ON 'F:'
In
this example,
F: is used as both the storage
path defined to the default storage group IBMSTOGROUP 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 a database on
a UNIX or Linux operating system without specifying a
database path:
To create a database named 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 defined to the default storage group IBMSTOGROUP
and
/DATA1 is the database path.
What to do next
- Configuration Advisor
- The Configuration Advisor helps you to tune performance and to
balance memory requirements for a single database per instance by
suggesting which configuration parameters to modify and providing
suggested values for them. The Configuration Advisor is automatically
invoked by default when you create a database.
You can override
this default so that the configuration advisor is not automatically
invoked by using one of the following methods:
- Event Monitor
- At the same time a database is created, a detailed deadlocks event
monitor is also created. As with any monitor, there is extra processing
time and resources associated with this event monitor. If you do not
want the detailed deadlocks event monitor, then the event monitor
can be dropped by using the command:
DROP EVENT MONITOR db2detaildeadlock
To
limit the amount of disk space that this event monitor consumes, the
event monitor deactivates, and a message is written to the administration
notification log, once it has reached its maximum number of output
files. Removing output files that are no longer needed allows the
event monitor to activate again on the next database activation.
- Remote databases
You can create a database in a different, possibly remote,
instance. To create a database at another (remote) database partition
server, you must first attach to that server. A database connection
is temporarily established by the following command during processing:
CREATE DATABASE database_name AT DBPARTITIONNUM options
In
this type of environment, you can perform instance-level administration
against an instance other than your default instance, including remote
instances. For instructions on how to do this, see the
db2iupdt (update
instance) command.
- Database code pages
By default, databases are created in the UTF-8 (Unicode) code
set.
To override the default code page for the database, it
is necessary to specify the required code set and territory when creating
the database. See the CREATE DATABASE command or
the sqlecrea API for information about setting
the code set and territory.