To execute the CREATE DATABASE command, you must possess either CREATEDBA or CREATEDBC privileges, or overall SYSADM or SYSCTRL authority for the location.
Bold text indicates clauses or options that are supported only syntactically.
CREATE DATABASE database-name
[BUFFERPOOL bpname]
[INDEXBP bpname]
[ROSHARE {OWNER | READ}]
[AS {WORKFILE | TEMP} [FOR member-name]]
[STOGROUP stogroup-name]
[CCSID {ASCII | EBCDIC | UNICODE}]
| database-name | Names the new database, which must not already exist at the current location. |
| bpname | A name that identifies a bufferpool. The 4K bufferpools are named BP0, BP1, BP2...BP49. The 32K bufferpools are named BP32K, BP32K1, BP32K2...BP32K9. |
| member-name | The name of the DB2 subsystem that the database will become part of. |
| stogroup-name | Identifies a user-defined XDB Server stogroup (drive and path) where database files can be stored. |
The CREATE DATABASE command creates a logical database the current XDB Server location. If a subdirectory for the database does not exist and a stogroup is not specified, a new subdirectory is created under the current location path (with the subdirectory taking on the name of the newly created database). The creator of a database is given DBADM authority for the database.
The CREATE DATABASE command has been enhanced to reflect the new XDB Server system architecture. The BUFFERPOOL clause (DB2-mainframe only) along with the ROSHARE and STOGROUP clauses have been added. The ROSHARE clause is used to create XDB Server read-only shared (ROSHARE) databases, while the STOGROUP clause allows you to store database objects within a user defined XDB Server stogroup (or directory path).
With DB2 V7, XDB locations (either EBCDIC or ASCII) can now contain Unicode data. If you create a database with CCSID UNICODE, all character data in corresponding tables is stored in UTF-8 format and all graphic data is stored in UTF-16 format.
This clause has no functional equivalent within the XDB Server operating environment. However, for DB2 compatibility purposes, The XDB Server records any of the sixty possible bufferpools (BP0...BP49, and BP32K, BP32K1...BP32K9). The first fifty bufferpools are for tables with 4K page sizes, while the last ten are for tables with 32K page sizes. Although not functionally supported, this new information is semantically checked and recorded in the XDB Server system catalog tables.
Specifies the default buffer pool name to be used for table spaces created within the database. If the database is a work file database, 8KB and 16KB buffer pools cannot be specified.
If you omit the BUFFERPOOL clause, the buffer pool specified for user data on installation panel DSNTIP1 is used. The default value for the user data field on that panel is BP0.
Specifies the default buffer pool name to be used for the indexes created within the database. The name must identify a 4KB buffer pool.If the database is a work file database, INDEXBP cannot be specified.
If you omit the INDEXBP clause, the buffer pool specified for user indexes on installation panel DSNTIP1 is used. The default value for the user indexes field on that panel is BP0.
Indicates that this is a work file database or a database for declared temporary tables (a TEMP database).
| AS WORKFILE | Specifies the database is a work file database. AS WORKFILE can be specified only in a data sharing environment. Only one work file database can be created for each DB2 member. |
| AS TEMP | Specifies the database is for declared temporary tables only. AS TEMP must be specified to create a database that will be used for declared temporary tables; otherwise, the database will not be used for declared temporary tables. Only one TEMP database can be created for each DB2 subsystem or data sharing member. A TEMP database cannot be shared between DB2 subsystems or data sharing members.
PUBLIC implicitly receives the CREATETAB privilege (without GRANT authority) to define a declared temporary table in the TEMP database. This implicit privilege is not recorded in the DB2 catalog and cannot be revoked. |
Specifies the member for which this database is to be created. Specify FOR member-name only in a data sharing environment.
If FOR member-name is not specified, the member is the DB2 subsystem on which the CREATE DATABASE statement is executed.
This optional clause is used for the creation of read-only shared (or ROSHARE) databases. The OWNER keyword identifies the database as the owner created and controlled database, while the READ keyword identifies a reading (or dummy) database that can only read (or SELECT) data from the owner database.
Identifies an optional XDB Server-created stogroup where database objects (tables, indexes, views, etc.) can be stored (stogroups allow you to define a path for storing data). To use the defined stogroup, first modify the special XDBPRIMEVOLPATH and XDBVOLPATH column values in the SYSIBM.SYSVOLUMES table to associate the stogroup's VOLID column value with the stogroup path (see CREATE STOGROUP command summary). If a stogroup is specified when creating the database, the XDB Server does not create a database subdirectory under the current location path, but uses the stogroup path instead.
If the optional STOGROUP clause is omitted, the stogroup associated with the new database defaults to SYSDEFLT -- a system default, not representing any physical storage entity on the XDB Server. This omission of a stogroup name causes the XDB Server to create a new database subdirectory under the currently set location directory path, with this information being recorded within the current location system tables.
The CCSID clause is only syntactically supported for EBCDIC and ASCII. This is because the definition of the XDB location itself defines how character data is stored.
If you specify UNICODE, your character and graphic data is stored as UNICODE within the database.
Create database DSN8D61P. Specify DSN8G610 as the default storage group to be used for the table spaces and indexes in the database. Specify 8KB buffer pool BP8K1 as the default buffer pool to be used for table spaces in the database, and BP2 as the default buffer pool to be used for indexes in the database.
CREATE DATABASE DSN8D61P
STOGROUP DSN8G610
BUFFERPOOL BP8K1
INDEXBP BP2;
Create database DSN8TEMP. Use the defaults for the default storage group and default buffer pool names. Specify ASCII as the default encoding scheme for data stored in the database.
CREATE DATABASE DSN8TEMP
CCSID ASCII;