DB2 Version 10.1 for Linux, UNIX, and Windows

CREATE TABLESPACE statement

The CREATE TABLESPACE statement defines 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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.

Syntax


>>-CREATE--+-----------------------+---------------------------->
           +-LARGE-----------------+   
           +-REGULAR---------------+   
           | .-SYSTEM-.            |   
           '-+--------+--TEMPORARY-'   
             '-USER---'                

>--TABLESPACE--tablespace-name---------------------------------->

>--+-----------------------------------------------------------+-->
   |     .-DATABASE PARTITION GROUP-.                          |   
   '-IN--+--------------------------+--db-partition-group-name-'   

>--+--------------------------+--------------------------------->
   '-PAGESIZE--integer--+---+-'   
                        '-K-'     

   .-MANAGED BY--AUTOMATIC STORAGE--| storage-group |--| size-attributes |--.   
>--+------------------------------------------------------------------------+-->
   '-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'   
                 '-DATABASE--| database-containers |--| size-attributes |-'     

>--+---------------------------------+-------------------------->
   '-EXTENTSIZE--+-number-of-pages-+-'   
                 '-integer--+-K-+--'     
                            '-M-'        

>--+-----------------------------------+------------------------>
   '-PREFETCHSIZE--+-AUTOMATIC-------+-'   
                   +-number-of-pages-+     
                   '-integer--+-K-+--'     
                              '-M-'        

>--+-----------------------------+------------------------------>
   '-BUFFERPOOL--bufferpool-name-'   

>--+--------------------------------------+--------------------->
   '-OVERHEAD--+-number-of-milliseconds-+-'   
               '-INHERIT----------------'     

>--+------------------------+----------------------------------->
   +-NO FILE SYSTEM CACHING-+   
   '-FILE SYSTEM CACHING----'   

>--+------------------------------------------+----------------->
   '-TRANSFERRATE--+-number-of-milliseconds-+-'   
                   '-INHERIT----------------'     

>--+--------------------------------+--------------------------->
   '-DATA TAG--+-integer-constant-+-'   
               +-INHERIT----------+     
               '-NONE-------------'     

>--+---------------------------------+-------------------------><
   '-DROPPED TABLE RECOVERY--+-ON--+-'   
                             '-OFF-'     

storage-group

|--+-----------------------------------+------------------------|
   '-USING STOGROUP--storagegroup-name-'   

size-attributes

|--+---------------------+--+-----------------------------+----->
   '-AUTORESIZE--+-NO--+-'  '-INITIALSIZE--integer--+-K-+-'   
                 '-YES-'                            +-M-+     
                                                    '-G-'     

>--+------------------------------------+----------------------->
   '-INCREASESIZE--integer--+-PERCENT-+-'   
                            '-+-K-+---'     
                              +-M-+         
                              '-G-'         

>--+-----------------------------+------------------------------|
   '-MAXSIZE--+-integer--+-K-+-+-'   
              |          +-M-+ |     
              |          '-G-' |     
              '-NONE-----------'     

system-containers

   .----------------------------------------------------------------------.   
   |           .-,------------------.                                     |   
   V           V                    |                                     |   
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
                                          '-| on-db-partitions-clause |-'     

database-containers

   .--------------------------------------------------------------.   
   V                                                              |   
|----USING--| container-clause |--+-----------------------------+-+--|
                                  '-| on-db-partitions-clause |-'     

container-clause

      .-,---------------------------------------------------.      
      V                                                     |      
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
        '-DEVICE-'                      '-integer--+-K-+--'        
                                                   +-M-+           
                                                   '-G-'           

on-db-partitions-clause

|--ON--+-DBPARTITIONNUM--+-------------------------------------->
       '-DBPARTITIONNUMS-'   

      .-,--------------------------------------------------.      
      V                                                    |      
>--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'

Description

LARGE, REGULAR, SYSTEM TEMPORARY, or USER TEMPORARY

Specifies the type of table space that is to be created. If no type is specified, the default is determined by the MANAGED BY clause.

LARGE

Stores all permanent data. This type is only allowed on database managed space (DMS) table spaces. It is also the default type for DMS table spaces when no type is specified. When a table is placed in a large table space:

