CREATE DATABASE 命令详解

CREATE DATABASE 命令详解
字数 52681阅读 7792评论 0赞 0
CREATE DATABASE 命令详解
Initializes a new database with an optional user-defined collating sequence, creates the three initial table spaces, creates the system tables, and allocates the recovery log file. When you initialize a new database, the AUTOCONFIGURE command is issued by default.
初始化一个新数据库：使用一个可选的、用户定义的整理顺序；创建三个初始表空间；创建系统表，并且收集恢复日志文件。当初始化一个新数据库时，默认执行AUTOCONFIGURE命令

Note: When the instance and database directories are created by the DB2® database manager, the permissions are accurate and should not be changed.
NOTE：由DB2数据库管理器创建的实例和数据库目录，权限确定的，不宜更改。

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 theDB2_ENABLE_AUTOCONFIG_DEFAULT registry variable. To disable Automated Runstats, refer to the auto_runstats database configuration parameter.
当执行 CREATE DATABASE 命令时，配置顾问程序也自动运行。这意味着数据库会根据您的系统资源自动配置数据库配置参数。而且，自动启用RUNSTATS。要从创建的数据库中禁用正在运行的 Configuration Advisor ，则涉及到注册变量 DB2_ENABLE_AUTOCONFIG_DEFAULT。要禁用 Automated Runstats，则涉及到数据库配置参数 auto_runstats。

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.
对于单分区数据库，默认情况下自适应自调优内存也是启用的。要想默认禁用 Adaptive Self Tuning Memory ，使用数据库配置参数 self_tuning_mem 。对于多分区数据库，默认情况下 Adaptive Self Tuning Memory 是禁用的。

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.
如果 CREATE DATABASE 命令中指定CODE SET，则允许的选项：SYSTEM, IDENTITY_16BIT,UCA400_NO, UCA400_LSK, UCA400_LTH, language-aware-collation, and locale-aware-collation (SQLCODE -1083).
默认的CODE SET是UTF-8.如果需要其他的code set 和 territory，则在 CREATE DATABASE 命令中指定需要的 code set 和 territory。

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.
在一个分区数据库环境下，这个命令会影响db2nodes.cfg文件中列出的所有分区数据库。

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:
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.
实例。 要想在另一个（远程）数据库分区服务器上创建数据库，你必须先连接上这个Server。此命令在执行期间建立数据库的临时连接。
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-'
Note:
The combination of the code set and territory values must be valid.code set和territory 的组合必须有效。
Not all collating sequences are valid with every code set and territory combination.列出整理的所有的code set 和 territory的组合并不是全都有效。
The table space definitions 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。 CREATE DATABASE指定的表空间适用于创建数据库的所有数据库分区。不能为每个数据库分区单独指定表空间。要在特定的数据库分区创建不同的表空间使用 CREATE TABLESPACE 命令。
When defining containers for table spaces, $N can be used. $N will be replaced by the database partition number when the container is actually created. This is required if the user wants to specify containers in a multiple logical partition database.为表空间定义container时可以使用$N。在创建container时$N会被数据库分区号取代。如果用户要在逻辑多分区数据库上指定container时使用这个。
The AUTOCONFIGURE option requires SYSADM authority.AUTOCONFIGURE选项需要SYSADM权限。
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.仅在指定的数据库分区创建数据库。创建数据库时不必指定这个选项。你可以用这个选项重新创建你dropp掉的损坏的数据库分区。在执行指定了AT DBPARTITIONNUM 选项 CREATE DATABASE 命令后，这个数据库分区上的数据库处于 restore-pending状态。你必须在这个分区服务器上立即restore数据库。此参数一般不使用。例如，当数据库分区服务器所在的数据库分区damaged且必须重建的时候，应该使用 RESTORE DATABASE 命令。使用不当会导致系统不一致，所以请谨慎使用。
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.
如果使用此参数重建dropp掉的数据库分区（已损坏了），则这个数据库分区上的数据库将处于restore-pending状态。在重建数据库分区后，应立即restore数据库。

