The CREATE TABLESPACE statement creates a new tablespace within the database, assigns containers to the tablespace, and records the tablespace definition and attributes in the catalog.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The authorization ID of the statement must have SYSCTRL or SYSADM authority.
Syntax
.-REGULAR------------------.
>>-CREATE----+--------------------------+----------------------->
+-LONG---------------------+
| .-SYSTEM--. |
'-+---------+---TEMPORARY--'
'-USER----'
>----TABLESPACE--tablespace-name-------------------------------->
>-----+-----------------------------------+--------------------->
| .-NODEGROUP-. |
'-IN-+-----------+--nodegroup-name--'
.-PAGESIZE--4096------------.
>-----+---------------------------+----------------------------->
'-PAGESIZE--integer--+----+-'
'-K--'
>----MANAGED BY--+-SYSTEM--| system-containers |------+--------->
'-DATABASE--| database-containers |--'
>-----+----------------------------------+---------------------->
'-EXTENTSIZE--+-number-of-pages-+--'
'-integer--+-K-+--'
+-M-+
'-G-'
>-----+------------------------------------+-------------------->
'-PREFETCHSIZE--+-number-of-pages-+--'
'-integer--+-K-+--'
+-M-+
'-G-'
>-----+------------------------------+-------------------------->
'-BUFFERPOOL--bufferpool-name--'
>-----+---------------------------------------+----------------->
| .-24.1-------------------. |
'-OVERHEAD--+-number-of-milliseconds-+--'
>-----+-------------------------------------------+------------->
| .-0.9--------------------. |
'-TRANSFERRATE--+-number-of-milliseconds-+--'
>-----+----------------------------------+---------------------><
'-DROPPED TABLE RECOVERY--+-ON--+--'
'-OFF-'
system-containers
.---------------------------------------------------------------------------.
| .-,-------------------------. |
V V | |
|-----USING--(-----'--container-string--'---+---)----+----------------------+--+->
'-| on-nodes-clause |--'
>---------------------------------------------------------------|
database-containers
.---------------------------------------------------------.
V |
|------USING--| container-clause |--+----------------------+--+-|
'-| on-nodes-clause |--'
container-clause
.-,------------------------------------------------------------.
V |
|---(------+-FILE---+--'--container-string--'----+-number-of-pages-+--+---)-->
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'
>---------------------------------------------------------------|
on-nodes-clause
|---ON----+-NODE--+--(------------------------------------------>
'-NODES-'
.-,--------------------------------------.
V |
>--------node-number1--+--------------------+--+--)-------------|
'-TO--node-number2---'
Description
See CREATE DATABASE in the Command Reference for more information.
If the nodegroup is not specified, the default nodegroup (IBMDEFAULTGROUP) is used for REGULAR, LONG and USER TEMPORARY tablespaces. For SYSTEM TEMPORARY tablespaces, the default nodegroup IBMTEMPGROUP is used.
Each container-string can be an absolute or relative directory name. The directory name, if not absolute, is relative to the database directory. If any component of the directory name does not exist, it is created by the database manager. When a tablespace is dropped, all components created by the database manager are deleted. If the directory identified by container-string exist, it must not contain any files or subdirectories (SQLSTATE 428B2).
The format of container-string is dependent on the operating system. The containers are specified in the normal manner for the operating system. For example, an OS/2 Windows 95 and Windows NT directory path begins with a drive letter and a ":", while on UNIX-based systems, a path begins with a "/".
Note that remote resources (such as LAN-redirected drives on OS/2, Windows 95 and Windows NT or NFS-mounted file systems on AIX) are not supported.
For a FILE container, the container-string must be an absolute or relative file name. The file name, if not absolute, is relative to the database directory. If any component of the directory name does not exist, it is created by the database manager. If the file does not exist, it will be created and initialized to the specified size by the database manager. When a tablespace is dropped, all components created by the database manager are deleted.
| Note: | If the file exists it is overwritten and if it is smaller than specified it is extended. The file will not be truncated if it is larger than specified. |
For a DEVICE container, the container-string must be a device name. The device must already exist.
All containers must be unique across all databases; a container can belong to only one tablespace. The size of the containers can differ, however optimal performance is achieved when all containers are the same size. The exact format of container-string is dependent on the operating system. The containers will be specified in the normal manner for the operating system. For more detail on declaring containers, refer to the Administration Guide .
Remote resources (such as LAN-redirected drives on OS/2, Windows 95 and Windows NT or NFS-mounted file systems on AIX) are not supported.
The partition specified by number and every partition (or node) in the range of partition must exist in the nodegroup on which the tablespace is defined (SQLSTATE 42729). A partition-number may only appear explicitly or within a range in exactly one on-nodes-clause for the statement (SQLSTATE 42613).
The default value is provided by the DFT_EXTENT_SZ configuration parameter.
The default value is provided by the DFT_PREFETCH_SZ configuration parameter. (This configuration parameter, like all configuration parameters, is explained in detail in the Administration Guide.)
Notes
You use the argument " $N" ([blank]$N) to
indicate the node expression. The argument must occur at the end of the
container string and can only be used in one of the following forms. In
the table that follows, the node number is assumed to be 5:
Table 24. Arguments for Creating Containers
| Syntax | Example | Value | ||
|---|---|---|---|---|
| [blank]$N | " $N" | 5 | ||
| [blank]$N+[number] | " $N+1011" | 1016 | ||
| [blank]$N%[number] | " $N%3" | 2 | ||
| [blank]$N+[number]%[number] | " $N+12%13" | 4 | ||
| [blank]$N%[number]+[number] | " $N%3+20" | 22 | ||
| ||||
Some examples are as follows:
Example 1:
CREATE TABLESPACE TS1 MANAGED BY DATABASE USING
(device '/dev/rcont $N' 20000)
On a two-node system, the following containers would be used:
/dev/rcont0 - on NODE 0
/dev/rcont1 - on NODE 1
Example 2:
CREATE TABLESPACE TS2 MANAGED BY DATABASE USING
(file '/DB2/containers/TS2/container $N+100' 10000)
On a four-node system, the following containers would be created:
/DB2/containers/TS2/container100 - on NODE 0
/DB2/containers/TS2/container101 - on NODE 1
/DB2/containers/TS2/container102 - on NODE 2
/DB2/containers/TS2/container103 - on NODE 3
Example 3:
CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING
('/TS3/cont $N%2','/TS3/cont $N%2+2')
On a two-node system, the following containers would be created:
/TS3/cont0 - On NODE 0
/TS3/cont2 - On NODE 0
/TS3/cont1 - On NODE 1
/TS3/cont3 - On NODE 1
Examples
Example 1: Create a regular DMS table space on a UNIX-based system using 3 devices of 10 000 4K pages each. Specify their I/O characteristics.
CREATE TABLESPACE PAYROLL
MANAGED BY DATABASE
USING (DEVICE'/dev/rhdisk6' 10000,
DEVICE '/dev/rhdisk7' 10000,
DEVICE '/dev/rhdisk8' 10000)
OVERHEAD 24.1
TRANSFERRATE 0.9
Example 2: Create a regular SMS table space on OS/2 or Windows NT using 3 directories on three separate drives, with a 64-page extent size, and a 32-page prefetch size.
CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
EXTENTSIZE 64
PREFETCHSIZE 32
Example 3: Create a temporary DMS table space on Unix using 2 files of 50,000 pages each, and a 256-page extent size.
CREATE TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY DATABASE
USING (FILE '/tmp/tempspace2.f1' 50000,
FILE '/tmp/tempspace2.f2' 50000)
EXTENTSIZE 256
Example 4: Create a DMS table space on nodegroup ODDNODEGROUP (nodes 1,3,5) on a Unix partitioned database. On all partitions (or nodes), use the device /dev/rhdisk0 for 10 000 4K pages. Also specify a partition specific device for each partition with 40 000 4K pages.
CREATE TABLESPACE PLANS
MANAGED BY DATABASE
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn1hd01' 40000)
ON NODE (1)
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn3hd03' 40000)
ON NODE (3)
USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn5hd05' 40000)
ON NODE (5)