The table can be larger than a table in a regular table space. For details on table and table space limits, see "SQL and XML limits".
The table can support more than 255 rows per data page, which can improve space utilization on data pages.
Indexes that are defined on the table will require an additional 2 bytes per row entry, compared to indexes defined on a table that resides in a regular table space.

REGULAR

Stores all permanent data. This type applies to both DMS and SMS table spaces. This is the only type allowed for SMS table spaces, and it is also the default type for SMS table spaces when no type is specified.

SYSTEM TEMPORARY

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

USER TEMPORARY

Stores created temporary tables and declared temporary tables. No user temporary table spaces exist when a database is created. To allow the definition of created temporary tables or declared temporary tables, at least one user temporary table space should be created with appropriate USE privileges.

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 DATABASE PARTITION GROUP db-partition-group-name

Specifies the database partition group for the table space. The database partition group must exist. The only database partition group that can be specified when creating a SYSTEM TEMPORARY table space is IBMTEMPGROUP. The DATABASE PARTITION GROUP keywords are optional.

If the database partition group is not specified, the default database partition group (IBMDEFAULTGROUP) is used for REGULAR, LARGE, and USER TEMPORARY table spaces. For SYSTEM TEMPORARY table spaces, the default database partition group 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, 8 192, 16 384, or 32 768. The valid values for integer with the suffix K are 4, 8, 16, or 32. Any number of spaces is allowed between integer and K, including no space. An error occurs if the page size is not one of these values (SQLSTATE 428DE), or if the page size is not the same as the page size of the buffer pool that is associated with the table space (SQLSTATE 428CB).

The default value is provided by the pagesize database configuration parameter, which is set when the database is created.

MANAGED BY AUTOMATIC STORAGE

Specifies that the table space is to be an automatic storage table space. If there are no storage groups defined, an error is returned (SQLSTATE 55060).

The database manager automatically decides how the automatic storage table space is initially created. Temporary table spaces are initialized as system managed space (SMS) table space and permanent table spaces are initialized as database managed space (DMS) table space. When creating a permanent table space and the type of table space is not specified, the default behavior is to create a large table space. With an automatic storage table space, the database manager determines which containers are to be assigned to the table space, based upon the storage paths that are associated with the storage group the table space uses.

storage-group

Specify the storage group for an automatic storage table space.

USING STOGROUP: For an automatic storage table space, identifies the storage group for the table space in which the table space data will be stored. If a storagegroup-name is not specified, then the currently designated default storage group is used. This clause only applies to automatic storage table spaces (SQLSTATE 42613).
storagegroup-name: Identifies the storage group in which table space data will be stored. storagegroup-name must identify a storage group that exists at the current server (SQLSTATE 42704). This is a one-part name.

size-attributes

Specify the size attributes for an automatic storage table space or a DMS table space that is not an automatic storage table space. SMS table spaces are not auto-resizable.

AUTORESIZE

Specifies whether or not the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled. Auto-resizable table spaces automatically increase in size when they become full. The default is NO for DMS table spaces and YES for automatic storage table spaces.

NO: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be disabled.
YES: Specifies that the auto-resize capability of a DMS table space or an automatic storage table space is to be enabled.

INITIALSIZE integer K | M | G

Specifies the initial size, per database partition, of an automatic storage table space. This option is only valid for automatic storage table spaces. The integer value must be followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). Note that the actual value used might be slightly smaller than what was specified, because the database manager strives to maintain a consistent size across containers in the table space. Moreover, if the table space is auto-resizable and the initial size is not large enough to contain meta-data that must be added to the new table space, the database manager will continue to extend the table space by the value of INCREASESIZE until there is enough space. If the INITIALSIZE clause is not specified, the database manager determines an appropriate value. The value for integer must be at least 48 K.

INCREASESIZE integer PERCENT or INCREASESIZE integer K | M | G

Specifies the amount, per database partition, by which a table space that is enabled for auto-resize will automatically be increased when the table space is full, and a request for space has been made. The integer value must be followed by:

PERCENT to specify the amount as a percentage of the table space size at the time that a request for space is made. When PERCENT is specified, the integer value must be between 0 and 100 (SQLSTATE 42615).
K (for kilobytes), M (for megabytes), or G (for gigabytes) to specify the amount in bytes

