CREATE DATABASE command

The CREATE DATABASE command 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 file. When you initialize a new database, the AUTOCONFIGURE command is issued by default.

Important: The Triple Data Encryption Standard (3DES) native encryption option is deprecated and might be removed in a future release. As a replacement, use the Advanced Encryption Standard (AES) native encryption option.

Note: When the instance and database directories are created by the Db2® database manager, the permissions are accurate and cannot be changed.

When the CREATE DATABASE command is issued, the Configuration Advisor also runs automatically. It means that the database configuration parameters are automatically tuned for you according to your system resources. In addition, Automated RUNSTATS is enabled. To disable the Configuration Advisor from running at database creation, refer to the DB2_ENABLE_AUTOCONFIG_DEFAULT registry variable. To disable Automated RUNSTATS, refer to the auto_runstats database configuration parameter.

Adaptive Self-Tuning Memory is also enabled by default for single partition databases. To disable Adaptive Self-Tuning-Tuning Memory by default, refer to the self_tuning_mem database configuration parameter. For multi-partition databases, Adaptive Self-Memory is turned off by default.

If no code set is specified on the CREATE DATABASE command, then the collations that are allowed are: SYSTEM, IDENTITY_16BIT, language-aware-collation, and locale-sensistive-collation (SQLCODE -1083). The default code set for a database is UTF-8. If a particular code set and territory is needed for a database, the required code set and territory can be specified in the CREATE DATABASE command.

This command is not valid on a client.

Scope

In a partitioned database environment, this command affects all database partitions that are listed in the db2nodes.cfg file.

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

Authorization

You must have one of the following authorities:

SYSADM
SYSCTRL

Required connection

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 this command during processing.

Command syntax

Create Database options

Tblspace-def

Report-clause

Encryption Options

Master Key Options

Note:

The combination of the code set and territory values must be valid.
Not all collating sequences are valid with every code set and territory combination.
The table space definitions that are specified on CREATE DATABASE apply to all database partitions on which the database is being created. They cannot be specified separately for each database partition. If the table space definitions are to be created differently on particular database partitions, the CREATE TABLESPACE statement must be used.
The $N parameter can be used when containers for table spaces are defined. $N is replaced by the database partition number when the container is created. This definition is required if the user wants to specify containers in a multiple logical partition database.
The AUTOCONFIGURE parameter requires SYSADM authority.

Command parameters

DATABASE database-name

A name to be assigned to the new database. It 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. Specifically, the name must not contain any space characters.

AT DBPARTITIONNUM

Specifies that the database is to be created only on the database partition that issues the command. You do not specify this parameter when you create a new database. You can use it to re-create a database partition that you dropped because it was damaged. After you use the CREATE DATABASE command with the AT DBPARTITIONNUM parameter, the database at this database partition is in the restore-pending state. You must immediately restore the database on this database partition server. This parameter is not intended for general use. For example, it can be used with RESTORE DATABASE command if the database partition at a database partition server was damaged and must be re-created. Improper use of this parameter can cause inconsistencies in the system, so it can only be used with caution.

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

AUTOMATIC STORAGE NO | YES

Specifies that automatic storage is being explicitly disabled or enabled for the database. The default value is YES. If the AUTOMATIC STORAGE clause is not specified, automatic storage is implicitly enabled by default.

NO: Automatic storage is not being enabled for the database. This parameter cannot be specified in a Db2 pureScale® environment.
YES: Automatic storage is being enabled for the database. The default storage group, IBMSTOGROUP, is created in the SYSSTOGROUPS catalog table. To modify a storage group, use the ALTER STOGROUP statement.

Important: This parameter is deprecated and might be removed in a future release. Once removed, AUTOMATIC STORAGE YES is the only option.

ON path or drive

The meaning of this parameter depends on the value of the AUTOMATIC STORAGE parameter.

