Command Reference

CREATE DATABASE

Initializes a new database with an optional user-defined collating sequence, creates the three initial table spaces, creates the system tables, and allocates the recovery log.

This command is not valid on a client.

Scope

In a multi-node environment, this command affects all nodes that are listed in the db2nodes.cfg file.

The node from which this command is issued becomes the catalog node for the new database.

Authorization

One of the following:

sysadm
sysctrl

Required Connection

Instance. To create a database at another (remote) node, it is necessary to first attach to that node. A database connection is temporarily established by this command during processing.

Command Syntax

>>-CREATE----+-DATABASE-+--database-name------------------------>
             '-DB-------'
 
>-----+------------------------------+-------------------------><
      +-AT NODE----------------------+
      '-| Create Database options |--'
 
Create Database options
 
|---+----------------+---+------------------------+------------->
    '-ON--+-path--+--'   '-ALIAS--database-alias--'
          '-drive-'
 
>-----+-----------------------------------------------+--------->
      '-USING CODESET--codeset--TERRITORY--territory--'
 
>-----+-----------------------------------+--------------------->
      |                .-SYSTEM--------.  |
      '-COLLATE USING--+-COMPATIBILITY-+--'
                       '-IDENTITY------'
 
>-----+-------------------+------------------------------------->
      '-NUMSEGS--numsegs--'
 
>-----+--------------------------------+------------------------>
      '-DFT_EXTENT_SZ--dft_extentsize--'
 
>-----+----------------------------------------+---------------->
      '-CATALOG TABLESPACE--| tblspace-defn |--'
 
>-----+-------------------------------------+------------------->
      '-USER TABLESPACE--| tblspace-defn |--'
 
>-----+------------------------------------------+-------------->
      '-TEMPORARY TABLESPACE--| tblspace-defn |--'
 
>-----+-------------------------+-------------------------------|
      '-WITH--"comment-string"--'
 
tblspace-defn
 
|---MANAGED BY-------------------------------------------------->
 
                         .-,--------------------.
                         V                      |
>-----+-SYSTEM USING--(-----'container-string'--+---)-------------------------------------+>
      |                    .-,-----------------------------------------------------.      |
      |                    V                                                       |      |
      '-DATABASE USING--(------+-FILE---+---'container-string'---number-of-pages---+---)--'
                               '-DEVICE-'
 
>-----+------------------------------+-------------------------->
      '-EXTENTSIZE--number-of-pages--'
 
>-----+--------------------------------+------------------------>
      '-PREFETCHSIZE--number-of-pages--'
 
>-----+-----------------------------------+--------------------->
      '-OVERHEAD--number-of-milliseconds--'
 
>-----+---------------------------------------+-----------------|
      '-TRANSFERRATE--number-of-milliseconds--'

Notes:

The code set and territory values specified must be a valid combination. For a list of valid combinations, see one of the Quick Beginnings books.
For details on the tblspace-defn parameters, see the CREATE TABLESPACE statement in the SQL Reference. The table space definitions specified on CREATE DATABASE apply to all nodes on which the database is being created. They cannot be specified separately for each node. If the table space definitions are to be created differently on particular nodes, the CREATE TABLESPACE statement must be used.
When defining containers for table spaces, $N can be used. $N will be replaced by the node number when the container is actually created. This is required if the user wants to specify containers in a multiple logical node database.

Command Parameters

DATABASE database-name

A name to be assigned to the new database. This must be a unique name that differentiates the database from any other database in either the local database directory or the system database directory. The name must conform to naming conventions for databases.

AT NODE

Specifies that the database is to be created only on the node that issues the command. This parameter is not intended for general use. For example, it should be used with RESTORE DATABASE if the database partition at a node was damaged and must be re-created. Improper use of this parameter can cause inconsistencies in the system, so it should only be used with caution.

Note:

If this parameter is used to recreate a database partition that was dropped (because it was damaged), the database at this node will be in the restore-pending state. After recreating the database partition, the database must immediately be restored on this node.