Note that the actual value used might be slightly smaller or larger than what was specified, because the database manager strives to maintain consistent growth across containers in the table space. If the table space is auto-resizable, but the INCREASESIZE clause is not specified, the database manager determines an appropriate value.

MAXSIZE integer K | M | G or MAXSIZE NONE

Specifies the maximum size to which a table space that is enabled for auto-resize can automatically be increased. If the table space is auto-resizable, but the MAXSIZE clause is not specified, the default is NONE.

integer: Specifies a hard limit on the size, per database partition, to which a DMS table space or an automatic storage table space can automatically be increased. The integer value must be followed by K (for kilobytes), M (for megabytes), or G (for gigabytes). Note that the actual value used might be slightly smaller than what was specified, because the database manager strives to maintain consistent growth across containers in the table space.
NONE: Specifies that the table space is to be allowed to grow to file system capacity, or to the maximum table space size (described in "SQL and XML limits").

MANAGED BY SYSTEM

Specifies that the table space is to be an SMS table space.

MANAGED BY SYSTEM cannot be specified in a DB2® pureScale® environment (SQLSTATE 42997).

Important: The SMS table space type has been deprecated in Version 10.1 for user-defined permanent table spaces and might be removed in a future release. The SMS table space type is not deprecated for catalog and temporary table spaces. For more information, see SMS permanent table spaces have been deprecated

system-containers

Specify the containers for an SMS table space.

USING ('container-string',…)

For an SMS table space, identifies one or more containers that will belong to the table space and in which the table space 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, and can be a path name alias (a symbolic link on UNIX systems) to storage that is not physically associated with the database directory. For example, dbdir/work/c1 could be a symbolic link to a separate file system.

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 exists, it must not contain any files or subdirectories (SQLSTATE 428B2).

The format of container-string is dependent on the operating system. On Windows operating systems, an absolute directory path name begins with a drive letter and a colon (:); on UNIX systems, an absolute path name begins with a forward slash (/). A relative path name on any platform does not begin with an operating system-dependent character.

For file-level protocols, such as NAS and CIFS, remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently supported only when the following technologies are used:

Network Appliance Filers
IBM® Network Attached Storage
NEC iStorage S2100, S2200, or S4100
NEC Storage NS Series with a database server on Windows
Note:
NEC Storage NS Series is supported only with the use of an uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended.

An NFS-mounted file system on AIX® must be mounted in uninterruptible mode by using the -o nointr option.

Start of change Block-level protocols, such as iSCSI and FCP, are supported by any backend storage that has non-volatile RAM or battery backup. The storage technology must guarantee that successful writes are not lost in the event of failure, such as a power outage. End of change

on-db-partitions-clause

Specifies the database partition or partitions on which the containers are created in a partitioned database. If this clause is not specified, then the containers are created on the database partitions in the database partition group that are not explicitly specified in any other on-db-partitions-clauses. For a SYSTEM TEMPORARY table space defined on database partition group IBMTEMPGROUP, when the on-db-partitions-clause is not specified, the containers will also be created on all new database partitions added to the database.

MANAGED BY DATABASE

Specifies that the table space is to be a DMS table space. When the type of table space is not specified, the default behavior is to create a large table space.

MANAGED BY DATABASE cannot be specified in a DB2 pureScale environment (SQLSTATE 42997).

Important: Starting with Version 10.1 Fix Pack 1, the DMS table space type is deprecated for user-defined permanent table spaces and might be removed in a future release. The DMS table space type is not deprecated for catalog and temporary table spaces. For more information, see DMS permanent table spaces have been deprecated.

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 in which the table space 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, 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, 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.

For file-level protocols, such as NAS and CIFS, remote resources (such as LAN-redirected drives or NFS-mounted file systems) are currently supported only when the following technologies are used:

Network Appliance Filers
IBM Network Attached Storage
NEC iStorage S2100, S2200, or S4100
NEC Storage NS Series with a database server on Windows
Note:
NEC Storage NS Series is supported only with the use of an uninterrupted power supply (UPS); continuous UPS (rather than standby) is recommended.

on-db-partitions-clause

Specifies the database partition or partitions on which the containers are created in a partitioned database. If this clause is not specified, then the containers are created on the database partitions in the database partition group that are not explicitly specified in any other on-db-partitions-clause. For a SYSTEM TEMPORARY table space defined on database partition group IBMTEMPGROUP, when the on-db-partitions-clause is not specified, the containers will also be created on all new database partitions added to the database.

