Creating a DB2 UDB Database with the CREATE DATABASE Command

Creating a DB2 UDB Database with the `CREATE DATABASE` Command

Now that you have a basic understanding of the types of DB2 UDB objects available, let's look at how a database is created. There are two ways to create a DB2 UDB database: by using the Create Database Wizard or by using the CREATE DATABASE command. Because the Create Database Wizard is essentially a graphical user interface (GUI) for the CREATE DATABASE command, we will look at the command method first.

In its simplest form, the syntax for the CREATE DATABASE command is:

 CREATE [DATABASE  DB] [  DatabaseName  ] <AT NODE>

where:

DatabaseName

Identifies the unique name to be assigned to the database to be created.

The only value you must provide when executing this form of the CREATE DATABASE command is the name to assign the database once it is created. This name:

Can only consist of the characters az, AZ, 09, @, #, $, and _.
Cannot begin with a number.
Cannot begin with the letter sequences "SYS", "DBM", or "IBM".
Cannot be the same as the name already assigned to another database within the same instance.

Of course, a much more complex form of the CREATE DATABASE command, which provides you with much more control over database parameters, is available, and we will look at it shortly. But for now, let's look at what happens when this form of the CREATE DATABASE command is executed.

What Happens When a DB2 UDB Database Is Created

Regardless of how the process is initiated, whenever a new database is created, the following tasks are performed, in the order shown:

All physical directories needed are created in the appropriate location.

Information about each database created is stored in a special hierarchical directory tree. Where this directory tree is actually created is determined by information provided with the CREATE DATABASE commandif no location information is provided, this directory tree is created in the location specified by the dftdbpath DB2 Database Manager configuration parameter associated with the instance the database is being created under. The root directory of this hierarchical tree is assigned the name of the instance the database is associated with. This directory will contain a subdirectory that has been assigned a name corresponding to the partition's node. If the database is a partitioned database, this directory will be named NODE xxxx , where xxxx is the unique node number that has been assigned to the partition; if the database is a nonpartitioned database, this directory will be named NODE0000. The node-name directory, in turn , will contain one subdirectory for each database that has been created under the nodethe name assigned to each subdirectory corresponds to the database token that has been assigned to the database (the subdirectory for the first database created will be named SQL00001, the subdirectory for the second database will be named SQL00002, and so on.) Figure 4-2 illustrates how this directory hierarchy typically looks in a nonpartitioned database environment.

Figure 4-2. Typical directory hierarchy tree for a nonpartitioned database.

Never attempt to modify this directory structure or any of the files stored in it. Such actions could destroy one or more databases or make them unusable.
Files that are needed for database recovery and other bookkeeping tasks are created.

After the appropriate database-name directory has been created, the following files are created in that directory:

SQLBP.1 . This file contains buffer pool information.

SQLBP.2 . This file is a backup copy of SQLBP.1.

SQLSPCS.1 . This file contains tablespace information.

SQLSPCS.2 . This file is a backup copy of SQLSPCS.1.

SQLDBCON . This file contains database configuration information.

DB2RHIST.ASC . This file contains historical information about backup operations, restore operations, table load operations, table reorganization operations, tablespace alterations, and similar database changes (i.e., the recovery history file).

DB2RHIST.BAK . This file is a backup copy of DB2RHIST.ASC.

SQLOGCTL.LFH . This file contains information about active transaction log files. Recovery operations use information stored in this file to determine how far back in the logs to begin the recovery process.

SQLOGMIR.LFH . This file is a mirrored copy of SQLOGCTL.LFH.

SQLINSLK . This file contains information used to ensure that a database is only assigned to one instance of the DB2 Database Manager.

A subdirectory named SQLOGDIR is also created, and three files (named S0000000.LOG, S0000001.LOG, and S0000002.LOG) are created in this subdirectory. These three files are used to store transaction log records as SQL operations are performed against the database.
The database is cataloged in the system and local database directory (a system and/or local database directory is created first if they don't already exist).

DB2 Universal Database uses a set of special files to keep track of where databases are stored and to provide access to those databases. Because the information stored in these files is used like the information stored in an office-building directory is used, they are referred to as directory files. Whenever a database is created, these directories are updated with the database's name and an alias. (We'll take a closer look at the directory files available when we look at cataloging databases.) If specified, a comment and code set values are also stored in these directories.
One buffer pool is created.

