DB2 10.5 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

Read syntax diagramSkip visual syntax diagram
>>-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 (or symbolic link) 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.

Operating system Format of absolute path name
Linux
AIX®
Solaris
HP-UX
An absolute path name begins with a forward slash (/)
Windows An absolute directory path name begins with a drive letter and a colon (:)

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 using the -o nointr option.

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.

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.

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.

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 database manager.

The prefetch size will be updated 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 Version 10.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 Version 10.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.7 or higher
FILE SYSTEM CACHING or NO FILE SYSTEM CACHING
Specifies whether or not I/O operations are to be cached at the file system level or non-cached through the use of direct I/O. If neither option is specified, the I/O mode is determined based on operating system, filesystem, and in the case of SMS tablespaces, data object type. For more information, see the "File system caching configurations" topic. Note that once a non-default file system caching option is chosen, it is not possible to return to the default (unspecified) behaviour. Instead, the file system caching mode must be selected explicitly.
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.  LOB and Long field data in SMS tablespaces are excepted.
Note: You must format the disk device to have a disk sector size according to the following table:
Operating system Disk sector size
AIX 512 bytes
Solaris 512 bytes
HP-UX 1024 bytes
Linux 512 bytes
Windows 512 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 DB2Version 10.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 DB2Version 10.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.17 or higher
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 (see CREATE WORK CLASS SET statement) or referenced within a threshold definition (see CREATE THRESHOLD statement). 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 is the default.
OFF
Specifies that dropped tables cannot be recovered.

Rules

Notes

Examples

  1. Create a large DMS table space on a Linux 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
  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 ('/tbsp/acc1', '/tbsp/acc2', '/tbsp/acc3')
         EXTENTSIZE 64
         PREFETCHSIZE 32
  3. Create a system temporary DMS table space on a Linux 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
  4. Create a large DMS table space in database partition group ODDNODEGROUP (database partitions 1, 3, and 5) on a Linux 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)
  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
  6. Create a system temporary automatic storage table space named TEMPDATA.
       CREATE TEMPORARY TABLESPACE TEMPDATA
    or
       CREATE TEMPORARY TABLESPACE TEMPDATA
         MANAGED BY AUTOMATIC STORAGE
  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
  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
  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 '/db/file1' 1 M, FILE '/db/file2' 1 M)
         AUTORESIZE YES
         INCREASESIZE 2 M
         MAXSIZE 100 M
  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)
      The partition is queried first to see whether there is a file system on it; if yes, the partition is not treated as a RAW device, and normal file system I/O operations are performed on the partition.