If AUTOMATIC STORAGE NO is specified, automatic storage is turned off for the database. In this case, only one path can be included as part of the ON parameter, and it specifies the path on which to create the database. If a path is not specified, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter).
Otherwise, automatic storage is enabled for the database by default. In this case, multiple paths can be listed here, each separated by a comma. These paths are referred to as storage paths defined to the default storage group IBMSTOGROUP and are used to hold table space containers for automatic storage table spaces. For multi-partition databases, the same storage paths are used on all partitions.
The DBPATH ON parameter specifies on which paths to create the database. If the DBPATH ON parameter is not specified, the database is created on the first path listed in the ON parameter. If no paths are specified with the ON parameter, 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 that is associated with the default storage group. Do not include the instance name, database partition number, or log stream ID on the specified path. Db2 will add these automatically to the path that you give. For example, if the path you give is "/home/dbuser", the final path after Db2 adds the necessary subdirectories will be "/home/dbuser/prod/NODE0000/LOGSTREAM0000/".
The database path is the location where a hierarchical directory structure is created. The structure holds the following files that are needed for the operation of the database:
- Buffer pool information
- Table space information
- Storage path information
- Database configuration information
- History file with information about backups, restores, loading of tables, reorganization of tables, altering of table spaces, and other database changes
- Log control files with information about active logs
The DBPATH ON parameter is used to place these files and information in a directory that is separate from the storage paths where the database data is kept. It is suggested that the DBPATH ON parameter is used when automatic storage is enabled to keep the database information separate from the database data.

The maximum length of a path is 175 characters.

For a partitioned database environment, a database cannot 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 operating systems, it cannot specify the $HOME directory of the instance owner). The path that is specified for this command in a partitioned database environment cannot be a relative path. Also, all paths that are specified as part of the ON parameter must exist on all database partitions.

A database path or storage path must exist and be accessible on each database partition.

DBPATH ON path or drive

If automatic storage is enabled, the DBPATH ON parameter specifies the path on which to create the database. If automatic storage is enabled and the DBPATH ON parameter is not specified, the database is created on the first path that is listed with the ON parameter.

The maximum length of a database path is 215 characters and the maximum length of a storage path is 175 characters.

Do not include the instance name, database partition number, or log stream ID on the specified path. Db2 will add these automatically to the path that you give. For example, if the path you give is "/home/dbuser", the final path after Db2 adds the necessary subdirectories will be "/home/dbuser/prod/NODE0000/LOGSTREAM0000/".

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. After you create the database, you cannot change the specified code set.

TERRITORY territory

Specifies the territory identifier or locale identifier to be used for data entered into this database. After you create the database, you cannot change the specified territory. The combination of the code set and territory or locale values must be valid.

COLLATE USING

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

In a Unicode database, the catalog tables and views are always created with the IDENTITY collation, regardless of the collation specified in the COLLATE USING clause. In non-Unicode databases, the catalog tables and views are created with the database collation.

COMPATIBILITY: The Db2 Version 2 collating sequence. Some collation tables are enhanced. This parameter specifies that the previous version of these tables is to be used.
IDENTITY: Identity collating sequence, in which strings are compared byte for byte.
IDENTITY_16BIT: CESU-8 (Compatibility Encoding Scheme for UTF-16: 8-Bit) collation sequence as specified by the Unicode Technical Report #26, which is available at the Unicode Consortium website (www.unicode.org). This parameter can only be specified when a Unicode database is created.
language-aware-collation: This parameter can only be used for Unicode databases. The database collating sequence is based on the SYSTEM collation for a non-Unicode database. This string must be of the format SYSTEM_codepage_territory. If the string supplied is invalid, the create database fails (SQLCODE -204; object not found). For more information, see Language-aware collations for Unicode data.
Note: When the CREATE DATABASE command is performed against a Version 9.0 server, this parameter cannot be used. By default, a Unicode database on such a server is created with SYSTEM collation.
locale-sensitive-collation: This parameter can only be used for Unicode databases. For more information, see Unicode Collation Algorithm based collations for more information and for the naming of locale-sensitive UCA-based collations. If the collation name provided is invalid, the CREATE DATABASE command execution fails (SQLCODE -204).
NLSCHAR: Built-in collating sequence that uses the unique collation rules for the specific code set or territory.
This parameter can only be used with the Thai code page (CP874). If this parameter is specified in non-Thai environments, the command fails and returns the error SQL1083N with Reason Code 4.
SYSTEM: This parameter is the default when a database is created. For non-Unicode databases, the collating sequence is based on the database territory. For Unicode databases, this parameter maps to a language-aware collation, based on the client code set and territory. If an appropriate language-aware collation is turned off, then the IDENTITY collation is used.