AUTOMATIC STORAGE NO | YES
Specifies that automatic storage is being explicitly disabled or enabled for the database. The default value is YES. If theAUTOMATIC STORAGE clause is not specified, automatic storage is implicitly enabled by default.明确指定数据库的 automatic storage 是禁用还是启用。默认值是YES.如果没指定，取默认值YES。

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.这个选项的意思取决于 AUTOMATIC STORAGE 的值。
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.如果指定 AUTOMATIC STORAGE NO，则在ON选项内只能包括一个路径，在这个路径下创建数据库。如果没指定，则数据库在默认的数据库目录下创建(dftdbpath参数)。这个行为适用于DB2 UDB 8.2及之前版本。
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.若数据库启用automatic storage，则可以指定多个路径，用逗号隔开。这些路径称为存储路径，用以为自动存储表空间存放表空间容器。对于多分区数据库，在所有分区上使用相同的存储路径。

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.
DBPATH ON 选项用以指定数据库的创建路径。如果没有指定 DBPATH ON 则在 ON 选项的第一个路径上创建数据库。如果ON选项上没指定路径，则在数据库管理器配置参数(dftdbpath参数)指定的路径下创建数据库.这个路径也是数据库的单一存储路径。
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 BufferPool信息
Table space information Tablespace 信息
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.
可以使用 DBPATH ON 选项为这些文件指定一个与数据库数据存放路径不同的目录。我们建议，当启用automatic storage时，使用 DBPATH ON 选项来分开数据库信息和数据库数据。
The maximum length of a path is 175 characters.字符长度限制在175个字符

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.
对于MPP系统，数据库不能在NFS-mounted的路径下创建。如果没有指定路径，那么应确保DBM配置参数 dftdbpath 指定的不是NFS-mounted的路径（例如，在UNIX系统上，不应该指定$home这个实例所有者的路径）。在MPP系统中这个命令指定的路径不能是相对路径。并且，ON选项指定的所有路径在所有的数据库分区上必须存在。
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.如果启用了automatic storage，则可以使用 DBPATH ON 选项指定数据库创建路径。如果启用了Automatic Storage且DBPATN ON选项没有指定，则在ON选项的第一个路径下创建数据库。

The maximum length of a database path is 215 characters and the maximum length of a storage path is 175 characters.数据库路径长度限制在215个字符，存储路径限制在175个字符。

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.指定数据库的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.指定地域或区域标识。创建数据库后不能更改。code set h和territory的组合必须有效。
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.
Unicode数据库使用 IDENTITY collation创建catalog表和视图，忽略COLLATE USING 指定的类型。non-Unicode数据库使用database collation创建catalog 表和视图。

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.DB2 version 2的整理次序。一些整理表已经加强了。这个选项指定了之前的版本会使用。
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.CESU-8 （Compatibility Encoding Scheme for UTF-16: 8-Bit（www.unicode.org），只能在unicode数据库应用。
UCA400_NOThe 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.只能在unicode数据库应用。
UCA400_LSKThe 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.只能在unicode数据库应用。
UCA400_LTHThe 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-collationThis 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.
只能在unicode数据库应用。对于non-unicode数据库，是基于system cllation。字符串必须是SYSTEM_codepage_territory的格局，不然创建数据库会失败。
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.
当 CREATE DATABASE在V9上执行时，这个选项不会被应用。默认，unicode数据库在v9上会使SYSTEM collation。
locale-sensitive-collationThis 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 DATABASEcommand execution will fail (SQLCODE -204).只能在unicode数据库应用。NLSCHARSystem-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.
只能用在Thai code page（CP874） ，为指定的set/territory设置独特的collation。
SYSTEMThis 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.这是创建数据库时，collation的默认选项。对于non-unicode数据库，collation是基于territory。对于unicode数据库，按照客户的code set和territory，对应一个language-aware-collation，如何没有合适的language-aware可用，会应用IDENTITY。PAGESIZE integerSpecifies 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).指定初始化表空间 (SYSCATSPACE, TEMPSPACE1, USERSPACE1)和缓冲池的默认页大小。并且是以后使用 CREATE BUFFERPOOL 和 CREATE TABLESPACE创建的默认页大小。 NUMSEGS numsegsSpecifies 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.指定路径数量（表空间容器），这些路径将会被创建并用来存放默认SMS表空间的表文件。这个参数不会影响到自动存储表空间，DMS表空间和明确指定创建（创建数据库时创建）或者在创建数据库后明确创建的SMS表空间。DFT_EXTENT_SZ dft_extentsizeSpecifies the default extent size of table spaces in the database.默认表空间扩展数据块大小RESTRICTIVEIf the RESTRICTIVE option 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 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".如果指定RESTRICTIVE 选项，则会导致 restrict_access 数据库配置参数设成YES，没有任何特权和权限自动授予PUBLIC。如果没有选择 RESTRICTIVE 选项，则 restrict_access 为NO，且特权自动收于PUBLIC。CATALOG TABLESPACE tblspace-defnSpecifies 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:目录表空间 SYSCATSPACE。如果没有指定而且automatic storage没有启用，则创建SMS表空间，container数量为 NUMSEGS 中定义的目录数，扩展数据块大小为DFT_EXTENTSIZE的值。/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.
如果没有指定且启用automatic storage，则自动存储表空间SYSCATASPACE和它的容器在指定的存储路径中创建。Extentsize为4,自动分配合适的AUTORESIZE, INITIALSIZE, INCREASESIZE和MAXSIZE
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.
在分区数据库环境下，当执行 CREATE DATABASE 时，catalog 表空间仅在这个编目数据库分区上创建。
USER TABLESPACE tblspace-defnSpecifies 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:用户表空间 USERSPACE1。如果没有指定而且automatic storage没有启用，则创建SMS型表空间USERSPACE1，container数量为 NUMSEGS 中定义的目录数，扩展数据块大小为DFT_EXTENTSIZE的值。 /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.
如果没有指定且启用automatic storage，则自动存储表空间USERSPACE1和它的容器在指定的存储路径中创建。Extentsize为DFT_EXTENTSIZE,自动分配合适的AUTORESIZE, INITIALSIZE, INCREASESIZE和MAXSIZE
See "CREATE TABLESPACE statement" for more information on the table space definition fields.

