Start of change

Creating table spaces explicitly

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.

Before you begin

For information about how DB2 can create table spaces for you, see Implicitly defined table spaces.

About this task

Begin general-use programming interface information.

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:

  1. Issue a CREATE TABLESPACE SQL statement.
  2. 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.
    • Start of changeDB2 either implicitly creates a new database for the table space, or uses an existing implicitly created database.End of change

    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.
    Start of changeMAXPARTITIONSEnd of change
    Start of changeSpecifies 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.End of change
    Start of changeMEMBER CLUSTEREnd of change
    Start of changeSpecifies 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.End of change
    Start of changeNUMPARTSEnd of change
    Start of changeIndicates 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.End of change
    Start of changeMAXROWSEnd of change
    Start of changeSpecifies 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.End of change

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.

End general-use programming interface information.

End of change