CREATE TABLESPACE statement

The CREATE TABLESPACE statement defines a new table space to a Db2 system.

createTableSpace_SPE2010.png

CREATE TABLESPACE databaseName.tableSpaceName

This option specifies the fully qualified name of the table space to be created.

LARGE

This option indicates whether the table space is a LARGE table space.

LARGE parameter values

Value

Description

YES

For a partitioned table space, indicates that each partition has a maximum partition size of 4 GB

NO

Indicates that the table space is not a LARGE table space

LOB

This option indicates whether the table space is a nonpartitioned table space that contains the data for a large object (LOB) column in an auxiliary table.

LOB parameter values

Value

Description

NO

Indicates that the table space is not a nonpartitioned table space that contains LOB data

YES

Indicates that the table space is a nonpartitioned table space that contains LOB data

OWNER authorizationID

This option specifies the owner of the table space.

DSSIZE integer G

This option specifies the maximum size for each table space partition in gigabtyes.

PAGENUM

This option specifies the page numbering used for the partition-by-range table space.

PAGENUM parameter values

Value

Description

ABSOLUTE

Indicates that page numbering includes a partition number and page number (4-byte value).

RELATIVE

Indicates that page numbering includes a page number (4-byte value).

MEMBER CLUSTER

This option indicates whether inserted data is clustered by the clustering index.

MEMBER CLUSTER parameter values

Value

Description

YES

Indicates that data is clustered based on the availability of space map pages

NO

Indicates that data is not clustered by the clustering index

NUMPARTS number

This option specifies the number of partitions for a table space. NUMPARTS 0 specifies a nonpartitioned table space.

NUMPARTS number PART number tableSpacePartitionParameters

This option specifies each partition of the table space according to the partition number and tableSpacePartitionParameters (see tableSpacePartitionParameters). If PART 64 (or greater) is specified, a LARGE table space is created, even if the LARGE keyword is not specified.

PART 0 tableSpacePartitionParameters

This option specifies a nonpartitioned table space according to tableSpacePartitionParameters (see tableSpacePartitionParameters).

LOCKPART

This option indicates whether selective partition locking is used when locking a partitioned table space.

LOCKPART parameter values

Value

Description

NO

Indicates that selective partition locking is not used

LOCKPART NO is the default.

YES

Indicates that selective partition locking is used

INSERT ALGORITHM

This option specifies the insert algorithm used for inserting rows into tables in a MEMBER CLUSTER UTS table space.

Value

Description

0

Specifies that the insert algorithm level is determined by the DEFAULT_INSERT_ALGORITHM subsystem parameter at the time a row is inserted into a table in the table space.

1

Specifies the basic insert algorithm is used when inserting rows into a table in the table space.

2

Specifies the fast insert algorithm is used when inserting rows into a table in the table space.

tableSpacePartitionParameters

tableSpacePartitionParameters uses the following parameters:

createTableSpacePartitionParameters_SPE2110.png

VCAT catalogName

This option specifies the volume catalog to be used for the table space.

STOGROUP

This option specifies the new default storage group for the table space.

STOGROUP parameter values

Value

Description

storageGroupName

Specifies the name of the storage group

<DEFLT>

Specifies a default value

<DEFLT> is a reserved keyword.

PRIQTY number

Specifies the primary space that is allocated for the Db2 defined data set.

SECQTY number

Specifies the secondary space that is allocated for the Db2 defined data set.

ERASE

Indicates whether the data sets can be erased when they are deleted by a statement that drops the table space.

  • NO (default)

    Specifies not to erase the data sets

  • YES

    Specifies to erase the data sets

FREEPAGE number

This option specifies that a free page is added after every number of pages upon table space creation or reorganization.

PCTFREE number

This option specifies the percentage of free space left on each page of the table space upon creation or reorganization.

PCTFREE parameter values

Value

Description

number

Specifies the percentage of free space left on each page when creating, loading, or reorganizing the table space

