DB2 - SQL Create Database Statement

The CREATE DATABASE statement is used to create a new DB2 database.

Database creation syntax is as follows

CREATE DATABASE database-name
    BUFFERPOOL bp-name
    INDEXBP idx-name
    STOGROUP stogroup-name
    CCSID ASCII/EBCDIC/UNICODE

Syntax Explanation:

• BUFFERPOOL:

  Specifies the default buffer pool name to be used for table spaces created within the database.

  If you omit the BUFFERPOOL clause, then the default BP, BP0 is used.

• INDEXBP:

  Specifies the default buffer pool name to be used for the indexes created within the database.

  If you omit the INDEXBP clause, the default BP, BP0 is used.

• STOGROUP:

  Specifies the storage group to be used to support DASD space requirements for table spaces and indexes within the database. The default is SYSDEFLT.

• CCSID (Coded Character Set ID)

  Specifies the default encoding scheme for data stored in the database.

  Encoding Schemes are ASCII, EBCDIC, UNICODE.

Example 1:

Create a database DSN1D91P. Specify DSN1G910 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 DSN1D91P
      STOGROUP DSN1G910
      BUFFERPOOL BP8K1
      INDEXBP BP2;

Example 2:

Create a database DSN1TEMP. 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 DSN1TEMP CCSID ASCII;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!