on-db-partitions-clause

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

ON DBPARTITIONNUMS

Keywords indicating that individual database partitions are specified. DBPARTITIONNUM is a synonym for DBPARTITIONNUMS.

db-partition-number1: Specify a database partition number.
TO db-partition-number2: Specify a range of database partition numbers. The value of db-partition-number2 must be greater than or equal to the value of db-partition-number1 (SQLSTATE 428A9). Containers are to be created on each database partition between and including the specified values. A specified database partition must be in the database partition group for the table space.

The database partition specified by number, and every database partition within the specified range of database partitions must exist in the database partition group for the table space (SQLSTATE 42729). A database partition number can only appear explicitly or within a range in exactly one on-db-partitions-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) or M (for megabytes). If specified in this way, the floor of the number of bytes divided by the page size is used to determine the value for the extent size. The database manager cycles repeatedly through the containers as data is stored.

In a DB2 pureScale environment, you should use an extent size of at least 32 pages. This minimum extent size reduces the amount of internal message traffic within the DB2 pureScale environment when extents are added for a table or index.

The default value is provided by the dft_extent_sz database configuration parameter, which has a valid range of 2-256 pages.

PREFETCHSIZE

Specifies to read in data needed by a query before 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 database configuration parameter.

AUTOMATIC: Specifies that the prefetch size of a table space is to be updated automatically; that is, the prefetch size will be managed by the DB2 database manager.
DB2 will update the prefetch size automatically whenever the number of containers in a table space changes (following successful execution of an ALTER TABLESPACE statement that adds or drops one or more containers). The prefetch size is also automatically updated at database startup.
number-of-pages: Specifies the number of PAGESIZE pages that will be read from the table space when data prefetching is being performed. The maximum value is 32767.
integer K | M: Specifies the prefetch size value as an integer value followed by K (for kilobytes) or M (for megabytes). If specified in this way, the floor of the number of bytes divided by the page size is used to determine the number of pages value for prefetch size.

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 buffer pool must match the page size specified (or defaulted) for the table space (SQLSTATE 428CB). The database partition group of the table space must be defined for the buffer pool (SQLSTATE 42735).

OVERHEAD number-of-milliseconds or OVERHEAD INHERIT

Specifies the I/O controller overhead and disk seek and latency time. This value is used to determine the cost of I/O during query optimization. If OVERHEAD is not specified for a non-automatic storage table space, the value will default to the database creation default described later in the description for this keyword. If OVERHEAD is not specified for an automatic storage table space the default is to INHERIT the value from the storage group it is using. If the OVERHEAD value at the storage group is undefined, the OVERHEAD will default to the database creation default.

number-of-milliseconds: The value of number-of-milliseconds is any numeric literal (integer, decimal, or floating point). If this value is not the same for all containers, the number should be the average for all containers that belong to the table space.
INHERIT: If INHERIT is specified, the table space must be defined using automatic storage and the OVERHEAD is dynamically inherited from the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42613).

For a database that was created in DB2 V10.1 or later, the default I/O controller overhead and disk seek and latency time for 4 KB PAGESIZE table space is 6.725 milliseconds.

For a database that was upgraded from a previous version of DB2 to DB2 V10.1 or later, the default I/O controller overhead and disk seek and latency time is as follows:

7.5 milliseconds for a database created in DB2 version 9.1 or higher
12.67 milliseconds for databases created between DB2 version 8.2 and DB2 version 9.1
24.1 milliseconds for DB2 versions previous to 8.2

FILE SYSTEM CACHING or NO FILE SYSTEM CACHING

Specifies whether or not I/O operations are to be cached at the file system level. If neither option is specified, the default is:

FILE SYSTEM CACHING for JFS on AIX, Linux System z®, all non-VxFS file systems on Solaris, HP-UX, SMS temporary table space files on all platforms, and all LOB and large data
NO FILE SYSTEM CACHING on all other platforms and file system types

FILE SYSTEM CACHING: Specifies that all I/O operations in the target table space are to be cached at the file system level.
NO FILE SYSTEM CACHING: Specifies that all I/O operations are to bypass the file system-level cache.
Note: On AIX, Solaris, Linux, and Windows operating systems, you must format the disk device to have a disk sector size of 512 bytes. On HP-UX operating systems, the disk sector size must be 1024 bytes.

