Defining initial table spaces on database creation

When a database is created, three table spaces are defined: (1) SYSCATSPACE for the system catalog tables, (2) TEMPSPACE1 for system temporary tables created during database processing, and (3) USERSPACE1 for user-defined tables and indexes. You can also create additional user table spaces at the same time.

About this task

Note: When you first create a database no user temporary table space is created.

Unless otherwise specified, the three default table spaces are managed by automatic storage.

Using the CREATE DATABASE command, you can specify the page size for the default buffer pool and the initial table spaces. This default also represents the default page size for all future CREATE BUFFERPOOL and CREATE TABLESPACE statements. If you do not specify the page size when creating the database, the default page size is 4 KB.

To define initial table spaces using the command line, enter:

   CREATE DATABASE name
     PAGESIZE page size
     CATALOG TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
       EXTENTSIZE value PREFETCHSIZE value
     USER TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
       EXTENTSIZE value PREFETCHSIZE value
     TEMPORARY TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
     WITH "comment"

If you do not want to use the default definition for these table spaces, you might specify their characteristics on the CREATE DATABASE command. For example, the following command could be used to create your database on Windows:

   CREATE DATABASE PERSONL
     PAGESIZE 16384
     CATALOG TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
       EXTENTSIZE 16 PREFETCHSIZE 32
     USER TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
       EXTENTSIZE 32 PREFETCHSIZE 64
     TEMPORARY TABLESPACE
       MANAGED BY AUTOMATIC STORAGE
     WITH "Personnel DB for BSchiefer Co"

In this example, the default page size is set to 16 384 bytes, and the definition for each of the initial table spaces is explicitly provided. You only need to specify the table space definitions for those table spaces for which you do not want to use the default definition.

Note: When working in a partitioned database environment, you cannot create or assign containers to specific database partitions. First, you must create the database with default user and temporary table spaces. Then you should use the CREATE TABLESPACE statement to create the required table spaces. Finally, you can drop the default table spaces.

The coding of the MANAGED BY phrase on the CREATE DATABASE command follows the same format as the MANAGED BY phrase on the CREATE TABLESPACE statement.

You can add additional user and temporary table spaces if you want. You cannot drop the catalog table space SYSCATSPACE, or create another one; and there must always be at least one system temporary table space with a page size of 4 KB. You can create other system temporary table spaces. You also cannot change the page size or the extent size of a table space after it has been created.