PAGESIZE integer

Specifies the page size of the default buffer pool along with the initial table spaces (SYSCATSPACE, TEMPSPACE1, USERSPACE1) when the database is created. This parameter also represents the default page size for all future CREATE BUFFERPOOL and CREATE TABLESPACE statements. The valid values for integer without the suffix K are 4096, 8192, 16384, or 32768. The valid values for integer with the suffix K are 4, 8, 16, or 32. At least one space is required between the integer and the suffix K. The default is a page size of 4096 bytes (4 K).

A 4- or 8-KB page size is generally suitable for an online transaction processing (OLTP) environment, and a 16- or 32-KB page size is appropriate for analytics. A 32-KB page size is recommended for column-organized tables.

NUMSEGS numsegs

Specifies the number of directories (table space containers) that are created and used to store the database table files for any default SMS table spaces. This parameter does not affect automatic storage table spaces, DMS table spaces, any SMS table spaces with explicit creation characteristics (created when the database is created), or any SMS table spaces that are explicitly created after the database is created.

DFT_EXTENT_SZ dft_extentsize

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

RESTRICTIVE

If the RESTRICTIVE parameter is present, it causes the restrict_access database configuration parameter to be set to YES and no privileges or authorities are automatically granted to PUBLIC. If the RESTRICTIVE parameter is not present, then the restrict_access database configuration parameter is set to NO and privileges are automatically granted to PUBLIC. For more information about privileges, see: Default privileges granted on creating a database.

ENCRYPT

Specifies that the database is to be encrypted. Encryption includes all system, user, and temporary table spaces, indexes, and all transaction log data. All data types within those table spaces are encrypted, including long field data, LOBs, and XML data.

CIPHER

Specifies the encryption algorithm that is to be used for encrypting the database. You can choose one of the following FIPS 140-2 approved options:

AES: Advanced Encryption Standard (AES) algorithm. This algorithm is the default.
3DES: Triple Data Encryption Standard (3DES) algorithm.

MODE CBC: Specifies the encryption algorithm mode that is to be used for encrypting the database. CBC (Cipher Block Chaining) is the default mode.

KEY LENGTH key-length

Specifies the length of the key that is to be used for encrypting the database. The length can be one of the following values, which are specified in bits:

128: Available with AES only.
168: Available with 3DES only.
192: Available with AES only.
256: Available with AES only. This key length is the default.

MASTER KEY LABEL

Specifies a label for the master key that is used to protect the key that is used to encrypt the database. The encryption algorithm that is used for encrypting with the master key is always AES. If the master key is automatically generated by the Db2 data server, it is always a 256-bit key.

label-name: Uniquely identifies the master key within the keystore that is identified by the value of the keystore_location database manager configuration parameter. The maximum length of label-name is 255 bytes.
If a master key label is not specified, the database manager automatically generates a master key label, and a master key is generated and inserted into the keystore.

Note:
When the allow_key_insert_without_ keystore_backup configuration knob is off, you cannot use the automatically generated master key.

CATALOG TABLESPACE tblspace-defn

Specifies the definition of the table space that holds the catalog tables, SYSCATSPACE. If not specified and automatic storage is not enabled for the database, SYSCATSPACE is 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

If not specified and automatic storage is enabled for the database, SYSCATSPACE is created as an automatic storage table space with its containers that are created on the defined storage paths. The extent size of this table space is 4. Appropriate values for AUTORESIZE, INITIALSIZE, INCREASESIZE, and MAXSIZE are set automatically.