TRANSFERRATE number-of-milliseconds or TRANSFERRATE INHERIT

Specifies the time to read one page into memory. If TRANSFERRATE is not specified for a non-automatic storage table space, the value will default to the database creation default described later in the description for this keyword. If TRANSFERRATE is not specified for an automatic storage table space the default is to INHERIT the value from the storage group it is using. If the DEVICE READ RATE value at the storage group is undefined, the TRANSFERRATE will default to the database creation default.

number-of-milliseconds

This value is used to determine the cost of I/O during query optimization. The value of number-of-milliseconds is any numeric literal (integer, decimal, or floating point). If this value is not the same for all containers, the number should be the average for all containers that belong to the table space.

INHERIT

If INHERIT is specified, the table space must be defined using automatic storage and the TRANSFERRATE is dynamically inherited from the DEVICE READ RATE of the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42613).

When an automatic storage table space inherits the TRANSFERRATE setting from the storage group it is using, the DEVICE READ RATE of the storage group, which is in megabytes per second, is converted into milliseconds per page read accounting for the PAGESIZE setting of the table space. The conversion formula follows:

TRANSFERRATE = ( 1 / DEVICE READ RATE ) * 1000 / 1024000 * PAGESIZE

For a database that was created in DB2 V10.1 or later, the default time to read one page into memory for 4 KB PAGESIZE table space is 0.04 milliseconds.

For a database that was upgraded from a previous version of DB2 to DB2 V10.1 or later, the default time to read one page into memory is as follows:

0.06 milliseconds for a database created in DB2 version 9.1 or higher
0.18 milliseconds for databases created between DB2 version 8.2 or version 9.1
0.9 milliseconds for DB2 versions previous to 8.2

DATA TAG integer-constant, DATA TAG INHERIT or DATA TAG NONE

Specifies a tag for the data in the table space. If the DATA TAG is not specified, the default for automatic storage table spaces is to INHERIT from the storage group it is using and for non-automatic table spaces it will be set to NONE. This value can be used as part of a WLM configuration in a work class definition or referenced within a threshold definition; for more information refer to the CREATE WORK CLASS SET and CREATE THRESHOLD statements. This clause cannot be specified if TEMPORARY is also specified (SQLSTATE 42613).

integer-constant: Valid values for integer-constant are integers from 1 to 9. If an integer-constant is specified and there is an associated storage group, the data tag specified for the table space will override any data tag value specified for the associated storage group.
INHERIT: If INHERIT is specified, the table space must be defined using automatic storage and the data tag is dynamically inherited from the storage group. INHERIT cannot be specified if the table space is not defined using automatic storage (SQLSTATE 42613).
NONE: If NONE is specified, there is no data tag.

DROPPED TABLE RECOVERY

Indicates whether dropped tables in the specified table space can be recovered using the RECOVER DROPPED TABLE option of the ROLLFORWARD DATABASE command. This clause can only be specified for a regular or large table space (SQLSTATE 42613).

ON: Specifies that dropped tables can be recovered. This has been the default since Version 8.
OFF: Specifies that dropped tables cannot be recovered. This is the default in Version 7.

Rules

If automatic storage is not defined for the database, an error is returned (SQLSTATE 55060).
The INITIALSIZE clause cannot be specified with the MANAGED BY SYSTEM or MANAGED BY DATABASE clause (SQLSTATE 42601).
The AUTORESIZE, INCREASESIZE, or MAXSIZE clause cannot be specified with the MANAGED BY SYSTEM clause (SQLSTATE 42601).
The AUTORESIZE, INITIALSIZE, INCREASESIZE, or MAXSIZE clause cannot be specified for the creation of a temporary automatic storage table space (SQLSTATE 42601).
The INCREASESIZE or MAXSIZE clause cannot be specified if the tables space is not auto-resizable (SQLSTATE 42601).
AUTORESIZE cannot be enabled for DMS table spaces that are defined to use raw device containers (SQLSTATE 42601).
A table space must initially be large enough to hold five extents (SQLSTATE 57011).
The maximum size of a table space must be larger than its initial size (SQLSTATE 560B0).
Container operations (ADD, EXTEND, RESIZE, DROP, or BEGIN NEW STRIPE SET) cannot be performed on automatic storage table spaces, because the database manager is controlling the space management of such table spaces (SQLSTATE 42858).
Each container definition requires 53 bytes plus the number of bytes necessary to store the container name. The combined length of all container definitions for the table space cannot exceed 208 kilobytes (SQLSTATE 54034).
For a partitioned database, if more than one database partition resides on the same physical node, the same device or path cannot be specified for more than one database partition (SQLSTATE 42730). In this environment, either specify a unique container-string for each database partition, or use a relative path name.
Only automatic storage table spaces can be created in a DB2 pureScale environment. (SQLSTATE 42997)
Container size limits: In DMS table spaces, a container must be at least two times the extent size pages in length (SQLSTATE 54039). The maximum size of a container is operating system dependent.

