Defining databases

When you define a DB2 database, you name an eventual collection of tables and associated indexes, as well as the table spaces in which they are to reside. When you decide whether to define a new database for a new set of objects or use an existing database, consider the following factors:

• You can start and stop an entire database as a unit. You can display the status of all objects in the database by using a single command that names only the database. Therefore, place a set of related tables into the same database. (The same database holds all indexes on those tables.)

• If you want to improve concurrency and memory use, keep the number of tables in a single database relatively small (maximum of 20 tables). For example, with fewer tables, DB2 performs a reorganization in a shorter length of time.

• Having separate databases allows data definitions to run concurrently and uses less space for control blocks as well.

To create a database, use the CREATE DATABASE statement. A name for a database is an unqualified identifier of up to eight characters. A DB2 database name must not be the same as the name of any other DB2 database.

The following example shows a valid database name:

Example: This CREATE DATABASE statement creates the database MYDB:

CREATE DATABASE MYDB
 STOGROUP MYSTOGRP
 BUFFERPOOL BP8K4
 INDEXBP BP4;

The STOGROUP, BUFFERPOOL, and INDEXBP clauses that this example shows establish default values. You can override these values on the definitions of the table space or index space.

You do not need to define a database to use DB2; for development and testing, you can use the default database, DSNDB04. This means that you can define tables and indexes without specifically defining a database. The catalog table SYSIBM.SYSDATABASE describes the default database and all other databases.

Recommendation: Do not use the default database for production work.