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

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

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

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

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

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

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

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

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

   .-7 OVERHEAD--7 12.67------------------.
>--+----------------------------------+------------------------->
   '-OVERHEAD--number-of-milliseconds-'

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

   .-7 TRANSFERRATE--7 0.18-------------------.
>--+--------------------------------------+--------------------->
   '-TRANSFERRATE--number-of-milliseconds-'

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

size-attributes:

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

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

>--+-----------------------------+------------------------------|
   '-9 MAXSIZE--+-9 integer--+-9 K-+-+-'
              |          +-9 M-+ |
              |          '-9 G-' |
              '-9 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

REGULAR

Stores all data except for temporary tables.

LARGE

Stores long or LOB table columns. It can also store structured type columns or index data. The table space must be a DMS table space.

SYSTEM TEMPORARY

Stores temporary tables (work areas used by the database manager to perform operations such as sorts or joins). The keyword SYSTEM is optional. Note that a database must always have at least one SYSTEM 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.

USER TEMPORARY

Stores declared global temporary tables. Note that no user temporary table spaces exist when a database is created. At least one user temporary table space should be created with appropriate USE privileges, to allow definition of declared temporary tables.

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]

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

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

9 9

MANAGED BY AUTOMATIC STORAGE

9

Specifies that the table space is to be an automatic storage 9 table space. 9 If automatic storage is not defined for the database (that is, the 9 AUTOMATIC STORAGE YES option was not specified on the CREATE 9 DATABASE command), an error is returned (SQLSTATE 55060). 9 9

An automatic storage table space is created as either a system 9 managed space (SMS) table space or a database managed space (DMS) 9 table space. 9 With an automatic storage table space, the database manager 9 determines which containers are to be assigned to the table space, 9 based upon the storage paths that are associated with the database.

9

9 9

size-attributes

9

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

9

AUTORESIZE

9

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

9

NO

9

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

9

YES

9

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

9

9

9

INITIALSIZE integer K | M | G

9

Specifies the initial size, per database partition, of an 9 automatic storage table space. 9 This option is only valid for automatic storage table spaces. 9 The integer value must be followed by K (for kilobytes), M (for 9 megabytes), or G (for gigabytes). 9 Note that the actual value used might be slightly smaller than what 9 was specified, because the database manager strives to maintain a 9 consistent size across containers in the table space. 9 If the table space is auto-resizable, but the INITIALSIZE clause is 9 not specified, the database manager determines an appropriate 9 value. 9

9

INCREASESIZE integer PERCENT or INCREASESIZE integer K | M | G

9

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

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

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

9

MAXSIZE integer K | M | G or MAXSIZE NONE

9

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

9

integer

9

Specifies a hard limit on the size, per database partition, to 9 which a DMS table space or an automatic storage table space can 9 automatically be increased. 9 The integer value must be followed by K (for kilobytes), M (for 9 megabytes), or G (for gigabytes). 9 Note that the actual value used might be slightly smaller than what 9 was specified, because the database manager strives to maintain 9 consistent growth across containers in the table space. 9

9

NONE

9

Specifies that the table space is to be allowed to grow to file 9 system capacity, or to the maximum table space size (described in 9 "SQL limits"). 9

9

9

9

9

MANAGED BY SYSTEM

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

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. 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, a Windows directory path begins with a drive letter and a ":", while on UNIX-based systems, a path begins with a "/".

3 Remote resources (such as LAN-redirected drives 3 or NFS-mounted file systems) are currently only supported when using 3 Network Appliance Filers, IBM iSCSI, IBM Network Attached Storage, 3 Network Appliance iSCSI, NEC iStorage S2100, S2200, or S4100, 3 7 or NEC Storage NS Series with a Windows DB2 server. 7 Note that NEC Storage NS Series is only supported with the use of an 7 uninterrupted power supply (UPS); continuous UPS (rather than 7 standby) is recommended.3 .

on-db-partitions-clause

Specifies the 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 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 partitions added to the database.

MANAGED BY DATABASE

Specifies that the table space is to be a 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 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, 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.

3 Remote resources (such as LAN-redirected drives 3 or NFS-mounted file systems) are currently only supported when using 3 Network Appliance Filers, IBM iSCSI, IBM Network Attached Storage, 3 Network Appliance iSCSI, NEC iStorage S2100, S2200, or S4100, 3 7 or NEC Storage NS Series with a Windows DB2 server. 7 Note that NEC Storage NS Series is only supported with the use of an 7 uninterrupted power supply (UPS); continuous UPS (rather than 7 standby) is recommended.3 .

on-db-partitions-clause

Specifies the 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 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 partitions added to the database.

on-db-partitions-clause

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