During the database creation process, a buffer pool is created and assigned the name IBMDEFAULTBP. On Linux and UNIX platforms, this buffer pool is 1,000 4K (kilobyte) pages in size; on Windows platforms, this buffer pool is 250 4K pages in size. The actual memory used by this buffer pool (and for that matter, by any other buffer pools that may exist) is allocated when the first connection to the database is established and freed when all connections to the database have been terminated .
One system temporary tablespace and two regular tablespaces are created.

Once the buffer pool IBMDEFAULTBP has been created, three tablespaces associated with this buffer pool are created. These three tablespaces are:
- A regular tablespace named SYSCATSPACE1, which is used to store the system catalog tables and views (which we will look at next ) associated with the database.
- A regular tablespace named USERSPACE1, which is used to store all user-defined objects (such as tables, indexes, and so on) along with user data, index data, and long value data.
- A system temporary tablespace named TEMPSPACE1, which is used as a temporary storage area for operations such as sorting data, reorganizing tables, and creating indexes.
Unless otherwise specified, all three of these tablespaces will be SMS tablespaces with an extent size of 32 4k pages; characteristics for each of these table spaces can be provided as input to the Create Database Wizard or the CREATE DATABASE command.
Four schemas are created.

During the database creation process, the following schemas are created: SYSIBM, SYSCAT, SYSSTAT, and SYSFUN. A special user named SYSIBM is made the owner of each.
The system catalog tables and views are created and initialized .

Once the tablespace SYSCATSPACE has been created, a special set of tables, known as the system catalog table s, are constructed and populated within that tablespace. The DB2 Database Manager uses these system catalog tables to keep track of information like database object definitions, database object dependencies, database object privileges, column data types, and table constraints. A set of system catalog views is created along with the system catalog tables, and these views are typically used when accessing data stored in the system catalog tables. The system catalog tables and views cannot be modified with SQL statements. Instead, they are modified by the DB2 Database Manager whenever:
- A database object (such as a table or index) is created, altered , or deleted.
- Authorizations and/or privileges are granted or revoked .
- Statistical information is collected for a table.
- Packages are bound to the database.
In most cases, the complete characteristics of a database object are stored in one or more system catalog tables when the object is created. However in some cases, such as when triggers and constraints are defined, the actual SQL statement used to create the object is stored instead.
The database configuration file for the database is initialized.

Some of the parameters in the database configuration file (such as code set, territory, and collating sequence) will be set using values that were specified as input for the Create Database Wizard or CREATE DATABASE command. Others will be assigned system default values.
A set of utility programs are bound to the database.

Before some of the DB2 UDB utilities available can work with a database, the packages needed to run those utilities must be created. Such packages are created by binding a set of defined DB2 Database Manager bind files to the database (the set of bind files used are stored in the utilities bind list file db2ubind.lst ).
Authorities and privileges are granted to the appropriate users.

To connect to and work with a particular database, a user must have the authorities and privileges needed to use that database. Therefore, whenever a new database is created, the following authorities and privileges are granted:
- DBADM authority, along with CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, and LOAD privileges, are granted to the user who created the database.
- USE privilege on the tablespace USERSPACE1 is granted to the group PUBLIC.
- CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA privileges are granted to the group PUBLIC.
- SELECT privilege on each system catalog table is granted to the group PUBLIC.
- EXECUTE privilege on all procedures found in the SYSIBM schema is granted to the group PUBLIC.
- EXECUTE WITH GRANT privilege on all functions found in the SYSFUN schema is granted to the group PUBLIC.
- BIND and EXECUTE privileges for each successfully bound utility are granted to the group PUBLIC.

The `CREATE DATABASE` Command