TEMPORARY TABLESPACE tblspace-defnSpecifies 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:临时表空间TEMPSPACE1.如果没有指定而且automatic storage没有启用，则创建SMS表空间，container数量为 NUMSEGS 中定义的目录数，扩展数据块大小为DFT_EXTENTSIZE的值。 /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.
如果没有指定且启用automatic storage，则自动存储表空间TEMPSPACE1和它的容器在指定的存储路径中创建。Extentsize为DFT_EXTENTSIZE。
See "CREATE TABLESPACE statement" for more information on the table space definition fields.

tblspace-defnVarious table space definitions can be specified through the following command parameters.使用下面命令参数定义不同类型表空间。 MANAGED BYSYSTEM USING container-stringSpecifies 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.指定表空间为SMS表空间，不指定的话，默认为regular表空间。

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.
对于SMS 表空间，指定一个或者多个表空间容器，表空间数据将存储在这些容器里。container-string 不能超过240个字节。
Each container-string can be an absolute or relative directory name.每个container-string 是一个绝对或者相对路径名。

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.
目录名若不是绝对的，是相对的数据库目录，并可以是一个用于存储的路径别名（在UNIX操作系统中的一个符号链接），这种情况下的目录不是一个实际的数据库目录。例如，dbdir/work/c1 就是一个单独的文件系统的符号链接。

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).
如果目录有部分不存在则数据库管理器会创建它。当drop一个表空间时，有DBM创建的所有组件也会被删除。如果container-string 指定的路径存在，则此路径下不能有任何文件或子目录。

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.
container-string 的格式取决于操作系统。在Windows上，绝对路径名开始的是一个驱动器号和冒号；UNIX系统上。绝对路径名开始于一个左斜杠。相对路径名在任何平台上都不开始于一个基于操作系统的参数。

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 USINGSpecifies 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.DMS表空间。当指定这种表空间时默认创建一个大表空间。

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.
对于DMS 表空间，指定一个或者多个将属于这个表空间的容器，表空间数据将存储在这些容器里。指定容器类型（FILE or DEVICE ）和大小（PAGESIZE页数）。可以混合指定FILE和DEVICE容器。container-string 不能超出254个字节。
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.
所有容器在所有数据库上是唯一的。一个容器只能属于一个表空间。表空间大小可以不同，但是，当表空间大小相同时获得最佳性能。container-string具体格式取决于OS。

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.
对于FILE容器，container-string必须是一个绝对或者相对路径。文件名称，如果不是绝对的，则是相对于数据库目录而言。
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-pagesFor a DEVICE container, container-string must be a device name. The device must already exist.对于DEVICE容器，container-string 必须是一个驱动器名称。驱动器必须已经存在。AUTOMATIC STORAGESpecifies 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).自动存储表空间。如果数据库没有指定为automatic storage，则会返回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.
可以为SMS或DMS指定为自动存储表空间。当选定DMS且没有指定表空间类型时，默认创建一个大表空间。对于自动存储表空间，数据库管理器根据数据库存储路径为表空间指派container。
EXTENTSIZE number-of-pagesSpecifies 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.指定跳到下一容器前在当前容器内写入的PAGESIZE的页数。Extentsize可以指定为一个整数，后面跟着K，M。如果使用这种方式指定，那么 the floor of 字节数除以pagesize 决定了Extentsize的值。数据库管理器在存储数据时重复循环使用各个容器。

