SQL Reference

CREATE TABLESPACE

The CREATE TABLESPACE statement creates a new table space within the database, assigns containers to the table space, and records the table space 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----+-----------+--TABLESPACE--tablespace-name--------->
             +-LONG------+
             '-TEMPORARY-'
 
>-----+-----------------------------------+--------------------->
      |    .-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

REGULAR

Stores all data except for temporary tables.

LONG

Stores long or LOB table columns. The table space must be a DMS table space.

TEMPORARY

Stores temporary tables. (Temporary tables are work areas used by the database manager to perform operations such as sorts or joins.) Note that a database must always have at least one TEMPORARY table space, as temporary tables can only be stored in such a table space. A temporary table space is created automatically when a database is created.

(See CREATE DATABASE in the Command Reference.)

tablespace-name

Names the table space. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The tablespace-name must not identify a table space that already exists in the catalog (SQLSTATE 42710). The tablespace-name must not begin with the characters SYS (SQLSTATE 42939).

IN NODEGROUP nodegroup-name

Specifies the nodegroup for the table space. The nodegroup must exist. The only nodegroup that can be specified when creating a TEMPORARY table space is IBMTEMPGROUP. The NODEGROUP keyword is optional.

If the nodegroup is not specified, the default nodegroup (IBMDEFAULTGROUP) is used unless TEMPORARY is specified and then IBMTEMPGROUP is used.

PAGESIZE integer [K]

Defines the size of pages used for the table space. The valid values for integer without the suffix K are 4 096 or 8 192, 16 384, or 32 768. The valid values for integer with the suffix K are 4 or 8, 16, or 32. An error occurs if the page size is not one of these values (SQLSTATE 428DE) or the page size is not the same as the page size of the bufferpool associated with the table space (SQLSTATE 428CB). The default is 4 096 byte (4K) pages. Any number of spaces is allowed between integer and K, including no space.

MANAGED BY SYSTEM

Specifies that the table space is to be a system managed space (SMS) table space.

system-containers

Specify the containers for an SMS table space.

USING ('container-string',...)

For a SMS table space, identifies one or more containers that will belong to the table space and into which the table space's data will be stored. The container-string cannot exceed 240 bytes in length.

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 table space 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.

on-nodes-clause

Specifies the partition or partitions on which the containers are created in a partitioned database. If this clause or any other on-nodes-clause of this statement is not specified, then the containers are created on all partitions or nodes currently in the nodegroup. For a TEMPORARY table space when the clause is not specified, the containers will also be created on all new partitions or nodes added to the database. See page *** for details on specifying this clause.

MANAGED BY DATABASE

Specifies that the table space is to be a database managed space (DMS) table space.

database-containers

Specify the containers for a DMS table space.

USING

Introduces a container-clause.

container-clause

Specifies the containers for a DMS table space.

(FILE|DEVICE 'container-string' number-of-pages,...)

For a DMS table space, identifies one or more containers that will belong to the table space and into which the table space's data will be stored. The type of the container (either FILE or DEVICE) and its size (in PAGESIZE pages) are specified. The size can also be specified as an integer value followed by K (for kilobytes), M (for megabytes) or G (for gigabytes). If specified in this way, the floor of the number of bytes divided by the pagesize is used to determine the number of pages for the container. A mixture of FILE and DEVICE containers can be specified. The container-string cannot exceed 254 bytes in length.

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 table space 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 table space. 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.

on-nodes-clause

Specifies the partition or partitions on which the containers are created in a partitioned database. If this clause or any other on-nodes-clause of this statement is not specified, then the containers are created on all partitions currently in the nodegroup. For a TEMPORARY table space when the clause is not specified, the containers will also be created on all new partitions added to the database. See page *** for details on specifying this clause.

on-nodes-clause

Specifies the partitions on which containers are created in a partitioned database.

ON NODES

Keywords that indicate that specific partitions are specified. NODE is a synonym for NODES.

node-number1: Specify a specific partition (or node) number.
TO node-number2: Specify a range of partition (or node) numbers. The value of node-number2 must be greater than or equal to the value of node-number1 (SQLSTATE 428A9). All partitions between and including the specified partition numbers are included in the partitions for which the containers are created if the node is included in the nodegroup of the table space.

The partition specified by number and every partition (or node) in the range of partition must exist in the nodegroup on which the table space 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).

EXTENTSIZE number-of-pages

Specifies the number of PAGESIZE pages that will be written to a container before skipping to the next container. The extent size value can also be specified as an integer value followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). If specified in this way, the floor of the number of bytes divided by the pagesize is used to determine the number of pages value for extent size. The database manager cycles repeatedly through the containers as data is stored.

The default value is provided by the DFT_EXTENT_SZ configuration parameter.

PREFETCHSIZE number-of-pages

Specifies the number of PAGESIZE pages that will be read from the table space when data prefetching is being performed. The prefetch size value can also be specified as an integer value followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). If specified in this way, the floor of the number of bytes divided by the pagesize is used to determine the number of pages value for prefetch size. Prefetching reads in data needed by a query prior to it being referenced by the query, so that the query need not wait for I/O to be performed.

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.)

BUFFERPOOL bufferpool-name

The name of the buffer pool used for tables in this table space. The buffer pool must exist (SQLSTATE 42704). If not specified, the default buffer pool (IBMDEFAULTBP) is used. The page size of the bufferpool must match the page size specified (or defaulted) for the table space (SQLSTATE 428CB). The nodegroup of the table space must be defined for the bufferpool (SQLSTATE 42735).

OVERHEAD number-of-milliseconds

Any numeric literal (integer, decimal, or floating point) that specifies the I/O controller overhead and disk seek and latency time, in milliseconds. The number should be an average for all containers that belong to the table space, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.

TRANSFERRATE number-of-milliseconds

Any numeric literal (integer, decimal, or floating point) that specifies the time to read one page into memory, in milliseconds. The number should be an average for all containers that belong to the table space, if not the same for all containers. This value is used to determine the cost of I/O during query optimization.

DROPPED TABLE RECOVERY

Dropped tables in the specified table space may be recovered using the RECOVER TABLE ON option of the ROLLFORWARD command.

Notes

For information on how to determine the correct EXTENTSIZE, PREFETCHSIZE, OVERHEAD, and TRANSFERRATE values, refer to the Administration Guide.
Choosing between a database-managed space or a system-managed space for a table space is a fundamental choice involving tradeoffs. See the Administration Guide for a discussion of those tradeoffs.
When more than one TEMPORARY table space exists in the database, they will be used in round-robin fashion in order to balance their usage. See the Administration Guide for information on using more than one table space, rebalancing and recommended values for EXTENTSIZE, PREFETCHSIZE, OVERHEAD, and TRANSFERRATE.
In a partitioned database if more than one partition resides on the same physical node, then the same device or specific path cannot be specified for such partitions (SQLSTATE 42730). For this environment, either specify a unique container-string for each partition or use a relative path name.

You can specify a node expression for container string syntax when creating either SMS or DMS containers. You would typically specify the node expression if you are using multiple logical nodes in the partitioned database system. This ensures that container names are unique across nodes (database partition servers). When you specify the expression, either the node number is part of the container name, or, if you specify additional arguments, the result of the argument is part of the container name.

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 23. 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

Note:

% is modulus
In all cases, the operators are evaluated from left to right.

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)

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]