For more information about the table space definition fields, see CREATE TABLESPACE statement.

In a partitioned database environment, the catalog table space is only created on the catalog database partition, the database partition 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 and automatic storage is not enabled for the database, USERSPACE1 is 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/SQLT0002.1
/u/smith/smith/NODE0000/SQL00001/SQLT0002.2
/u/smith/smith/NODE0000/SQL00001/SQLT0002.3
/u/smith/smith/NODE0000/SQL00001/SQLT0002.4

If not specified and automatic storage is enabled for the database, USERSPACE1 is created as an automatic storage table space with its containers that are created on the defined storage paths. The extent size of this table space is DFT_EXTENTSIZE. Appropriate values for AUTORESIZE, INITIALSIZE, INCREASESIZE, and MAXSIZE are set automatically.

For more information about the table space definition fields, see CREATE TABLESPACE statement.

TEMPORARY TABLESPACE tblspace-defn

Specifies the definition of the initial system temporary table space, TEMPSPACE1. If not specified and automatic storage is not enabled for the database, TEMPSPACE1 is 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/SQLT0001.1
/u/smith/smith/NODE0000/SQL00001/SQLT0001.2
/u/smith/smith/NODE0000/SQL00001/SQLT0001.3
/u/smith/smith/NODE0000/SQL00001/SQLT0001.4

If not specified and automatic storage is enabled for the database, TEMPSPACE1 is created as an automatic storage table space with its containers that are created on the defined storage paths. The extent size of this table space is DFT_EXTENTSIZE.

For more information about the table space definition fields, see CREATE TABLESPACE statement.

tblspace-defn

Various table space definitions can be specified through the following command parameters. In a Db2 pureScale environment, only MANAGED BY AUTOMATIC STORAGE can be used.

MANAGED BY

SYSTEM USING container-string

Specifies that the table space is to be an SMS table space. When the type of table space is not specified, the default behavior is to create a regular table space.

Important: For USER TABLESPACE specification, MANAGED BY SYSTEM is deprecated and might be removed in a future release. Use MANAGED BY AUTOMATIC STORAGE instead.

For an SMS table space, identifies one or more containers that belong to the table space and in which the table space data is stored. The container-string cannot exceed 240 bytes.

Each container-string can be an absolute or relative directory name.

The directory name, if not absolute, is relative to the database directory, and can be a path name alias (a symbolic link on UNIX operating systems) to storage that is not physically associated with the database directory. For example, dbdir/work/c1 might be a symbolic link to a separate file system.

If any component of the directory name does not exist, it is created by the database manager. When a table space is dropped, all components that are created by the database manager are deleted. If the directory identified by container-string exists, it must not contain any files or subdirectories (SQLSTATE 428B2).

The format of container-string depends on the operating system. On Windows operating systems, an absolute directory path name begins with a drive letter and a colon (:); on UNIX operating systems, an absolute path name begins with a forward slash (/). A relative path name on any platform does not begin with an operating system-dependent character.

Remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently only supported when using Network Appliance Filers, IBM® iSCSI, IBM Network-Attached Storage, Network Appliance iSCSI, NEC iStorage S2100, S2200, or S4100, or NEC Storage NS Series with a Windows Db2 server.

Note: NEC Storage NS Series is only supported by the use of an uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended. An NFS-mounted file system on AIX® must be mounted in uninterruptible mode by using the -o nointr parameter.

DATABASE USING

Specifies that the table space is to be a DMS table space. When the type of table space is not specified, the default behavior is to create a large table space.

Important: For USER TABLESPACE specification, MANAGED BY DATABASE is deprecated and might be removed in a future release. Use MANAGED BY AUTOMATIC STORAGE instead.

For a DMS table space, identifies one or more containers that belong to the table space and in which the table space data is stored. The type of the container (either FILE or DEVICE) and its size (in PAGESIZE pages) are specified. A mixture of FILE and DEVICE containers can be specified. The container-string cannot exceed 254 bytes.

Remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently only supported when using Network Appliance Filers, IBM iSCSI, IBM Network-Attached Storage, Network Appliance iSCSI, NEC iStorage S2100, S2200, or S4100, or NEC Storage NS Series with a Windows Db2 server.

Note: NEC Storage NS Series is only supported by the use of an uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended.

All containers must be unique across all databases. A container can belong to only one table space. The size of the containers can differ; however, optimal performance is achieved when all containers are the same size. The exact format of container-string depends on the operating system.

FILE container-string number-of-pages: For a FILE container, container-string must be an absolute or relative file name. The file name, if not absolute, is relative to the database directory. If any component of the directory name does not exist, it is created by the database manager. If the file does not exist, it is created and initialized to the specified size by the database manager. When a table space is dropped, all components that are created by the database manager are deleted.

Note: If the file exists, it is overwritten, and if it is smaller than specified, it is extended. The file is not truncated if it is larger than specified.
DEVICE container-string number-of-pages: For a DEVICE container, container-string must be a device name and it must exist.

AUTOMATIC STORAGE

Specifies that the table space is to be an automatic storage table space. If no storage groups are defined, an error is returned (SQLSTATE 55060).

An automatic storage table space is created as a system-managed space (SMS) table space if it is a temporary table space and as a database-managed space (DMS) table space if it is a permanent table space. If the type of DMS table space is not specified, the default behavior is to create a large table space. With an automatic storage table space, the database manager determines which containers are to be assigned to the table space, based on the storage paths that are associated with the database.

EXTENTSIZE number-of-pages

Specifies the number of PAGESIZE pages that will be written to a container before it skips to the next container. The extent size value can also be specified as an integer value followed by K (for kilobytes) or M (for megabytes). If specified in this way, the floor of the number of bytes divided by the page size is used to determine the value for the extent size. The database manager cycles repeatedly through the containers as data is stored.

The default value is provided by the dft_extent_sz database configuration parameter, which has a valid range of 2-256 pages.

PREFETCHSIZE number-of-pages

Specifies the number of PAGESIZE pages that are read from the table space when data prefetching is being performed. The prefetch size value can also be specified as an integer value followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). If specified in this way, the floor of the number of bytes divided by the page size is used to determine the number of pages value for prefetch size.

OVERHEAD number-of-milliseconds

Number that specifies the I/O controller usage, disk seek, and latency time in milliseconds. This value is used to determine the cost of I/O during query optimization. The value of number-of-milliseconds is any numeric literal (integer, decimal, or floating point). If this value is not the same for all containers, the number is the average for all containers that belong to the table space.

For a database that was created in Version 9 or later, the default I/O controller usage and disk seek and latency time is 7.5 milliseconds. For a database that was upgraded from a previous version of Db2 to Version 9 or later, the default is 12.67 milliseconds.

TRANSFERRATE number-of-milliseconds

Specifies the time to read one page into memory. This value is used to determine the cost of I/O during query optimization. The value of number-of-milliseconds is any numeric literal (integer, decimal, or floating point). If this value is not the same for all containers, the number is the average for all containers that belong to the table space.

For a database that was created in Version 9 or later, the default time to read one page into memory is 0.06 milliseconds. For a database that was upgraded from a previous version of Db2 to Version 9 or later, the default is 0.18 milliseconds.

NO FILE SYSTEM CACHING

Specifies that all I/O operations are to bypass the file system-level cache. See Table spaces without file system caching for more details. This parameter is the default on most configurations. See File system caching configurations for details.

FILE SYSTEM CACHING

Specifies that all I/O operations in the target table space are to be cached at the file system level. See Table spaces without file system caching for more details. This is the default parameter on some configurations. See File system caching configurations for details.

AUTORESIZE

Specifies whether the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled. Auto-resizable table spaces automatically increase in size when they become full. The default is NO for DMS table spaces and YES for automatic storage table spaces.

NO: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be turned off.
YES: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled.

