DB2® can create table spaces for you. However, you
might also create table spaces explicitly if you manage your own data sets, among other
reasons.
About this task
You can create different types of table spaces. Universal table spaces are best in most cases.
Other types are deprecated. That is, they are supported in DB2 10, but support might be removed in the future.
For more information about the different types, see Types of DB2 table spaces.
Tip: You can alter table
spaces after they are created, but the application of some statements, such as ALTER MAXPARTITIONS,
prevent access to the database until alterations complete. Consider future growth when you define
new table spaces.
Procedure
To explicitly create a table space:
- Issue a CREATE TABLESPACE SQL statement.
- Specify the attributes of the table space.
The table space name is an identifier of up to 8 characters. You can qualify a table space name
with a database name. Consider the following facts about naming guidelines for table spaces:
- If you do not qualify an explicit table space with a database name, the default database name is
DSNDB04.
- If you do not explicitly specify a table space, DB2
implicitly creates the table space with a derived name. The name is derived based on the name of the
table that is being created.
- DB2 either implicitly creates a new database for the table space,
or uses an existing implicitly created database.
The following list introduces some of the clauses of the CREATE TABLESPACE statement that define
the attributes of a table space:
- LOB
- Indicates that the table space is to be a large object (LOB) table space.
- DSSIZE
- Indicates the maximum size, in GB, for each partition or, for LOB table spaces, for each data
set.
The
size of the table space depends on how many partitions are in the table space and on the DSSIZE. The
maximum number of partitions for a partition-by-growth table space depends on the value that is
specified for the MAXPARTITIONS option.
- FREEPAGE integer
- Specifies how often DB2 is to leave a page of free space
when the table space or partition is loaded or reorganized. You specify that DB2 is to set aside one free page for every integer
number of pages. Using free pages can improve performance for applications that perform high-volume
inserts or that update variable-length columns. For details, see Reserving free space for table spaces.
- PCTFREE integer
- Indicates the percentage (integer) of each page that DB2 leaves as free space when the table is loaded or reorganized. Specifying
PCTFREE can improve performance for applications that use high-volume inserts or that update
variable-length columns. For details, see Reserving free space for table spaces.
- .
- COMPRESS
- Specifies that data is to be compressed. You can compress data in a table space to store more
data on each data page. For details, see Compressing your data.
- BUFFERPOOL bpname
- Identifies the buffer pool that this table space is to use and determines the page size of the
table space. The buffer pool is a portion of memory in which DB2 temporarily stores data for retrieval. For more information, see Tuning database buffer pools.
- LOCKSIZE
- Specifies the size of locks that DB2 is to use within the
table space. DB2 uses locks to protect data integrity. Use of
locks results in some processing costs, so choose the lock size carefully. For details, see Specifying the size of locks for a table space.
- MAXPARTITIONS
- Specifies the maximum number of partitions for a partition-by-growth table space. Within this
clause, you can specify the NUMPARTS clause to specify the number of partitions that you want to
create initially.
- MEMBER CLUSTER
- Specifies that data that is inserted by an INSERT operation is not clustered by the implicit
clustering index (the first index), or the explicit clustering index. DB2 locates the data in the table space based on available space. You can use
the MEMBER CLUSTER keyword on range-partitioned universal table spaces and partition-by-growth table
spaces. For details, see Member affinity clustering.
- NUMPARTS
- Indicates that the table space is partitioned. If you also specify the MAXPARTITIONS clause, the
table space is a partition-by-growth table space; otherwise, the table space is a range-partitioned
universal table space.
- MAXROWS
- Specifies the maximum number of rows that DB2 places on
each data page. The integer can range from 1 through 255. If you do not specify MAXROWS, the default
number of rows is 255. Do not use MAXROWS for a LOB table space or a table space in a work file
database.
Examples
The following examples illustrate how to use CREATE TABLE statements to create different types of
table spaces.
- Examples of creating partition-by-growth universal table spaces
The following examples show how to create a partition-by-growth universal table space.
Example 1: In the following SQL statement, the universal table space
is implicitly created by a CREATE TABLE statement.
CREATE TABLE TEST02TB(
C1 SMALLINT,
C2 DECIMAL(9,2),
C3 CHAR(4))
PARTITIONING BY SIZE EVERY 4G
IN TEST02DB;
COMMIT;
Example 2: In the following SQL statement, the partition-by-growth
universal table space has a maximum size of 2 GB for each partition, 4 pages per segment, with a
maximum of 24 partitions for table space.
CREATE TABLESPACE TEST01TS IN TEST01DB USING STOGROUP SG1
DSSIZE 2G
MAXPARTITIONS 24
LOCKSIZE ANY
SEGSIZE 4;
COMMIT;
- Creating range-partitioned universal table spaces
The following examples show how to create a range-partitioned universal table space (UTS).
Example 1: The following SQL statement defines a range-partitioned
universal table space with 16 pages per segment and 55 partitions. This universal table space uses a
storage group SG1 and has LOCKSIZE
ANY.
CREATE TABLESPACE TS1 IN DB1 USING STOGROUP SG1
NUMPARTS 55 SEGSIZE 16
LOCKSIZE ANY;
Example 2: The following SQL statement defines a range-partitioned
universal table space with 64 pages per segment and 7 defer-defined partitions. This universal table
space uses a storage group SG1 and compresses every odd-numbered partition.
CREATE TABLESPACE TS1 IN DB1 USING STOGROUP SG1
NUMPARTS 7
(
PARTITION 1 COMPRESS YES,
PARTITION 3 COMPRESS YES,
PARTITION 5 COMPRESS YES,
PARTITION 7 COMPRESS YES
)
SEGSIZE 64
DEFINE NO;
Example 2:
- Creating segmented table spaces (deprecated)
The following CREATE TABLESPACE statement creates a segmented table space with 32 pages in each
segment:
CREATE TABLESPACE MYTS
IN MYDB
USING STOGROUP MYSTOGRP
PRIQTY 30720
SECQTY 10240
SEGSIZE 32
LOCKSIZE TABLE
BUFFERPOOL BP0
CLOSE NO;
What to do next
Generally, when you use the CREATE TABLESPACE statement with the USING STOGROUP clause, DB2 allocates data sets for the table space. However, if you also
specify the DEFINE NO clause, you can defer the allocation of data sets until data is inserted or
loaded into a table in the table space.