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.
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).
The privileges held by the authorization ID of the statement must include SYSCTRL or SYSADM authority.
>>-CREATE--+-----------------------+----------------------------> +-LARGE-----------------+ +-REGULAR---------------+ | .-SYSTEM-. | '-+--------+--TEMPORARY-' '-USER---' >--TABLESPACE--tablespace-name----------------------------------> >--+-----------------------------------------------------------+--> | .-DATABASE PARTITION GROUP-. | '-IN--+--------------------------+--db-partition-group-name-' >--+--------------------------+---------------------------------> '-PAGESIZE--integer--+---+-' '-K-' .-MANAGED BY--AUTOMATIC STORAGE--| 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-+ '-G-' >--+-----------------------------+------------------------------> '-BUFFERPOOL--bufferpool-name-' >--+----------------------------------+-------------------------> '-OVERHEAD--number-of-milliseconds-' >--+------------------------+-----------------------------------> +-NO FILE SYSTEM CACHING-+ '-FILE SYSTEM CACHING----' >--+--------------------------------------+---------------------> '-TRANSFERRATE--number-of-milliseconds-' >--+---------------------------------+------------------------->< '-DROPPED TABLE RECOVERY--+-ON--+-' '-OFF-' 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-'
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.
The default value is provided by the pagesize database configuration parameter, which is set when the database is created.
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 database.
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.
An NFS-mounted file system on AIX® must be mounted in uninterruptible mode by 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.
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.
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.
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.
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).
The default value is provided by the dft_extent_sz database configuration parameter, which has a valid range of 2-256 pages.
The default value is provided by the dft_prefetch_sz database configuration parameter.
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.
For a database that was created in Version 9 or later, the default I/O controller overhead and disk seek and latency time is 7.5 milliseconds. For a database that was upgraded from a previous version of DB2 to Version 9 or later, the default is 12.67 milliseconds.
For a database that was created in Version 9 or later, the default time to read one page into memory is 0.06 milliseconds. For a database that was upgraded from a previous version of DB2 to Version 9 or later, the default is 0.18 milliseconds.
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
CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
EXTENTSIZE 64
PREFETCHSIZE 32
CREATE TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY DATABASE
USING (FILE 'dbtmp/tempspace2.f1' 50000,
FILE 'dbtmp/tempspace2.f2' 50000)
EXTENTSIZE 256
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)
CREATE TABLESPACE DATATS
or CREATE TABLESPACE DATATS
MANAGED BY AUTOMATIC STORAGE
CREATE TEMPORARY TABLESPACE TEMPDATA
or
CREATE TEMPORARY TABLESPACE TEMPDATA
MANAGED BY AUTOMATIC STORAGE
CREATE TABLESPACE USERSPACE3
INITIALSIZE 100 M
MAXSIZE 1 G
CREATE LARGE TABLESPACE LARGEDATA
INCREASESIZE 10 PERCENT
MAXSIZE 512 M
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
CREATE TABLESPACE TS1
MANAGED BY DATABASE USING (DEVICE '\\.\PhysicalDrive5' 10000,
DEVICE '\\.\PhysicalDrive6' 10000)
CREATE TABLESPACE TS2
MANAGED BY DATABASE USING (DEVICE '\\.\G:' 10000,
DEVICE '\\.\H:' 10000)
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. 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.