INITIALSIZE integer

Specifies the initial size, per database partition, of an automatic storage table space. This parameter is only valid for automatic storage table spaces. The integer value must be followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). If the actual value that is used might be slightly smaller than what was specified, because the database manager strives to maintain a consistent size across containers in the table space. Moreover, if the table space is auto-resizable and the initial size is not large enough to contain metadata that must be added to the new table space, the database manager will continue to extend the table space by the value of INCREASESIZE until there is enough space. If the INITIALSIZE clause is not specified, the database manager determines an appropriate value. The value for integer must be at least 48 K.

K: K (for kilobytes).
M: M (for megabytes).
G: G (for gigabytes).

INCREASESIZE integer

Specifies the amount, per database partition, by which a table space that is enabled for auto-resize is automatically increased when the table space is full, and a request for space is made. The integer value must be followed by either:

PERCENT to specify the amount as a percentage of the table space size at the time that a request for space is made. When PERCENT is specified, the integer value must be between 0 and 100 (SQLSTATE 42615).
K (for kilobytes), M (for megabytes), or G (for gigabytes) to specify the amount in bytes.

Note: The actual value used might be slightly smaller or larger than what was specified, because the database manager strives to maintain consistent growth across containers in the table space. If the table space is auto-resizable, but the INCREASESIZE clause is not specified, the database manager determines an appropriate value.

PERCENT: Percent from 0 to 100.
K: K (for kilobytes).
M: M (for megabytes).
G: G (for gigabytes).

MAXSIZE

Specifies the maximum size to which a table space that is enabled for auto-resize can automatically be increased. If the table space is auto-resizable, but the MAXSIZE clause is not specified, the default is NONE.

NONE

Specifies that the table space is to be allowed to grow to file system capacity, or to the maximum table space size.

integer

Specifies a hard limit on the size, per database partition, to which a DMS table space or an automatic storage table space can automatically be increased. The integer value must be followed by K (for kilobytes), M (for megabytes), or G (for gigabytes).

Note: The actual value that is used might be slightly smaller than what was specified, because the database manager strives to maintain consistent growth across containers in the table space.

K: K (for kilobytes).
M: M (for megabytes).
G: G (for gigabytes).

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.

AUTOCONFIGURE

Based on user input, calculates the recommended settings for buffer pool size, database configuration, and database manager configuration and optionally applies them. The Configuration Advisor is run by default when the CREATE DATABASE command is issued. The AUTOCONFIGURE parameter is needed only if you want to tweak the recommendations.

USING input-keyword param-value

Table 1. Valid input keywords and parameter values
Keyword	Valid values	Default value	Explanation
mem_percent	When analytics is not enabled, the default value is 25 When analytics is enabled, mem_percent defaults to the higher of the following values: 25 80% divided by the number of database instances	25	Percentage of instance memory that is assigned to the database. However, if the CREATE DATABASE command invokes the configuration advisor and you do not specify a value for mem_percent, the percentage is calculated based on memory usage in the instance and the system up to a maximum of 25% of the instance memory.
workload_type	simple, mixed, complex	mixed	Simple workloads tend to be I/O intensive and mostly transactions, whereas complex workloads tend to be CPU intensive and mostly queries.
num_stmts	1–1 000 000	25	Number of statements per unit of work
tpm	1–200 000	60	Transactions per minute
admin_priority	performance, recovery, both	both	Optimize for better performance (more transactions per minute) or better recovery time
num_local_apps	0–5 000	0	Number of connected local applications
num_remote_apps	0–5 000	100	Number of connected remote applications
isolation	RR, RS, CS, UR	RR	Isolation level of applications connecting to this database (Repeatable Read, Read Stability, Cursor Stability, Uncommitted Read)
bp_resizeable	yes, no	yes	Are buffer pools resizable?

APPLY

DB ONLY: Displays the recommended values for the database configuration and the buffer pool settings based on the current database manager configuration. Applies the recommended changes to the database configuration and the buffer pool settings.
DB AND DBM: Displays and applies the recommended changes to the database manager configuration, the database configuration, and the buffer pool settings.
NONE: Disables the Configuration Advisor (it is enabled by default).