Notes

Choosing between a database-managed space or a system-managed space for a table space is a fundamental choice involving trade-offs.
When more than one TEMPORARY table space exists in the database, they are used in round-robin fashion to balance their usage.
The owner of the table space is granted USE privilege with the WITH GRANT OPTION on the table space when it is created.
An automatic storage table space is created as either an SMS table space or a DMS table space. DMS is chosen for large and regular table spaces, and SMS is chosen for temporary table spaces. Note that this behavior cannot be depended upon, because it might change in a future release. When DMS is chosen and the type of table space is not specified, the default behavior is to create a large table space.
The creation of an automatic storage table space does not include container definitions. The database manager automatically determines the location and size, if applicable, of the containers on the basis of the storage paths that are associated with the specified storage group or the default storage group. The database manager will attempt to grow large and regular table spaces, as necessary, provided that the maximum size has not been reached. This might involve extending existing containers or adding containers to a new stripe set. Every time that the database is activated, the database manager automatically reconfigures the number and location of the containers for temporary table spaces that are not in an abnormal state.
A large or regular automatic storage table space will not use new storage paths (see the description of the ALTER STOGROUP statement) until there is no more space in one of the existing storage paths that the table space is using. Temporary automatic storage table spaces can only use the new storage paths once the database has been deactivated and then reactivated.

Media attributes: The following table shows how the media attributes of newly created table spaces are treated in upgraded and newly created DB2 V10.1 databases.

Table 1. Media attributes across different versions of DB2
Media attributes	Upgraded Database	Newly Created Database
New automatic storage table spaces / storage group DEVICE READ RATE set to undefined	Defaults based on version database was created (no change)	Not applicable
New automatic storage table spaces / storage group OVERHEAD set to undefined	Defaults based on version database was created (no change)	Not applicable
New automatic storage table spaces / storage group DEVICE READ RATE is set	Inherit from storage group factoring in PAGESIZE	Inherit from storage group factoring in PAGESIZE
New automatic storage table spaces / storage group OVERHEAD is set	Inherit from storage group	Inherit from storage group
New non-automatic storage table spaces	Defaults based on version database was created (no change)	DB2 V10.1 media defaults taking PAGESIZE into account

Default TRANSFERRATE in DB2 V10.1: The following table shows how the default TRANSFERRATE value differs for newly created table spaces in DB2 V10.1.
Table 2. Default TRANSFERRATE in DB2 V10.1
PAGESIZE TRANSFERRATE

4 KB 0.04 ms per page read

8 KB 0.08 ms per page read

16 KB 0.16 ms per page read

32 KB 0.32 ms per page read
Syntax alternatives: The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
- NODE can be specified in place of DBPARTITIONNUM
- NODES can be specified in place of DBPARTITIONNUMS
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP
- LONG can be specified in place of LARGE

Table 2. Default TRANSFERRATE in DB2 V10.1
PAGESIZE	TRANSFERRATE
4 KB	0.04 ms per page read
8 KB	0.08 ms per page read
16 KB	0.16 ms per page read
32 KB	0.32 ms per page read

Examples

Example 1: Create a large DMS table space on a UNIX system using three 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 12.67
     TRANSFERRATE 0.18

Example 2: Create a regular SMS table space on Windows using three 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 system temporary DMS table space on a UNIX system using two files of 50 000 pages each, and a 256-page extent size.

   CREATE TEMPORARY TABLESPACE TEMPSPACE2
     MANAGED BY DATABASE
     USING (FILE 'dbtmp/tempspace2.f1' 50000,
       FILE 'dbtmp/tempspace2.f2' 50000)
     EXTENTSIZE 256

