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.
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.
CREATE DATABASE name
PAGESIZE page size
CATALOG TABLESPACE
MANAGED BY SYSTEM USING ('path')
EXTENTSIZE value PREFETCHSIZE value
USER TABLESPACE
MANAGED BY DATABASE USING (FILE 'path' 5000,
FILE 'path' 5000)
EXTENTSIZE value PREFETCHSIZE value
TEMPORARY TABLESPACE
MANAGED BY SYSTEM USING ('path')
WITH "comment"
CREATE DATABASE PERSONL
PAGESIZE 16384
CATALOG TABLESPACE
MANAGED BY SYSTEM USING ('d:\pcatalog','e:\pcatalog')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY DATABASE USING (FILE'd:\db2data\personl' 5000,
FILE'd:\db2data\personl' 5000)
EXTENTSIZE 32 PREFETCHSIZE 64
TEMPORARY TABLESPACE
MANAGED BY SYSTEM USING ('f:\db2temp\personl')
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.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.