ALTER TABLESPACE statement

The ALTER TABLESPACE statement specifies the changes that are necessary to make a remote table space identical to a local table space.

alterTableSpace_SPE2010.png

ALTER TABLESPACE databaseName 1 .tableSpaceName 1

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

DATABASE databaseName 2

This option specifies the new database for this table space.

NAME tableSpaceName 2

This option specifies the new name for this table space.

OWNER authorizationID

This option specifies the new owner of the table space.

CONVERT_TO_PARTITION_BY_GROWTH

This option indicates that the product is converting a table space to a partition-by-growth table space.

NUMPARTS number

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

ALTER PART partitionNumber tableSpacePartitionParameters

For a partitioned table space (NUMPARTS > 1), modifies part partitionNumber according to the parameters in tableSpacePartitionParameters (see tableSpacePartitionParameters).

Important

For a nonpartitioned table space, partitionNumber must be 0.

ADD PART BEFORE partitionNumber tableSpacePartitionParameters

For a partitioned table, adds a new partition according to tableSpacePartitionParameters (see tableSpacePartitionParameters). The partition is added before the specified partitionNumber.

ADD PART AFTER partitionNumber tableSpacePartitionParameters

For a partitioned table, adds a new partition according to tableSpacePartitionParameters (see tableSpacePartitionParameters). The partition is added after the specified partitionNumber.

DROP PART partitionNumber

For a partitioned table space, drops the specified partition.

LOCKPART

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

LOCKPART parameter values

Value

Description

YES

Indicates that selective partition locking is used.

NO

Indicates that selective partition locking is not used.

ROTATE number

For a partitioned table space, this option specifies the number of logical partitions that should be rotated from the first to the last.

Important

ROTATE is used in the ALTER TABLESPACE statement only when you convert an alter-type work ID to CDL.

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.

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

DSSIZE integer G

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

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).

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 tables in the table space.

2

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

tableSpacePartitionParameters

tableSpacePartitionParameters uses the following parameters:

alterTableSpacePartitionParameters_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 value)

    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 gigabytes.

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*