ON DBPARTITIONNUMS

Keywords that indicate that specific 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 partition numbers. The value of db-partition-number2 must be greater than or equal to the value of db-partition-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 partition is included in the database partition group of the table space.

The partition specified by number and every partition in the range of partitions must exist in the database partition group on which the table space is defined (SQLSTATE 42729). A partition-number may 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. | |

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

|

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.

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

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.

7 7

FILE SYSTEM CACHING or NO FILE SYSTEM CACHING

7

Specifies whether or not I/O operations are to be cached at the 7 file system level. 7 The default is FILE SYSTEM CACHING. 7 7

7

FILE SYSTEM CACHING

7

Specifies that all I/O operations in the target table space are 7 to be cached at the file system level. 7

7

NO FILE SYSTEM CACHING

7

Specifies that all I/O operations are to bypass the file 7 system-level cache. 7

7

7

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. This clause can only be specified for a REGULAR table space (SQLSTATE 42613).

Rules

• 9 If automatic storage is not defined for the database 9 (that is, the AUTOMATIC STORAGE YES option was not specified on the 9 CREATE DATABASE command), an error is returned (SQLSTATE 55060).
• 9 The INITIALSIZE clause cannot be specified with the 9 MANAGED BY SYSTEM or MANAGED BY DATABASE clause (SQLSTATE 9 42601).
• 9 The AUTORESIZE, INCREASESIZE, or MAXSIZE clause cannot 9 be specified with the MANAGED BY SYSTEM clause (SQLSTATE 9 42601).
• 9 The AUTORESIZE, INITIALSIZE, INCREASESIZE, or MAXSIZE 9 clause cannot be specified for the creation of a temporary automatic 9 storage table space (SQLSTATE 42601).
• 9 The INCREASESIZE or MAXSIZE clause cannot be specified 9 if the tables space is not auto-resizable (SQLSTATE 42601).
• 9 AUTORESIZE cannot be enabled for DMS table spaces that 9 are defined to use raw device containers (SQLSTATE 42601).
• 9 A table space must initially be large enough to hold 9 five extents (SQLSTATE 57011).
• 9 The maximum size of a table space must be larger than 9 its initial size (SQLSTATE 560B0).
• 9 Container operations (ADD, EXTEND, RESIZE, REDUCE, 9 DROP, or BEGIN STRIPE SET) cannot be performed on automatic storage 9 table spaces, because the database manager is controlling the space 9 management of such table spaces (SQLSTATE 42858).

Notes

• Choosing between a database-managed space or a system-managed space for a table space is a fundamental choice involving trade-offs.
• |Each container definition requires 53 bytes plus the |number of bytes necessary to store the container name. |The combined length of all container names for the table space |cannot exceed 20 480 bytes (SQLSTATE 54034).
• 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.
• In a partitioned database, if more than one database partition resides on the same physical node, the same device or specific path cannot be specified for such database partitions (SQLSTATE 42730). For this environment, either specify a unique container-string for each database partition or use a relative path name.
• You can specify a database partition expression for container string syntax when creating either SMS or DMS containers. You would typically specify the database partition expression if you were using multiple logical database partitions in the partitioned database system. This ensures that container names are unique across nodes (database partition servers). When you specify the expression, the database partition 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 a database partition expression. A database partition expression can be used anywhere in the container name, and multiple database partition expressions can be specified. Terminate the database partition expression with a space character; whatever follows the space is appended to the container name after the database partition expression is evaluated. If there is no space character in the container name after the database partition expression, it is assumed that the rest of the string is part of the expression. The argument can only be used in one of the following forms:

  Table 8. Arguments for Creating Containers Operators are evaluated from left to right. The database partition number in the examples is assumed to be 5.

  Syntax	Example	Value
  [blank]$N	" $N"	5
  [blank]$N+[number]	" $N+1011"	1016
  [blank]$N%[number]	" $N%3" a	2
  [blank]$N+[number]%[number]	" $N+12%13"	4
  [blank]$N%[number]+[number]	" $N%3+20"	22
  a % is modulus.

  For example:

     CREATE TABLESPACE TS1 MANAGED BY DATABASE USING
       (device '/dev/rcont $N' 20000)
  
     On a two database partition system, the following containers
      would be created:
       /dev/rcont0  - on DATABASE PARTITION 0
       /dev/rcont1  - on DATABASE PARTITION 1
  
  
     CREATE TABLESPACE TS2 MANAGED BY DATABASE USING
       (file '/DB2/containers/TS2/container $N+100' 10000)
  
     On a four database partition system, the following containers
      would be created:
       /DB2/containers/TS2/container100  - on DATABASE PARTITION 0
       /DB2/containers/TS2/container101  - on DATABASE PARTITION 1
       /DB2/containers/TS2/container102  - on DATABASE PARTITION 2
       /DB2/containers/TS2/container103  - on DATABASE PARTITION 3
  
  
     CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING
       ('/TS3/cont $N%2','/TS3/cont $N%2+2')
  
     On a two database partition system, the following containers
      would be created:
       /TS3/cont0  - On DATABASE PARTITION 0
       /TS3/cont2  - On DATABASE PARTITION 0
       /TS3/cont1  - On DATABASE PARTITION 1
       /TS3/cont3  - On DATABASE PARTITION 1
  
  
     If database partition = 5, the containers:
       '/dbdir/node $N /cont1'
       '/ $N+1000 /file1'
       ' $N%10 /container'
       '/dir/ $N%5+2000 /dmscont'
  
     are created as:
       '/dbdir/node5/cont1'
       '/1005/file1'
       '5/container'
       '/dir/2000/dmscont' 