ON path/drive

On UNIX based systems, specifies the path on which to create the database. If a path is not specified, the database is created on the default database path specified in the database manager configuration file (dftdbpath parameter). Maximum length is 205 characters. On OS/2 or the Windows operating system, specifies the letter of the drive on which to create the database.

Note: For MPP systems, a database should not be created in an NFS-mounted directory. If a path is not specified, ensure that the dftdbpath database manager configuration parameter is not set to an NFS-mounted path (for example, on UNIX based systems, it should not specify the $HOME directory of the instance owner). The path specified for this command in an MPP system cannot be a relative path.

ALIAS database-alias

An alias for the database in the system database directory. If no alias is provided, the specified database name is used.

USING CODESET codeset

Specifies the code set to be used for data entered into this database.

TERRITORY territory

Specifies the territory to be used for data entered into this database.

COLLATE USING

Identifies the type of collating sequence to be used for the database. Once the database has been created, the collating sequence cannot be changed.

COMPATIBILITY: The DB2 Version 2 collating sequence. Some collation tables have been enhanced. This option specifies that the previous version of these tables is to be used.
IDENTITY: Identity collating sequence, in which strings are compared byte for byte.
SYSTEM: Collating sequence based on the current territory.

For information about how the database collating sequence is used, see the SQL Reference.

NUMSEGS numsegs

Specifies the number of segment directories that will be created and used to store DAT, IDX, LF, LB, and LBA files for any default SMS table spaces. This parameter does not affect DMS table spaces, any SMS table spaces with explicit creation characteristics (created when the database is created), or any SMS table spaces explicitly created after the database is created.

DFT_EXTENT_SZ dft_extentsize

Specifies the default extent size of table spaces in the database.

CATALOG TABLESPACE tblspace-defn

Specifies the definition of the table space which will hold the catalog tables, SYSCATSPACE. If not specified, SYSCATSPACE will be created as a System Managed Space (SMS) table space with numsegs number of directories as containers, and with an extent size of dft_extentsize. For example, the following containers would be created if numsegs were specified to be 5:

   /u/smith/smith/NODE0000/SQL00001/SQLT0000.0 
   /u/smith/smith/NODE0000/SQL00001/SQLT0000.1 
   /u/smith/smith/NODE0000/SQL00001/SQLT0000.2 
   /u/smith/smith/NODE0000/SQL00001/SQLT0000.3 
   /u/smith/smith/NODE0000/SQL00001/SQLT0000.4

In an MPP system, the catalog table space is only created on the catalog node (the node on which the CREATE DATABASE command is issued).

USER TABLESPACE tblspace-defn

Specifies the definition of the initial user table space, USERSPACE1. If not specified, USERSPACE1 will be created as an SMS table space with numsegs number of directories as containers, and with an extent size of dft_extentsize. For example, the following containers would be created if numsegs were specified to be 5:

   /u/smith/smith/NODE0000/SQL00001/SQLT0001.0 
   /u/smith/smith/NODE0000/SQL00001/SQLT0001.1 
   /u/smith/smith/NODE0000/SQL00001/SQLT0001.2 
   /u/smith/smith/NODE0000/SQL00001/SQLT0001.3 
   /u/smith/smith/NODE0000/SQL00001/SQLT0001.4

TEMPORARY TABLESPACE tblspace-defn

Specifies the definition of the initial temporary table space, TEMPSPACE1. If not specified, TEMPSPACE1 will be created as an SMS table space with numsegs number of directories as containers, and with an extent size of dft_extentsize. For example, the following containers would be created if numsegs were specified to be 5:

   /u/smith/smith/NODE0000/SQL00001/SQLT0002.0 
   /u/smith/smith/NODE0000/SQL00001/SQLT0002.1 
   /u/smith/smith/NODE0000/SQL00001/SQLT0002.2 
   /u/smith/smith/NODE0000/SQL00001/SQLT0002.3 
   /u/smith/smith/NODE0000/SQL00001/SQLT0002.4