If the AUTOCONFIGURE keyword is specified with the CREATE DATABASE command, the DB2_ENABLE_AUTOCONFIG_DEFAULT variable value is not considered. Adaptive Self-Tuning Memory and Auto Runstats will be enabled and the Configuration Advisor tunes the database configuration and database manager configuration parameters as indicated by the APPLY DB or APPLY DBM parameters.
Specifying the AUTOCONFIGURE parameter with the CREATE DATABASE command on a database recommends enablement of the Self-Tuning Memory Manager. However, if you run the AUTOCONFIGURE command on a database in an instance where sheapthres is not zero, sort memory tuning (sortheap) will not be enabled automatically. To enable sort memory tuning (sortheap), you must set sheapthres equal to zero by using the UPDATE DATABASE MANAGER CONFIGURATION command.
Note: Changing the value of sheapthres can affect the sort memory usage in your previously existing databases.

REPORT FOR

Specifies the members to include in the report when used in a partitioned database environment or Db2 pureScale environment.

MEMBER -1: In a partitioned database environment or Db2 pureScale environment, the Configuration Advisor reports the member level configuration parameters computed changes that are recommended, or made on the current member that is determined by your connection. This is the default if the REPORT FOR clause is not specified.

MEMBER -2: Indicates that the Configuration Advisor is to report the computed recommendations or changes for all members.

FOR MEMBER member-number1: Specifies the number of the member the Configuration Advisor is to report the computed recommendations or changes for.

FOR MEMBERS (member-number2…): Specifies the numbers for each of the members the Configuration Advisor is to report the computed recommendations or changes for.

INCLUDE SUMMARY: Indicates that the Configuration Advisor includes a summary of all the member groupings based on the computed recommendations as part of the report. This option is the default.

EXCLUDE SUMMARY: Indicates that the Configuration Advisor does not include a summary as part of the report.

Examples

Create database TESTDB3 on the drive that is specified by the value of the dftdbpath database manager configuration parameter. By default, the storage group IBMSTOGROUP is created with the path dftdbpath.
```
CREATE DATABASE TESTDB3
```
Create database TESTDB7 on drive C: (the first drive in the storage path list). The storage group IBMSTOGROUP has storage paths C: and D:.
```
CREATE DATABASE TESTDB7 ON C:,D:
```
Create database TESTDB15 on drive E: (explicitly listed as DBPATH). The storage group IBMSTOGROUP has storage paths C: and D:.
```
CREATE DATABASE TESTDB15
  ON C:,D: DBPATH ON E:
```
Encrypt database MYDB by using the default encryption options.
```
CREATE DATABASE mydb ENCRYPT;
```
Encrypt database MYDB by using explicitly provided encryption options. The label mylabel.mydb.myinstance.myserver exists in the keystore.
```
CREATE DATABASE mydb
  ENCRYPT CIPHER AES KEY LENGTH 192
  MASTER KEY LABEL mylabel.mydb.myinstance.myserver;
```

Usage notes

The CREATE DATABASE command:

Creates a database in the specified subdirectory. In a partitioned database environment, creates the database on all database partitions listed in db2nodes.cfg, and creates a $DB2INSTANCE/NODExxxx directory under the specified subdirectory at each database partition. In a single partition database environment, 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 by the dftdbpath parameter. 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 contains 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.

