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, UCA400_NO, UCA400_LSK, UCA400_LTH, language-aware-collation, and locale-aware-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 desired 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-------------+     
                    +-UCA400_NO------------------+     
                    +-UCA400_LSK-----------------+     
                    +-UCA400_LTH-----------------+     
                    +-language-aware-collation---+     
                    +-locale-sensitive-collation-+     
                    '-NLSCHAR--------------------'     

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

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

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

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 option when you create a new database. You can use it to recreate a database partition that you dropped because it was damaged. After you use the CREATE DATABASE command with the AT DBPARTITIONNUM option, 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 recreated. 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 recreate 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.

YES

Automatic storage is being enabled for the database.

ON path or drive

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

• 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 option, 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). This behavior matches that of DB2 Universal Database™ Version 8.2 and earlier.
• 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 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 option specifies on which paths to create the database. If the DBPATH ON option is not specified, the database is created on the first path listed in the ON option. If no paths are specified with the ON option, the database is created on the default database path that is specified in the database manager configuration file (dftdbpath parameter). This will also be used as the location for the single storage path associated with the database.

  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 option 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 option 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 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 operating 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. Also, all paths specified as part of the ON option 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 option specifies the path on which to create the database. If automatic storage is enabled and the DBPATH ON option is not specified, the database is created on the first path listed with the ON option.

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

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 Web site (www.unicode.org). This option can only be specified when creating a Unicode database.

UCA400_NO

The UCA (Unicode Collation Algorithm) collation sequence that is based on the Unicode Standard version 4.0.0 with normalization implicitly set to ON. Details of the UCA can be found in the Unicode Technical Standard #10, which is available at the Unicode Consortium Web site (www.unicode.org). This option can only be used when creating a Unicode database.

UCA400_LSK

The UCA (Unicode Collation Algorithm) collation sequence based on the Unicode Standard version 4.0.0 but will sort Slovak characters in the appropriate order. Details of the UCA can be found in the Unicode Technical Standard #10, which is available at the Unicode Consortium Web site (www.unicode.org). This option can only be used when creating a Unicode database.

UCA400_LTH

The UCA (Unicode Collation Algorithm) collation sequence that is based on the Unicode Standard version 4.0.0 but will sort all Thai characters according to the Royal Thai Dictionary order. Details of the UCA can be found in the Unicode Technical Standard #10 available at the Unicode Consortium Web site (www.unicode.org). This option can only be used when creating a Unicode database. This collator might order Thai data differently from the NLSCHAR collator option.

language-aware-collation

This option 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 option cannot be used. By default, a Unicode database on such a server will be created with SYSTEM collation.

locale-sensitive-collation

This option 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

System-defined collating sequence using the unique collation rules for the specific code set/territory.

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

SYSTEM

This is the default option when creating a database. For non-Unicode databases, the collating sequence is based on the database territory. For Unicode databases, this option 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).

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 option is present it causes the restrict_access database configuration parameter to be set to YES and limited privileges and no authorities are automatically granted to PUBLIC. If the RESTRICTIVE option 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".

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 on the 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 on the 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 on the table space definition fields.

tblspace-defn

Various table space definitions can be specified through the following command parameters.

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.

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 option.

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.

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 automatic storage is not defined for the database, an error is returned (SQLSTATE 55060).

An automatic storage table space is created as either a system managed space (SMS) table space or a database managed space (DMS) table space. When DMS is chosen and the type of 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

Specifies the I/O controller overhead and disk seek and latency time. 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 overhead 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 option 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 option 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 option 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 option 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 resizeable?

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 options.
• Specifying the AUTOCONFIGURE option 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.

Examples

Here are several examples of the CREATE DATABASE command:

Example 1:

CREATE DATABASE TESTDB3
  AUTOMATIC STORAGE YES

Database TESTDB3 is created on the drive that is the value of database manager configuration parameter dftdbpath. Automatic storage is enabled with a single storage path that also has the value of dftdbpath.

Example 2:

CREATE DATABASE TESTDB7 ON C:,D:

Database TESTDB7 is created on drive C: (first drive in storage path list). Automatic storage is implicitly enabled and the storage paths are C: and D:.

Example 3:

CREATE DATABASE TESTDB15
  AUTOMATIC STORAGE YES
  ON C:,D: DBPATH ON E:

Database TESTDB15 is created on drive E: (explicitly listed as DBPATH). Automatic storage is explicitly enabled and the storage paths are C: and D:.

Usage notes

Automatic storage is a collection of storage paths associated with a database 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 option.

It is important to note that automatic storage can only be enabled at database creation time, it cannot be enabled after the database has been created. Also, automatic storage cannot be disabled once a database has been defined to use it.

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 prior to 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 an MPP 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 overhead 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.

Compatibilities