FOR UPDATE number

Specifies the percentage of space reserved on each page for reorganizing the table space

DSSIZE integer G

This option specifies the maximum size for the table space partition in gigabtyes.

TRACKMOD

This option indicates whether modified pages are tracked in the space map pages of the table space.

TRACKMOD parameter values

Value

Description

YES

Specifies to track modified pages

TRACKMOD YES is the default.

NO

Specifies not to track modified pages

COMPRESS

This option indicates whether rows in the table space or partition are compressed.

COMPRESS parameter values

Value

Description

NO

Specifies not to compress data

COMPRESS NO is the default.

YES

Specifies to compress data using the default compression algorithm

YES FIXEDLENGTH


Specifies to compress data using the fixed-length data compression algorithm

YES HUFFMAN


Specifies to compress data using the Huffman data compression algorithm

GBPCACHE

This option specifies which pages are cached to the group buffer pool for this partition.

GBPCACHE parameter values

Value

Description

CHANGED

Specifies to cache updated pages to the buffer pool

GBPCACHE CHANGED is the default.

ALL

Specifies to cache all pages as they are read

NONE

Specifies not to cache pages to the buffer pool


 tableSpaceOptionstableSpaceOptions includes the following parameters:

GUID-02A33126-687B-4406-B682-0E092811323E-low.png

BUFFERPOOL

This option specifies the new default buffer pool for the database for the creation of the table space. If the BUFFERPOOL keyword is not specified, the Db2 default value is used.

BUFFERPOOL parameter values

Value

Description

bufferPoolName

Specifies the name of the buffer pool

<DEFLT>

Specifies a default value

<DEFLT> is a reserved keyword.

LOCKSIZE

This option specifies the size of the locks for the table space.

LOCKSIZE parameter values

Value

Description

ANY

Specifies to use any lock size

PAGE

Specifies to use page locks

TABLE

Specifies to use table locks

TABLESPACE

Specifies to use table space locks

ROW

Specifies to use row locks

LOB

For LOB table spaces, specifies to use LOB locks

LOCKMAX

This option specifies the maximum number of locks for the table space.

LOCKMAX parameter values

Value

Description

integer

Specifies the number of page, row, or LOB locks

SYSTEM

Specifies the value used by Db2 for the number of locks

CLOSE

This option indicates whether the index for the table space can be closed when the index is not being used.

CLOSE parameter values

Value

Description

YES

Specifies that the index can be closed

CLOSE YES is the default.

NO

Specifies that the index cannot be closed

MAXPARTITIONS integer

This option specifies the maximum number of partitions in a partition-by-growth table space. The valid range of values is 1 through 4096.

CCSID

This option specifies the encoding scheme of the table space.

CCSID parameter values

Value

Description

ASCII

Specifies ASCII encoding

EBCDIC

Specifies EBCDIC encoding

UNICODE

Specifies Unicode encoding

<DEFLT>

Specifies a default value

The default encoding scheme is determined in the installation options module with the CCSID keyword.

MAXROWS integer

This option specifies the maximum number of rows per page for the table space. The valid range of values is 1 through 255. This parameter does not apply to LOB table spaces.

LOG

This option indicates whether changes to data in a table space are written to a log.

LOG parameter values

Value

Description

YES

Specifies to write changes to a log

LOG YES is the default.

NO

Specifies not to write changes to a log

DEFINE

This option indicates when the data sets for the table space are created.

DEFINE parameter values

Value

Description

YES

Indicates that Db2 creates the data sets when it creates the table space

DEFINE YES is the default.

NO

Indicates that Db2 creates the data sets when a row is inserted into a table

SEGSIZE number

This option specifies the number of pages for each segment of a segmented table space. SEGSIZE 0 specifies a unsegmented table space.

Important

Db2 requires SEGSIZE to be between 4 and 64 pages and divisible by 4.


Related topic

CDL-statements


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*