CREATE DATABASE command

When the CREATE DATABASE command is issued, the Configuration Advisor also runs automatically. This 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 Memory by default, refer to the self_tuning_mem database configuration parameter . For multi-partition databases, Adaptive Self Tuning Memory is disabled by default.

If no code set is specified on the CREATE DATABASE command, then the collations 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 should 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

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-+----------------------------------------->
           '-DB-------'   

>----database-name--+-----------------------------+------------><
                    +-AT DBPARTITIONNUM-----------+     
                    '-| Create Database options |-'     

Create Database options

   .-AUTOMATIC STORAGE--YES-.   
|--+------------------------+----------------------------------->
   '-AUTOMATIC STORAGE--NO--'   

>--+---------------------------------------------+-------------->
   |     .-,---------.                           |   
   |     V           |                           |   
   '-ON----+-path--+-+--+----------------------+-'   
           '-drive-'    '-DBPATH ON--+-path--+-'     
                                     '-drive-'       

>--+-----------------------+------------------------------------>
   '-ALIAS--database-alias-'   

>--+----------------------------------------------+------------->
   '-USING CODESET--codeset--TERRITORY--territory-'   

>--+-----------------------------------------------+------------>
   |                .-SYSTEM---------------------. |   
   '-COLLATE USING--+-COMPATIBILITY--------------+-'   
                    +-IDENTITY-------------------+     
                    +-IDENTITY_16BIT-------------+     
                    +-language-aware-collation---+     
                    +-locale-sensitive-collation-+     
                    '-NLSCHAR--------------------'     

   .-PAGESIZE--4096-----------.                         
>--+--------------------------+--+------------------+----------->
   '-PAGESIZE--integer--+---+-'  '-NUMSEGS--numsegs-'   
                        '-K-'                           

>--+-------------------------------+--+-------------+----------->
   '-DFT_EXTENT_SZ--dft_extentsize-'  '-RESTRICTIVE-'   

>--+-----------------------------------------------------------------+-->
   '-ENCRYPT--+------------------------+--+------------------------+-'   
              '-| Encryption Options |-'  '-| Master Key Options |-'     

>--+---------------------------------------+-------------------->
   '-CATALOG TABLESPACE--| tblspace-defn |-'   

>--+------------------------------------+----------------------->
   '-USER TABLESPACE--| tblspace-defn |-'   

>--+-----------------------------------------+------------------>
   '-TEMPORARY TABLESPACE--| tblspace-defn |-'   

>--+------------------------+----------------------------------->
   '-WITH--"comment-string"-'   

>--+---------------------------------------------------------------------------------+--|
   |                                                                  .-DB ONLY----. |   
   '-AUTOCONFIGURE--+---------------------------------------+--APPLY--+-DB AND DBM-+-'   
                    |        .----------------------------. |         '-NONE-------'     
                    |        V                            | |                            
                    '-USING----input-keyword--param-value-+-'                            

tblspace-defn

|--MANAGED BY--------------------------------------------------->

                      .-,----------------------.                                       
                      V                        |                                       
>--+-SYSTEM USING--(----'--container-string--'-+--)--------------------------------+-->
   |                    .-,---------------------------------------------------.    |   
   |                    V                                                     |    |   
   +-DATABASE USING--(----+-FILE---+--'--container-string--'--number-of-pages-+--)-+   
   |                      '-DEVICE-'                                               |   
   '-AUTOMATIC STORAGE-------------------------------------------------------------'   

>--+-----------------------------+------------------------------>
   '-EXTENTSIZE--number-of-pages-'   

>--+-------------------------------+---------------------------->
   '-PREFETCHSIZE--number-of-pages-'   

>--+----------------------------------+------------------------->
   '-OVERHEAD--number-of-milliseconds-'   

>--+--------------------------------------+--------------------->
   '-TRANSFERRATE--number-of-milliseconds-'   

   .-NO FILE SYSTEM CACHING-.                            
>--+------------------------+--+---------------------+---------->
   '-FILE SYSTEM CACHING----'  '-AUTORESIZE--+-NO--+-'   
                                             '-YES-'     

>--+-----------------------------+------------------------------>
   '-INITIALSIZE--integer--+-K-+-'   
                           +-M-+     
                           '-G-'     

>--+------------------------------------+----------------------->
   '-INCREASESIZE--integer--+-PERCENT-+-'   
                            '-+-K-+---'     
                              +-M-+         
                              '-G-'         

>--+-----------------------------+------------------------------|
   '-MAXSIZE--+-NONE-----------+-'   
              '-integer--+-K-+-'     
                         +-M-+       
                         '-G-'       