Example 4: Create a large DMS table space in database partition group ODDNODEGROUP (database partitions 1, 3, and 5) on a UNIX system. Use the device /dev/rhdisk0 for 10 000 4K pages on each database partition. Specify a database partition-specific device with 40 000 4K pages for each database partition.

   CREATE TABLESPACE PLANS
     MANAGED BY DATABASE
     USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn1hd01' 40000)
     ON DBPARTITIONNUM (1)
     USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn3hd03' 40000)
     ON DBPARTITIONNUM (3)
     USING (DEVICE '/dev/rhdisk0' 10000, DEVICE '/dev/rn5hd05' 40000)
     ON DBPARTITIONNUM (5)

Example 5: Create a large automatic storage table space named DATATS, allowing the system to make all decisions with respect to table space size and growth.
```
   CREATE TABLESPACE DATATS
```
or
```
   CREATE TABLESPACE DATATS
     MANAGED BY AUTOMATIC STORAGE
```

Example 6: Create a system temporary automatic storage table space named TEMPDATA.

   CREATE TEMPORARY TABLESPACE TEMPDATA

   CREATE TEMPORARY TABLESPACE TEMPDATA
     MANAGED BY AUTOMATIC STORAGE

Example 7: Create a large automatic storage table space named USERSPACE3 with an initial size of 100 megabytes and a maximum size of 1 gigabyte.
```
   CREATE TABLESPACE USERSPACE3
     INITIALSIZE 100 M
     MAXSIZE 1 G
```
Example 8: Create a large automatic storage table space named LARGEDATA with a growth rate of 10 percent (that is, its total size increases by 10 percent each time that it is automatically resized) and a maximum size of 512 megabytes. Instead of specifying the INITIALSIZE clause, let the database manager determine an appropriate initial size for the table space.
```
   CREATE LARGE TABLESPACE LARGEDATA
     INCREASESIZE 10 PERCENT
     MAXSIZE 512 M
```
Example 9: Create a large DMS table space named USERSPACE4 with two file containers (each container being 1 megabyte in size), a growth rate of 2 megabytes, and a maximum size of 100 megabytes.
```
   CREATE TABLESPACE USERSPACE4
     MANAGED BY DATABASE USING (FILE '/db2/file1' 1 M, FILE '/db2/file2' 1 M)
     AUTORESIZE YES
     INCREASESIZE 2 M
     MAXSIZE 100 M
```
Example 10: Create large DMS table spaces, using RAW devices on a Windows operating system.
- To specify entire physical drives, use the \\.\physical-drive format:
```
   CREATE TABLESPACE TS1
     MANAGED BY DATABASE USING (DEVICE '\\.\PhysicalDrive5' 10000,
       DEVICE '\\.\PhysicalDrive6' 10000)
```
- To specify logical partitions by using drive letters:
```
   CREATE TABLESPACE TS2
     MANAGED BY DATABASE USING (DEVICE '\\.\G:' 10000,
       DEVICE '\\.\H:' 10000)
```
- To specify logical partitions by using volume global unique identifiers (GUIDs), use the db2listvolumes utility to retrieve the volume GUID for each local partition, then copy the GUID for the logical partition that you want into the table space container clause:
```
   CREATE TABLESPACE TS3
     MANAGED BY DATABASE USING (
       DEVICE '\\?\Volume{2ca6a0c1-8542-11d8-9734-00096b5322d2}\' 20000M)
```
  You might prefer to use volume GUIDs over the drive letter format if you have more partitions than available drive letters on the machine.
- To specify logical partitions by using junction points (or volume mount points), mount the RAW partition to another NTFS-formatted volume as a junction point, then specify the path to the junction point on the NTFS volume as the container path. For example:
```
   CREATE TABLESPACE TS4
     MANAGED BY DATABASE USING (DEVICE 'C:\JUNCTION\DISK_1' 10000,
       DEVICE 'C:\JUNCTION\DISK_2' 10000)
```
  DB2 first queries the partition to see whether there is a file system on it; if yes, the partition is not treated as a RAW device, and DB2 performs normal file system I/O operations on the partition.