The default value is provided by the dft_extent_sz database configuration parameter, which has a valid range of 2-256 pages.
默认值为数据库配置参数 dft_extent_sz 的值，extentsize在2-256页有效。
PREFETCHSIZE number-of-pagesSpecifies 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.数据预取时从表空间读取的PAGESIZE页面数量。prefetchsize值可以指定为整数，后面跟K，M，G。如果使用这种方式指定，那么 the floor of 字节数除以pagesize 决定了prefetchsize的值OVERHEAD number-of-millisecondsSpecifies 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.指与I/O控制器活动、磁盘寻道时间、旋转延迟时间相关联的时间。该值用于确定查询优化时的I/O成本。number-of-milliseconds 的值为数字（整型，十进制、浮点型）。如果所有容器不是同一个overhead值,那么这个值为表空间所有容器的平均值。

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.
DB2 V9及更新版本，overhead默认值为7.5ms.从旧版本升级到V9或者更新版本的，默认值为12.67ms.
TRANSFERRATE number-of-millisecondsSpecifies 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.指将一页读入内存所需要的时间量。该值用于确定查询优化时的I/O成本。number-of-milliseconds 的值为数字（整型，十进制、浮点型）。如果所有容器不是同一个overhead值,那么这个值为表空间所有容器的平均值。

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.
DB2 V9及更新版本，transferrate默认值为0.06ms.从旧版本升级到V9或者更新版本的，默认值为0.18ms.
NO FILE SYSTEM CACHINGSpecifies 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.指所有I/O操作绕过文件系统级的缓存。更多信息看“Table spaces without file system caching”。对于大多数配置这是默认选项。更多信息看“File system caching configurations”。FILE SYSTEM CACHINGSpecifies 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.指定所有的I/O操作缓存到文件系统层。更多信息看“Table spaces without file system caching”。对于一些配置这是默认选项。更多信息看“File system caching configurations”。AUTORESIZESpecifies 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.指定DMS表空间和自动存储表空间是否启用自动扩展。自动扩展表空间在表空间满时自动扩充。DMS表空间默认NO，自动存储表空间默认YES。 NOSpecifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be disabled.禁用YESSpecifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled.启用INITIALSIZE integerSpecifies 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,M,G才有效。注意使用的实际值会比指定的值略微小一些，因为数据库管理器会在表空间中保持各个容器大小一致。而且，当数据库是自动扩展大小的、初始大小不足以容纳必须添加到表空间的源数据时，则DBM使用 INCREASESIZE 自动扩充表空间直到有足够的空间为止。如果没指定，则DBM会确定一个合适的值。integer 至少48K。KK (for kilobytes).MM (for megabytes).GG (for gigabytes).INCREASESIZE integerSpecifies 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).百分数，表空间会增长它当前大小的百分比。0-100有效。
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.
注意使用的实际值会比指定的值略小或略大，因为数据库管理器会保持每个表空间容器增长一致。如果表空间的自扩充的，而没有指定 INCREASESIZE ，那么DBM会确定一个适合的值。
PERCENTPercent from 0 to 100.KK (for kilobytes).MM (for megabytes).GG (for gigabytes).MAXSIZESpecifies 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为没有限制。默认为NONE。
NONESpecifies that the table space is to be allowed to grow to file system capacity, or to the maximum table space size.integerSpecifies 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. KK (for kilobytes).MM (for megabytes).GG (for gigabytes).WITH comment-stringDescribes 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.介绍数据库目录的数据库条目。可以输入能够帮助描述数据库的任何内容。最大长度30字符，不能有回车、换行符。用单引号或者双引号括起来。AUTOCONFIGUREBased 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 tweaks the recommendations.根据用户输入，计算缓冲池大小、数据库配置和数据库管理器配置的推荐设置，并且有选择的适用于他们。默认运行 Configuration Advisor 。需要微调建议设置时适用AUTOCONFIGURE选项。
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 DATABASEcommand 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?
APPLYDB ONLYDisplays 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 DBMDisplays and applies the recommended changes to the database manager configuration, the database configuration, and the buffer pool settings.显示并且使用推荐的变更设置DBM cfg ，DB cfg 和缓冲池设置。NONEDisables the Configuration Advisor (it is enabled by default).禁用Configuration Advisor（默认启用）
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.如果在 CREATE DATABASE 命令中指定AUTOCONFIGURE 关键字，则不考虑DB2_ENABLE_AUTOCONFIG_DEFAULT参数了。自适应的自调优内存和自动RUNSTATS将被启用，Configuration Advisor将根据 APPLY DB 或者 APPLY DBM 选项调整数据库配置和数据库管理器配置参数。
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.带AUTOCONFIGURE 选项的 CREATE DATABASE 命令将建议启用自调整内存管理器。然而，如果AUTOCONFIGURE 命令运行在 sheapthres 不为0的实例上，则sort memory tuning (sortheap)不能自动启用。要启用sort memory tuning (sortheap)，你必须使用UPDATE DATABASE MANAGER CONFIGURATION 将 sheapthres 值设成0。注意变更sheapthres 的值可能会影响你之前已存在的数据库的使用。
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
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.在指定的子目录下创建数据库。在一个分区数据库环境下，则会在db2nodes.cfg文件中列出的所有数据库分区上创建数据库，并在每个数据库分区上指定的子目录下创建$DB2INSTANCE/NODExxxx目录。在单分区数据库环境下，在指定的子目录下创建 $DB2INSTANCE/NODE0000 目录。
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. 服务器的本地数据库目录在path指定的目录上，如果没有指定则使用DBM参数 dftdbpath 指定的值。本地数据库目录驻留在包含一个数据库的每个文件系统上。
Server's system database directory for the attached instance. The resulting directory entry will contain the database name and a database alias. attached实例的服务器系统数据库目录。最终的目录条目将包含数据库名称和数据库别名。
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.如果不存在则会创建一个系统数据库目录或者本地数据库目录。如果指定了，则这两个目录的内容和codeset 都会被替换掉。