Encryption Options

                     .-MODE--CBC-.                           
|--CIPHER--+-AES--+--+-----------+--KEY LENGTH--key-length------|
           '-3DES-'                                          

Master Key Options

|--MASTER KEY LABEL--label-name---------------------------------|

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. 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 should 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 should 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 will be in the restore-pending state. After recreating 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 has been deprecated and might be removed in a future release. Once removed, AUTOMATIC STORAGE YES will be 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 disabled 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 may be listed here, each separated by a comma. These 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 will be 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 associated with the default storage group.

  The database path is the location where a hierarchical directory structure is created. The structure holds the following files needed for the operation of the database:

  • Buffer pool information
  • Table space information
  • Storage path information
  • Database configuration information
  • History file information regarding 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 can be 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 be 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 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 operating systems, it should not specify the $HOME directory of the instance owner). The path specified for this command in a partitioned database environment cannot be a relative path. Also, all paths specified as part of the ON parameter must exist on all database partitions.

A given 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 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.

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 has been 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 have been 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 creating a Unicode database.

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 will fail (SQLCODE -204; object not found). See "Language-aware collations for Unicode data" for more information and for the naming of system based collations.

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 will be created with SYSTEM collation.

locale-sensitive-collation

This parameter can only be used for Unicode databases. 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 will fail (SQLCODE -204).

NLSCHAR

Built-in collating sequence using the unique collation rules for the specific code set/territory.

This parameter can only be used with the Thai code page (CP874). If this parameter is specified in non-Thai environments, the command will fail and return the error SQL1083N with Reason Code 4.

SYSTEM

This is the default parameter when creating a database. 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 unavailable, 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 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 4 096, 8 192, 16 384, or 32 768. 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 4 096 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 will be 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 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 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 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, specified in bits:

128

Available with AES only

168

Available with 3DES only

192

Available with AES only

256

Available with AES only. This 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_type 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 and inserts it into the keystore file.

CATALOG TABLESPACE tblspace-defn

Specifies the definition of the table space that will hold 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 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.

See "CREATE TABLESPACE statement" for more information aboutthe table space definition fields.

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 created on the defined storage paths. The extent size of this table space will be DFT_EXTENTSIZE. Appropriate values for AUTORESIZE, INITIALSIZE, INCREASESIZE, and MAXSIZE are set automatically.

See "CREATE TABLESPACE statement" for more information aboutthe table space definition fields.

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 created on the defined storage paths. The extent size of this table space is DFT_EXTENTSIZE.

See "CREATE TABLESPACE statement" for more information aboutthe table space definition fields.

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 will belong to the table space and in which the table space data will be stored. The container-string cannot exceed 240 bytes in length.

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 could 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 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 is dependent 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 that NEC Storage NS Series is only supported with 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 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 will belong to the table space and in which the table space data will be 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 in length.

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 that NEC Storage NS Series is only supported with 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 is dependent 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 will be created and initialized to the specified size by the database manager. When a table space is dropped, all components 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 will not be truncated if it is larger than specified.

DEVICE container-string number-of-pages

For a DEVICE container, container-string must be a device name. The device must already exist.

AUTOMATIC STORAGE

Specifies that the table space is to be an automatic storage table space. If there are no storage groups 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 upon 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 skipping 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 will be 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 should be 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 should be 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 is the default parameter 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 or not 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 disabled.

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). Note that the actual value 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 meta-data 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 will automatically be increased when the table space is full, and a request for space has been 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 that 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 that the actual value 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	1–100	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 will tune 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 will recommend 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 using the UPDATE DATABASE MANAGER CONFIGURATION command. Note that changing the value of sheapthres may affect the sort memory usage in your previously existing databases.

Usage notes

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

When free space is calculated for an automatic storage path for a given database partition, the database manager will check for the existence of the following directories or mount points within the storage path and will use the first one that is found. In doing this, 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 may not be the same amount that is associated with the storage path directory itself.

1. storage_path/instance_name/NODE####/database_name
2. storage_path/instance_name/NODE####
3. storage_path/instance_name
4. 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 may 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 creating containers on the storage path and determining free space, the database manager will know 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 executing the CREATE DATABASE command. One exception to this is where database partition expressions are used within the storage path. Doing this 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). Subdirectories SQL00001 through SQLnnnnn will reside on this path. This ensures that the database objects 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 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 the CATALOG LDAP DATABASE command.

CREATE DATABASE will fail 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 some additional processing usage 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.