• 9 An automatic storage table space is created as either 9 an SMS table space or a DMS table space. 9 DMS is chosen for regular and large table spaces, and SMS is chosen 9 for temporary table spaces. 9 Note that this behavior cannot be depended upon, because it might 9 change in a future release.
• 9 The creation of an automatic storage table space does 9 not include container definitions. 9 The database manager automatically determines the location and size, 9 if applicable, of the containers on the basis of the storage paths 9 that are associated with the database. 9 The database manager will attempt to grow regular and large table 9 spaces, as necessary, provided that the maximum size has not been 9 reached. 9 This might involve extending existing containers or adding 9 containers to a new stripe set. 9 Every time that the database is activated, the database manager 9 automatically reconfigures the number and location of the containers 9 for temporary table spaces that are not in an abnormal state.
• 9 A regular or large automatic storage table space will 9 not use new storage paths (see the description of the ALTER DATABASE 9 statement) until there is no more space in one of the existing 9 storage paths that the table space is using. 9 Temporary automatic storage table spaces can only use the new 9 storage paths once the database has been deactivated and 9 then reactivated.
• Compatibilities
  • For compatibility with previous versions of DB2:
    • 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

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 12.67
     TRANSFERRATE 0.18 

Example 2: Create a regular SMS table space on Windows NT or Windows 2000 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 a UNIX-based system 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 in database partition group ODDNODEGROUP (partitions 1,3, and 5) on a UNIX-based system. Use the device /dev/rhdisk0 for 10 000 4K pages on each partition. Specify a partition-specific device with 40 000 4K pages for each 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) 

9 Example 5: Create a regular 9 automatic storage table space named DATATS, allowing the system to 9 make all decisions with respect to table space size and growth. 9

9

   CREATE TABLESPACE DATATS 

9 or 9

9

   CREATE TABLESPACE DATATS
9      MANAGED BY AUTOMATIC STORAGE 

9 Example 6: Create a temporary 9 automatic storage table space named MYTEMP. 9

9

   CREATE TEMPORARY TABLESPACE MYTEMP 

9 or 9

9

   CREATE TEMPORARY TABLESPACE MYTEMP
9      MANAGED BY AUTOMATIC STORAGE 

9 Example 7: Create a regular 9 automatic storage table space named USERSPACE3 with an initial size 9 of 100 megabytes and a maximum size of 1 gigabyte. 9

9

   CREATE TABLESPACE USERSPACE3
9      INITIALSIZE 100 M
9      MAXSIZE 1 G 

9 Example 8: Create a large 9 automatic storage table space named LARGEDATA with a growth rate of 9 10 percent (that is, its total size increases by 10 percent each 9 time that it is automatically resized) and a maximum size of 512 9 megabytes. 9 Instead of specifying the INITIALSIZE clause, let the database 9 manager determine an appropriate initial size for the table space. 9

9

   CREATE LARGE TABLESPACE LARGEDATA
9      INCREASESIZE 10 PERCENT
9      MAXSIZE 512 M 

9 Example 9: Create a regular 9 DMS table space named USERSPACE4 with two file containers (each 9 container being 1 megabyte in size), a growth rate of 2 megabytes, 9 and a maximum size of 100 megabytes. 9

9

   CREATE TABLESPACE USERSPACE4
9      MANAGED BY DATABASE USING (FILE '/db2/file1' 1 M, FILE '/db2/file2' 1 M)
9      AUTORESIZE ON
9      INCREASESIZE 2 M
9      MAXSIZE 100 M 

Related concepts

Related reference

Related samples

• C
• Java Database Connectivity (JDBC)

This topic can be found in: SQL Reference, Volume 2.