Creating a table space within a database assigns containers to the table space and records its definitions and attributes in the database system catalog. You can then create tables within this table space.
See Designing and Choosing Table Spaces for design information on table spaces.
The syntax of the CREATE TABLESPACE statement is discussed in detail in the SQL Reference manual. For information on SMS and DMS table spaces, see Designing and Choosing Table Spaces.
To create a table space using the Control Center:
|
To create an SMS table space using the command line, enter:
CREATE TABLESPACE <NAME>
MANAGED BY SYSTEM
USING ('<path>')
To create an SMS table space using the command line, enter:
CREATE TABLESPACE <NAME>
MANAGED BY DATABASE
USING (FILE'<path>' <size>)
The following SQL statement creates an SMS table space on OS/2 or Windows NT using three directories on three separate drives:
CREATE TABLESPACE RESOURCE
MANAGED BY SYSTEM
USING ('d:\acc_tbsp', 'e:\acc_tbsp', 'f:\acc_tbsp')
The following SQL statement creates a DMS table space on OS/2 using two file containers each with 5,000 pages:
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (FILE'd:\db2data\acc_tbsp' 5000,
FILE'e:\db2data\acc_tbsp' 5000)
In the above two examples, explicit names have been provided for the containers. However, if you specify relative container names, the container is created in the subdirectory created for the database (see Database Directories).
In addition, if part of the path name specified does not exist, the database manager creates it. If a subdirectory is created by the database manager, it may also be deleted by the database manager when the table space is dropped.
The assumption in the above examples is that the table spaces are not associated with a specific nodegroup. The default nodegroup IBMDEFAULTGROUP is used when the following parameter is not specified in the statement:
IN nodegroup
The following SQL statement creates a DMS table space on a UNIX-based system using three logical volumes of 10 000 pages each, and specifies their I/O characteristics:
CREATE TABLESPACE RESOURCE
MANAGED BY DATABASE
USING (DEVICE '/dev/rdblv6' 10000,
DEVICE '/dev/rdblv7' 10000,
DEVICE '/dev/rdblv8' 10000)
OVERHEAD 24.1
TRANSFERRATE 0.9
The UNIX devices mentioned in this SQL statement must already exist, and the instance owner and the SYSADM group must be able to write to them.
The following example creates a DMS table space on a nodegroup called ODDNODEGROUP in a UNIX partitioned database. ODDNODEGROUP must be previously created with a CREATE NODEGROUP statement. In this case, the ODDNODEGROUP nodegroup is assumed to be made up of database partitions numbered 1, 3, and 5. On all database partitions, use the device /dev/hdisk0 for 10 000 4 KB pages. In addition, declare a device for each database partition of 40 000 4 KB pages.
CREATE TABLESPACE PLANS
MANAGED BY DATABASE
USING (DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n1hd01' 40000) ON NODE 1
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n3hd03' 40000) ON NODE 3
(DEVICE '/dev/HDISK0' 10000, DEVICE '/dev/n5hd05' 40000) ON NODE 5
UNIX devices are classified into two categories: character serial devices and block-structured devices. For all file-system devices, it is normal to have a corresponding character serial device (or raw device) for each block device (or cooked device). The block-structured devices are typically designated by names similar to "hd0" or "fd0". The character serial devices are typically designated by names similar to "rhd0", "rfd0", or "rmt0". These character serial devices have faster access than block devices. The character serial device names should be used on the CREATE TABLESPACE command and not block device names.
The overhead and transfer rate help to determine the best access path to use when the SQL statement is compiled. See Chapter 22, Application Considerations for information on the OVERHEAD and TRANSFERRATE parameters.
DB2 can greatly improve the performance of sequential I/O using the sequential prefetch facility, which uses parallel I/O. See Understanding Sequential Prefetching for details on this facility.
You can also create a table space that uses a page size larger than the default 4 KB size. The following SQL statement creates an SMS table space on a UNIX-based system with an 8 KB page size.
CREATE TABLESPACE SMS8K
PAGESIZE 8192
MANAGED BY SYSTEM
USING ('FSMS_8K_1')
BUFFERPOOL BUFFPOOL8K
Notice that the associated buffer pool must also have the same 8 KB page size.
The created table space cannot be used until the buffer pool it references is activated.
The ALTER TABLESPACE SQL statement can be used to add a container to a DMS table space and modify the PREFETCHSIZE, OVERHEAD, and TRANSFERRATE settings for a table space. The transaction issuing the table space statement should be committed as soon as possible, to prevent system catalog contention.
| Note: | The PREFETCHSIZE should be a multiple of the EXTENTSIZE. For example if the EXTENTSIZE is 10, the PREFETCHSIZE should be 20 or 30. For more information, See Understanding Sequential Prefetching for more information. |
A system temporary table space is used to store system temporary tables. When a database is created, one of the three default table spaces defined is a system temporary table space called "TEMPSPACE1".
| Note: | A database must always have at least one system temporary table space since system temporary tables can only be stored in such a table space. |
You can use the CREATE TABLESPACE statement to create another system temporary table space. For example,
CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp
MANAGED BY SYSTEM
USING ('d:\tmp_tbsp','e:\tmp_tbsp')
The only nodegroup that can be specified when creating a system temporary table space is IBMTEMPGROUP.
A user temporary table space is used to store declared temporary tables.
You can use the CREATE TABLESPACE statement to create a user temporary table space:
CREATE USER TEMPORARY TABLESPACE usr_tbsp
MANAGED BY DATABASE
USING (FILE 'd:\db2data\user_tbsp' 5000,
FILE 'e:\db2data\user_tbsp' 5000)
Like regular table spaces, user temporary table spaces may be created in any nodegroup other than IBMTEMPGROUP. The default nodegroup used when creating a user temporary table space is IBMDEFAULTGROUP.
The DECLARE GLOBAL TEMPORARY TABLE statement defines declared temporary tables for use within a user temporary table space.
By placing a table space in a multiple database partition nodegroup, all of the tables within the table space are divided or partitioned across each database partition in the nodegroup. The table space is created into a nodegroup. Once in a nodegroup, the table space must remain there; it cannot be changed to another nodegroup. The CREATE TABLESPACE statement is used to associate a table space with a nodegroup.
DB2 Universal Database supports direct disk access (raw I/O). This allows you to attach a direct disk access (raw) device to any DB2 Universal Database system. (The only exceptions are the Linux, Windows 95, and Windows 98 operating systems.) The following list demonstrates the physical and logical methods for identifying this type of device:
where N represents one of the physical drives in the system. In
this case, N could be replaced by 0, 1, 2, or any other positive
integer:
\\.\PhysicalDisk5
where N: represents a logical drive letter in the system. For example, N: could be replaced by E: or any other drive letter.