So how do you create a database that uses a DMS tablespace to hold the system catalog tables? Or a database whose tablespaces are to have an extent size of 64? You can create these and other databases by using any combination of the options available with the CREATE DATABASE command. The complete syntax for this command is:

 CREATE [DATABASE  DB] [  DatabaseName  ] <AT DBPARTITION NUM>

 CREATE [DATABASE  DB] [  DatabaseName  ] <ON [  Path  ]> <ALIAS [  Alias  ]> <USING CODESET [  CodeSet  ] TERRITORY [  Territory  ]> <COLLATE USING [  CollateType  ]> <NUMSEGS [  NumSegments  ]> <DFT_EXTENT_SZ [  DefaultExtentSize  ]> <CATALOG TABLESPACE [  TS_Definition  ]> <USER TABLESPACE [  TS_Definition  ]> <TEMPORARY TABLESPACE [  TS_Definition  ]> <WITH "[  Description  ]"> <AUTOCONFIGURE <USING [  Keyword  ] [  Value  ] ,... >     <APPLY [DB ONLY  DB AND DBM  NONE>>

where:

DatabaseName	Identifies the unique name to be assigned to the database to be created.
Path	Identifies the location (drive and/or directory) where the directory hierarchy and files associated with the database to be created are to be physically stored.
Alias	Identifies the alias to be assigned to the database to be created.
CodeSet	Identifies the code set to be used for storing data in the database to be created. (In a DB2 UDB database, each single-byte character is represented internally as a unique number between 0 and 255. This number is referred to as the code point of the character; assignments of code points to every character in a particular character set are called the code page ; and the International Organization for Standardization term for a code page is code set .)
Territory	Identifies the territory to be used for storing data in the database to be created.
CollateType	Identifies the collating sequence (i.e., the sequence in which characters are ordered for the purpose of sorting, merging, and making comparisons) to be used by the database to be created. (Valid values include COMPATABILITY, IDENTITY, NLSCHAR, and SYSTEM.)
NumSegments	Identifies the number of segments that will be created and used to store files for each SMS tablespace used by the database to be created (SYSCATSPACE, USERSPACE1, and TEMPSPACE1 only).
DefaultExtentSize	Identifies the default extent size to be used whenever a tablespace is created and no extent size is specified during the creation process.
Description	A comment used to describe the database entry that will be made in the database directory for the database to be created. The description must be enclosed by double quotation marks.
Keyword	One or more keywords recognized by the AUTOCONFIGURE command. (Valid values include mem_percent, workload_type, num_stmts, tpm, admin_priority, is_populated, num_local_apps, num_remote_apps, isolation, and bp_resizablerefer to the DB2 UDB Command Reference for more information on how the AUTOCONFIGURE command is used).
Value	Identifies the value that is to be associated with the Keyword specified.
TS_Definition	Specifies the definition that is to be used to create the tablespace that will be used to hold the system catalog tables (SYSCATSPACE), user-defined objects (USERSPACE1), and/or temporary objects (TEMPSPACE1). The syntax used to define a system managed (SMS) tablespace is:

 MANAGED BY SYSTEM USING ('[  Container  ]' ,...) <EXTENTSIZE [  ExtentSize  ]> <PREFETCHSIZE [  PrefetchSize  ]> <OVERHEAD [  Overhead  ]> <TRANSFERRATE [  TransferRate  ]>

The syntax used to define a database managed (DMS) tablespace is:

 MANAGED BY DATABASE USING ([FILE  DEVICE] '[  Container  ]'  NumberOfPages  ,...) <EXTENTSIZE [  ExtentSize  ]> <PREFETCHSIZE [  PrefetchSize  ]> <OVERHEAD [  Overhead  ]> <TRANSFERRATE [  TransferRate  ]>

where:

Container	Identifies one or more containers to be used to store data that will be assigned to the tablespace specified. For SMS tablespaces, each container specified must identify a valid directory; for DMS FILE containers, each container specified must identify a valid file; and for DMS DEVICE containers, each container specified must identify an existing device.
NumberOfPages	Specifies the number of 4K pages to be used by the tablespace container.
ExtentSize	Specifies the number of 4K pages of data that will be written in a round- robin fashion to each tablespace container.
PrefetchSize	Specifies the number of 4K pages of data that will be read from the specified tablespace when data prefetching is performed.
Overhead	Identifies the I/O controller overhead and disk-seek latency time (in number of milliseconds ) associated with the containers that belong to the specified tablespace.
TransferRate	Identifies the time, in number of milliseconds, that it takes to read one 4K page of data from a tablespace container and store it in memory.

Now that we have seen the complete syntax for the CREATE DATABASE command, let's go back to our original question. The way you would create a database that uses a DMS tablespace to hold the system catalog tables would be to execute a CREATE DATABASE command that looks something like this:

 CREATE DATABASE SAMPLEDB ON E: USING CODESET 1252 TERRITORY US COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY DATABASE   (FILE 'E:\SYSCATSPACE.DAT', 5000)

When executed, this statement would create a database that

Is physically located on drive E:.
Has been assigned the name SAMPLEDB.
Recognizes the United States/Canada code set. (The code page, along with the territory, is used to convert alphanumeric data to binary data that is stored in the database.)
Uses a collating sequence that is based on the territory used (which in this case is United States/Canada).
Will store the system catalog in a DMS table space that uses the file SYSCATSPACE.DAT as its container. (This file is stored on drive E: and is capable of holding up to 5,000 pages that are 4k in size.)
Is created using default values for all other parameters not specified (i.e., USERSPACE1 and TEMPSPACE1 will be SMS tablespaces, extent sizes will be 32, etc.).