WITH "comment-string"

Describes the database entry in the database directory. Any comment that helps to describe the database can be entered. Maximum length is 30 characters. A carriage return or a line feed character is not permitted. The comment text must be enclosed by single or double quotation marks.

Usage Notes

CREATE DATABASE:

Creates a database in the specified subdirectory. In an MPP system, creates the database on all nodes listed in db2nodes.cfg, and creates a $DB2INSTANCE/NODExxxx directory under the specified subdirectory at each node. In a non-MPP system, creates a $DB2INSTANCE/NODE0000 directory under the specified subdirectory.
Creates the system catalog tables and recovery log.
Catalogs the database in the following database directories:
- server's local database directory on the path indicated by path or, if the path is not specified, the default database path defined in the database manager system configuration file. A local database directory resides on each file system that contains a database.
- server's system database directory for the attached instance. The resulting directory entry will contain the database name and a database alias.
  If the command was issued from a remote client, the client's system database directory is also updated with the database name and an alias.
Creates a system or a local database directory if neither exists. If specified, the comment and code set values are placed in both directories.
Stores the specified code set, territory, and collating sequence. A flag is set in the database configuration file if the collating sequence consists of unique weights, or if it is the identity sequence.
Creates the schemata called SYSCAT, SYSFUN, SYSIBM, and SYSSTAT with SYSIBM as the owner. The server node on which this command is issued becomes the catalog node for the new database. Two nodegroups are created automatically: IBMDEFAULTGROUP and IBMCATGROUP. For more information, see the SQL Reference.

Binds the previously defined database manager bind files to the database (these are listed in the utilities bind file list, db2ubind.lst). If one or more of these files do not bind successfully, CREATE DATABASE returns a warning in the SQLCA, and provides information about the binds that failed. If a bind fails, the user can take corrective action and manually bind the failing file. The database is created in any case. A schema called NULLID is implicitly created when performing the binds with CREATEIN privilege granted to PUBLIC.

Note:

The utilities bind file list contains two bind files that cannot be bound against down-level servers:

db2ugtpi.bnd cannot be bound against DB2 Version 2 servers.
db2dropv.bnd cannot be bound against DB2 Parallel Edition Version 1 servers.

If db2ubind.lst is bound against a down-level server, warnings pertaining to these two files are returned, and can be disregarded.

Creates SYSCATSPACE, TEMPSPACE1, and USERSPACE1 table spaces. The SYSCATSPACE table space is only created on the catalog node.
Grants the following:
- DBADM authority, and CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA privileges to the database creator
- CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA privileges to PUBLIC
- USE privilege on the USERSPACE1 table space to PUBLIC
- SELECT privilege on each system catalog to PUBLIC
- BIND and EXECUTE privilege to PUBLIC for each successfully bound utility.

With dbadm authority, one can grant these privileges to (and revoke them from) other users or PUBLIC. If another administrator with sysadm or dbadm authority over the database revokes these privileges, the database creator nevertheless retains them.

In an MPP environment, the database manager creates a subdirectory, $DB2INSTANCE/NODExxxx, under the specified or default path on all nodes. The xxxx is the node number as defined in the db2nodes.cfg file (that is, node 0 becomes NODE0000). Subdirectories SQL00001 through SQLnnnnn will reside on this path. This ensures that the database objects associated with different nodes are stored in different directories (even if the subdirectory $DB2INSTANCE under the specified or default path is shared by all nodes).

If LDAP (Lightweight Directory Access Protocol) support is enabled on the current machine, the database will be automatically registered in the LDAP directory. If a database object of the same name already exists in the LDAP directory, the database is still created on the local machine, but a warning message is returned, indicating that there is a naming conflict. In this case, the user can manually catalog an LDAP database entry by using CATALOG LDAP DATABASE.

CREATE DATABASE will fail if the application is already connected to a database.

Use CATALOG DATABASE to define different alias names for the new database.

See Also

BIND

CATALOG DATABASE

DROP DATABASE.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]