Note: If the database configuration parameter newlogpath is not set, then, the default for the location of log files configuration parameter logpath is the path that is shown by the DBPATH ON parameter. It is suggested that the DBPATH ON parameter is used when automatic storage is enabled to keep the database information separate from the database data.
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 schemas called SYSCAT, SYSFUN, SYSIBM, and SYSSTAT with SYSIBM as the owner. The database partition server on which this command is issued becomes the catalog database partition for the new database. Two database partition groups are created automatically: IBMDEFAULTGROUP and IBMCATGROUP.
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 that is called NULLID is implicitly created when performing the binds with the CREATEIN privilege granted to PUBLIC, if the RESTRICTIVE parameter is not selected.
The utilities bind file list contains two bind files that cannot be bound against previous version of the server:
- 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 server that is not at the latest level, warnings about 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 database partition.
For information about the privileges granted when creating a database, see: Default privileges granted on creating a database.
The size of the partitioning map is selected by the database manager. The size cannot change once selected. By default, the partitioning map has 32, 768 entries. This size can reduce data skew for clusters with many database partitions. However, the following deprecated APIs do not work and return a SQL2768N error: sqlugtpi() and sqlugrpn(). In its place, you can use db2GetDistMap() and getRowPartNum(). If the deprecated APIs are required set the DB2_PMAP_COMPATIBILITY registry variable before database creation. This registry variable picks a partitioning map size of 4,096, which allows the deprecated APIs to work.

Automatic storage is a collection of storage paths that are associated with a storage group on which table spaces can be created without having to explicitly specify container definitions (see CREATE TABLESPACE statement for more information). Automatic storage is enabled by default, but can be explicitly run for a database when it is created. Automatic storage can be turned off at database creation time by specifying the AUTOMATIC STORAGE NO parameter.

When free space is calculated for an automatic storage path for a specific database partition, the database manager checks for the existence of the following directories or mount points within the storage path and uses the first one that is found. In doing so, file systems can be mounted at a point beneath the storage path and the database manager will recognize that the actual amount of free space available for table space containers cannot be the same amount that is associated with the storage path directory itself.

storage_path/instance_name/NODE####/database_name
storage_path/instance_name/NODE####
storage_path/instance_name
storage_path/

Where

storage_path is a storage path associated with the database.
instance_name is the instance under which the database resides.
NODE#### corresponds to the database partition number (for example NODE0000 or NODE0001).
database_name is the name of the database.

Consider the example where two logical database partitions exist on one physical machine and the database is being created with a single storage path: /db2data. Each database partition will use this storage path but the user might want to isolate the data from each partition within its own file system. In this case, a separate file system can be created for each partition and be mounted at /db2data/instance/NODE####. When containers are created on the storage path and determining free space, the database manager knows not to retrieve free space information for /db2data, but instead retrieve it for the corresponding /db2data/instance/NODE#### directory.

In general, the same storage paths must be used for each partition in a multi-partition database and they must all exist before running the CREATE DATABASE command. One exception is where database partition expressions are used within the storage path. Doing so allows the database partition number to be reflected in the storage path such that the resulting path name is different on each partition.

In a partitioned database environment, the database manager creates a subdirectory, $DB2INSTANCE/NODExxxx, under the specified or default path on all database partitions. The xxxx is the database partition number as defined in the db2nodes.cfg file (that is, database partition 0 becomes NODE0000). Sub-directories SQL00001 through SQLnnnnn resides on the path. The different sub-directory paths ensures that the database objects that are associated with different database partitions are stored in different directories (even if the subdirectory $DB2INSTANCE under the specified or default path is shared by all database partitions).

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

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

When a database is created, a detailed deadlocks event monitor is created. As with any monitor, there is more processing usage that is associated with this event monitor. You can drop the deadlocks event monitor by issuing the DROP EVENT MONITOR command.

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

The combination of the code set and territory values must be valid. For a list of the supported combinations, see Supported territory codes and code pages.

To specify a database path (instead of a drive) on a Windows operating system, you need to set the Db2 registry variable: DB2_CREATE_DB_ON_PATH=YES.

Use the COLLATE USING clause with a language-aware-collation or locale-sensitive-collation instead of UCA400_NO, UCA400_LSK, or UCA400_LTH.

Important: Collations based on the Unicode Collation Algorithm of the Unicode Standard version 4.0.0 have been deprecated and might be removed in a future release. For more information, see Collations based on the Unicode Collation Algorithm of the Unicode Standard version 4.0.0 have been deprecated.