Note: If the change the database by path configuration parameter newlogpath is not set, the default for the location of log files configuration parameter logpath is the path shown by the DBPATH ON option. 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.
如果newlogpath 没指定，默认情况下logpath 为 DBPATH ON 指定的路径。这就建议我们将自动存储数据库信息和数据库数据分开存放时使用 DBPATH ON 选项。
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.储存指定的codeset,territory,collating 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.创建schema:SYSCAT, SYSFUN, SYSIBM, and SYSSTAT,SYSIBM是所有者。执行命令的数据库分区服务器称为这个新数据库的编目数据库分区。自动创建两个数据库分区组： IBMDEFAULTGROUP 和 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 called NULLID is implicitly created when performing the binds with CREATEIN privilege granted to PUBLIC, if the RESTRICTIVE option is not selected.将这个数据库绑定到之前定义的数据库管理bind文件（db2ubind.lst）。如果有一个或者多个文件没有绑定成功， CREATE DATABASE 会在SQLCA中返回一个警告信息，并提供绑定失败的信息。若绑定失败，用户可以采取相应的操作手动绑定错误文件。数据库会在任务情况下都创建。如果没有指定 RESTRICTIVE 选项，当执行 CREATEIN 特权给PUBLIC的绑定时会隐式创建NULLID的schema。
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 which is not at the latest level, warnings pertaining to these two files are returned, and can be disregarded.如果db2ubind.lst绑定的服务器不是最新的，会返回这两个文件的警告信息，可以忽略。
Creates SYSCATSPACE, TEMPSPACE1, and USERSPACE1 table spaces. The SYSCATSPACE table space is only created on the catalog database partition.创建SYSCATSPACE, TEMPSPACE1 和 USERSPACE1表空间。仅会在编目数据库分区创建SYSCATSPACE表空间。
Note: If the RESTRICTIVE option 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.
NOTE：如果指定 RESTRICTIVE 选项，则DB CFG 参数 restrict_access 为YES，不会为PUBLIC授予任何权限和特权。
Compatibilities 相容性
For compatibility with versions earlier than Version 8:对于V8之前的版本
The keyword NODE can be substituted for DBPARTITIONNUM. 可以使用NODE关键字代替DBPARTITIONNUM。