CREATE TABLESPACE

The CREATE TABLESPACE statement defines a table space at the current server. The type of table space depends on the keywords specified.

Start of changeFL 504Depending on the keywords specified, the result is a partition-by-range or partition-by-growth table space.End of change

For information about the rules and restrictions for creating table spaces in work file databases, see Table spaces in a work file database.

Start of changeFor information about creating large object (LOB) table spaces, see CREATE LOB TABLESPACE.End of change

Start of changeDb2 creates XML table spaces implicitly when you create or alter tables to add XML columns. For more information, see XML table space implicit creation.End of change

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization

The privilege set that is defined below must include at least one of the following:

  • The CREATETS privilege for the database
  • DBADM, DBCTRL, or DBMAINT authority for the database
  • SYSADM or SYSCTRL authority
  • System DBADM
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Additional privileges might be required, as explained in the description of the BUFFERPOOL and USING STOGROUP clauses.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the of the owner of the plan or package. If the application is bound in a trusted context with the ROLE AS OBJECT OWNER clause specified, a role is the owner. Otherwise, an authorization ID is the owner.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is specified. In that case, the privileges set is the privileges that are held by the role that is associated with the primary authorization ID of the process.

Syntax

Read syntax diagramSkip visual syntax diagram CREATE TABLESPACE table-space-name 1INDSNDB04INdatabase-nameBUFFERPOOLbpnamepartition-by-growth-specificationpartition-by-range-specificationDPSEGSZ-parameter2SEGSIZEintegerCCSIDASCIIEBCDICUNICODECLOSE YESCLOSE NOCOMPRESS NOCOMPRESS YESCOMPRESS YES FIXEDLENGTHCOMPRESS YES HUFFMANDEFINE YESDEFINE NOfree-blockgbpcache-blockINSERT ALGORITHM0INSERT ALGORITHMlevelLOCKMAXSYSTEMintegerlocksize-blockLOGGEDNOT LOGGEDMAXROWSintegerMEMBER CLUSTERTRACKMODimptkmod-parameter3TRACKMOD YESTRACKMOD NOusing-block
Notes:

partition-by-growth-specification:

Read syntax diagramSkip visual syntax diagramMAXPARTITIONS256MAXPARTITIONSintegerNUMPARTS 1NUMPARTSintegerDSSIZEinteger1G
Notes:
  • 1 Specify a power-of-two integer in the range 1 - 256, or accept a default based on the MAXPARTITIONS value and the buffer pool page size. See Table 2.

partition-by-range-specification:

Read syntax diagramSkip visual syntax diagram NUMPARTS integer 1(,PARTITIONintegerusing-blockfree-blockgbpcache-blockCOMPRESS NOCOMPRESS YESCOMPRESS YES FIXEDLENGTHCOMPRESS YES HUFFMANIMPTKMOD-parameter3TRACKMOD YESTRACKMOD NODSSIZE4 G4DSSIZEintegerG)PAGENUMpageset-pagenum-parameter5PAGENUM ABSOLUTE4PAGENUM RELATIVEDSSIZEintegerG6
Notes:
  • 1 Group multiple PARTITION clauses. Other clauses must not be specified more than one time.
  • 2 The same clause must not be specified more than one time.
  • 3 The IMPTKMOD subsystem parameter specified the default TRACKMOD option. For more information, see IMPTKMOD in macro DSN6SYSP.
  • 4 DSSIZE at the partition level can be specified only if PAGENUM RELATIVE is in effect.
  • 5 The PAGESET_PAGENUM subsystem parameter specifies the default PAGENUM value. The default value for PAGESET_PAGENUM is ABSOLUTE. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).
  • 6 If PAGENUM RELATIVE is used, specify any integer in the range 1 - 1024, or accept the default value 4G. If absolute page numbers are used, specify a power-of-two integer in the range 1 - 256, or accept the default value based on the NUMPARTS value and the buffer pool page size. See Table 3.

free-block:

Read syntax diagramSkip visual syntax diagramFREEPAGE 0FREEPAGEintegerPCTFREE 5PCTFREEsmallintsmallintFOR UPDATEsmallint12
Notes:

gbpcache-block:

Read syntax diagramSkip visual syntax diagramGBPCACHE CHANGEDGBPCACHE ALLGBPCACHE SYSTEMGBPCACHE NONE

locksize-block:

Read syntax diagramSkip visual syntax diagramLOCKSIZE ANYLOCKSIZE TABLESPACELOCKSIZE PAGELOCKSIZE ROW

using-block:

Read syntax diagramSkip visual syntax diagram USING VCATcatalog-nameSTOGROUPstogroup-namePRIQTYintegerSECQTYintegerERASE NOERASE YES1
Notes:
  • 1 The same clause must not be specified more than one time.

Description

table-space-name
Names the table space. The name, qualified with the database-name implicitly or explicitly specified by the IN clause, must not identify a table space, index space, or LOB table space that exists at the current server or that exists in the SYSPENDINGOBJECTS catalog table.

A table space that is for declared temporary tables must be in the work file database. PUBLIC implicitly receives the USE privilege (without GRANT authority) on any table space created in the work file database. This implicit privilege is not recorded in the Db2 catalog, and it cannot be revoked.

IN database-name
Specifies the database in which the table space is created. database-name must identify a database that exists at the current server and must not specify the following:
  • DSNDB06
  • A TEMP database
  • An implicitly created database

If the table space is for declared temporary tables or static scrollable cursors, the name of the work file database must be specified.

Start of changeFL 504 If database-name identifies a work file database, the table space must be a partition-by-growth table space.End of change

DSNDB04 is the default.

BUFFERPOOL bpname
Identifies the buffer pool to be used for the table space and determines the page size of the table space. For 4KB, 8KB, 16KB and 32KB page buffer pools, the page sizes are 4 KB, 8 KB, 16 KB, and 32 KB, respectively. The bpname must identify an activated buffer pool, and the privilege set must include SYSADM or SYSCTRL authority, or the USE privilege on the buffer pool. If the table space is to be created in a work file database, you cannot specify 8KB and 16KB buffer pools.

If you do not specify the BUFFERPOOL clause, the default buffer pool of the database is used.

See Naming conventions for more details about bpname. See -ALTER BUFFERPOOL (Db2) for a description of active and inactive buffer pools.

The buffer pool page size affects the number of partitions that can be used. For more information, see Maximum number of partitions and table space size.

partition-by-growth-specification

Specifies the creation of a partition-by-growth table space. Start of changeFL 504 If partition-by-growth-specification and partition-by-range-specification are both omitted, a partition-by-growth table space is created with MAXPARTITIONS 256. End of change For a summary of clauses that control the table space type to create, see Table space types.

MAXPARTITIONS integer
Specifies that the table space is a partition-by-growth table space.

integer specifies the maximum number of partitions to which the table space can grow. integer must be in the range of 1 - 4096, depending on the corresponding value of the DSSIZE clause. The following table shows the maximum value for MAXPARTITIONS in relation to the page size or DSSIZE value for the table space.

Table 1. Maximum MAXPARTITIONS value for a given page size and DSSIZE value
DSSIZE value 4K page size 8K page size 16K page size 32K page size
1 G - 4 G 4096 4096 4096 4096
8 G 2048 4096 4096 4096
16 G 1024 2048 4096 4096
32 G 512 1024 2048 4096
64 G 254 512 1024 2048
128 G 128 256 512 1024
256 G 64 128 256 512
For more information, see Maximum number of partitions and table space size.

The schema definition for the first partition is always created, and the NUMPARTS value, if specified, controls the number of partition schema definitions to initially create and allocate data sets for. The data sets for additional partitions are not allocated until they are needed.

Although the physical data sets are not defined based on the MAXPARTITIONS value, storage and CPU overhead is possible. If an increase in the number of partitions is expected by using the MAXPARTITONS clause, be aware that specifying a value larger than necessary, such as 4096 (the maximum value), as a default for all partition-by-growth table spaces can cause larger than expected storage requests.

NUMPARTS integer
If specified with MAXPARTITIONS, the integer value specifies the number of partition schema definitions to initially create. Data sets are also allocated for this many partitions, unless DEFINE NO is specified. integer must be a value in the range 1 - 4096 inclusive and must be less than or equal to the value that is specified for the MAXPARTITIONS clause.

If MAXPARTITIONS is not specified, see the NUMPARTS clause description in partition-by-range-specification.

DSSIZE integer G
Specifies the data set size in integer gigabytes for partitions in the table space. Each partition occupies one data set, so the data set size is also the maximum size of the partitions.

When DSSIZE is specified for a partition-by-growth table space, it must be a power-of-two integer in the range 1 G - 256 G (1, 2, 4, 8, 16, 32, 64, 128, or 256).

Start of changeFL 504 If the MAXPARTITIONS clause is omitted, the default value for DSSIZE is 4G for any page size. Otherwise, the default value for DSSIZE depends on the buffer pool page size and the specified MAXPARTITIONS value as shown in the following table.End of change

Table 2. DSSIZE defaults for partition-by-growth table spaces
Page size MAXPARTITIONS value DSSIZE default value
Any 1 - 254 4 G
4K 255 - 4096 4 G
8K 255 - 4096 8 G
16K 255 - 4096 16 G
32K 255 - 4096 32 G

For any DSSIZE value greater than 4 G, the data sets for the table space must be associated with a DFSMS data class that is specified with extended format and extended addressability.

The DSSIZE value affects the number of partitions that can be used. For more information, see Maximum number of partitions and table space size.

partition-by-range-specification
Start of changeFL 504 Specifies the creation of a partition-by-range table space. For a summary of clauses that control the table space type to create, see Table space types.End of change
NUMPARTS integer

Start of changeIf specified without MAXPARTITIONS, indicates that a partition-by-range table space is created. The integer value specifies the number of partitions schema definitions to create. Data sets are also allocated for this many partitions, unless DEFINE NO is also specified. integer must be a value in the range 1 - 4096 inclusive. End of change

Start of changeIf MAXPARTITIONS is also specified, see the NUMPARTS clause description in partition-by-growth-specification.End of change

Start of changeFor table spaces created with PAGENUM RELATIVE, the maximum number of partitions is 4096. For table spaces with PAGENUM ABSOLUTE, the maximum number of partitions depends on the buffer pool page size and DSSIZE.End of change The total table space size depends on the number of partitions and DSSIZE. For more information, see Maximum number of partitions and table space size.

PARTITION integer
Specifies the partition to which the following partition-level clauses apply. integer can range from 1 to the number of partitions given by NUMPARTS.

You can specify the PARTITION clause as many times as needed. If you use the same partition number more than once, only the last specification for that partition is used.

Start of change
DSSIZE integer G
When specified in a PARTITION clause for a partition-by-range table space, which is supported only when PAGENUM RELATIVE is specified for the table space, specifies the data set size in integer gigabytes for the partition identified by PARTITION integer. Each partition occupies one data set, so the data set size is also the maximum size of the partition. The integer G value can be any value 1G - 1024G. The default value is 4G.
End of change
Start of changePAGENUMEnd of change
Start of changeIdentifies the type of page numbering that is used when you create a partition-by-range table space. The PAGESET_PAGENUM subsystem parameter specifies the default PAGENUM value. The default value for PAGESET_PAGENUM is ABSOLUTE. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).
ABSOLUTE
Indicates that internal page numbering is kept as a 4-byte value that includes a partition number and page number. Distinguishing which bits represent the partition and which represent the page number requires a shift value. The shift value is LOG base 2 (DSSIZE/(page -size)).
RELATIVE
Indicates that internal page numbering is kept as a 4-byte value without a partition number. The page number is a relative page from the start of the partition, and the partition number is kept only in the header page. When PAGENUM RELATIVE is specified, the data sets for the table space must be associated with a DFSMS data class that is specified with extended format and extended addressability.
End of change
DSSIZE integer G
Specifies the data set size in integer gigabytes for partitions in the table space. Each partition occupies one data set, so the data set size is also the maximum size of the partitions.

Start of changeWhen DSSIZE is specified at the table-space level for a partition-by-range table space with PAGENUM RELATIVE, it can specify any integer value in the range 1 G - 1024 G, and the default value is 4 G. End of change

Start of changeIf absolute page numbers are used, the DSSIZE value must be a power-of-two integer in the range 1 G - 256 G (1, 2, 4, 8, 16, 32, 64, 128, or 256), and the default value depends on the NUMPARTS value and the buffer pool page size, as shown in the following table.End of change

Table 3. DSSIZE defaults for partition-by-range table spaces with absolute page numbering
Page size NUMPARTS value DSSIZE default value
Any 1 - 16 4 G
Any 17 - 32 2 G
Any 33 - 64 1 G
Any 65 - 254 4 G
4K 255 - 4096 4 G
8K 255 - 4096 8 G
16K 255 - 4096 16 G
32K 255 - 4096 32 G

The DSSIZE value affects the number of partitions that can be used. For more information , see Maximum number of partitions and table space size.

For any DSSIZE value greater than 4 G, the data sets for the table space must be associated with a DFSMS data class that is specified with extended format and extended addressability.

SEGSIZE integer
Start of change Specifies the size in pages for each segment of the table space. The integer value must be a multiple of 4, in the range 4 - 64.

If SEGSIZE is not specified, the default SEGSIZE value is controlled by the DPSEGSZ subsystem parameter. SEGSIZE 32 is used if the DPSEGSZ value is 0. Otherwise the SEGSIZE value is the DPSEGSZ value.

However, the DPSEGSZ value has no effect for a table space created in a work file database, and SEGSIZE 16 is used.

End of change
CCSID encoding-scheme
Specifies the encoding scheme for a table in the table space.

If you do not specify a CCSID when it is allowed, the default is the encoding scheme of the database in which the table space resides, except for table spaces in database DSNDB04; for table spaces in DSNDB04, the default is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

ASCII
Specifies that the data is encoded using ASCII CCSIDs. If the database in which the table space is to reside is already defined as ASCII, the ASCII CCSIDs associated with that database are used. Otherwise, the default ASCII CCSIDs of the server are used.
EBCDIC
Specifies that the data is encoded using EBCDIC CCSIDs. If the database in which the table space is to reside is already defined as EBCDIC, the EBCDIC CCSIDs associated with that database are used. Otherwise, the default EBCDIC CCSIDs of the server are used.

Start of changeAn EBCDIC table in the table space can include Unicode columns.End of change

UNICODE
Specifies that the data is encoded using the UNICODE CCSIDs of the server.

Usually, each encoding scheme requires only a single CCSID. Additional CCSIDs are needed when mixed, graphic, or Unicode data is used.

All data stored within a table space must use the same encoding scheme unless the table space is in a work file database.

CLOSE
When the limit on the number of open data sets is reached, specifies the priority in which data sets are closed.
YES
Eligible for closing before CLOSE NO data sets. CLOSE YES is the default value, unless the table space is in a work file database.
NO
Eligible for closing after all eligible CLOSE YES data sets are closed.
COMPRESS
Specifies whether data compression applies to the rows of the table space or a partition in the table space.
For partitioned table spaces, the COMPRESS attribute for a partition is determined from the first of the following conditions that apply to the CREATE statement:
  • The value specified in the COMPRESS clause in the PARTITION clause for that partition
  • The value specified in the COMPRESS clause that is not in any PARTITION clause
  • Otherwise, COMPRESS NO is the default for the partition

For more information about data compression, see Compressing your data.

Start of changeYESEnd of change
Start of changeFL 509 Specifies that data compression is used for the table space or partition. The rows are not compressed until the LOAD or REORG utility is run on a table in the table space or on a partition, or until the total row data size reaches the compression data threshold while an insert operation is performed.

If a keyword for the compression algorithm is not specified, the default compression algorithm is used. The data compression algorithm is determined by the TS_COMPRESSION_TYPE subsystem parameter.

If a keyword for the compression algorithm is specified:
  • LOB table spaces that are implicitly created for LOB columns in this table space are defined as if COMPRESS YES has been specified without a compression algorithm. LOB compression is managed by zEDC hardware if available.
  • XML table spaces that are implicitly created for XML columns in this table space inherit the compression attribute.
FIXEDLENGTH
FL 509 Specified the fixed-length data compression algorithm.
HUFFMAN
FL 509 Specifies the Huffman data compression algorithm. See Using Huffman compression to compress your data for requirements to enable Huffman compression.
End of change
NO
Specifies no data compression for the table space or partition. Inserted rows are not compressed.
DEFINE
Specifies when the underlying data sets for the table space are physically allocated.
YES
The data sets are created when the table space is created (the CREATE TABLESPACE statement is executed). YES is the default.
NO
The data sets are not created until data is inserted into the table space. DEFINE NO is applicable only for Db2-managed data sets (USING STOGROUP is specified). DEFINE NO is ignored for user-managed data sets (USING VCAT is specified). Db2 uses the SPACE column in catalog table SYSTABLEPART to record the status of the data sets (undefined or allocated).

DEFINE NO is not recommended if you intend to use any tools outside of Db2 to manipulate data, such as to load data, because data sets might then exist when Db2 does not expect them to exist. When Db2 encounters this inconsistent state, applications will receive an error.

For table spaces that are created with DEFINE NO, point-in-time recover will not work before data sets exist and before a recovery copy exists.

free-block
Specifies how to leave free space when the table space is loaded or reorganized.
FREEPAGE integer
Specifies how often to leave a page of free space when the table space or partition is loaded or reorganized. You must specify an integer in the range 0 - 255. If you specify 0, no pages are left as free space. Otherwise, one free page is left after every n pages, where n is the specified integer value. Start of changeThe number of pages left must be less than the segment size. If the integer you specify is not less than the segment size, n is one less than the segment size.End of change

The default is FREEPAGE 0, leaving no free pages.

PCTFREE smallint
Indicates what percentage of each page to leave as free space when the table is loaded or reorganized. smallint is in the range from 0 - 99. The first record on each page is loaded without restriction. When additional records are loaded, at least smallint percent of free space is left on each page.

The default is PCTFREE 5, which means that 5% of the space on each page is reserved as free space.

FOR UPDATE smallint
Specifies the percentage of space to reserve as free space on each page, for use by subsequent update operations. The smallint value is an integer in the range -1 - 99. FOR UPDATE -1 specifies that 5% of free space is reserved initially, and the amount of free space is calculated automatically based on certain real-time statistics values. The first record on each page is always loaded without restriction.

An update operation might use more space, if it is available, than is specified by the PCTFREE or PCTFREE FOR UPDATE options.

The default FOR UPDATE value is controlled by the PCTFREE_UPD subsystem parameter.

The value is recorded in the PCTFREE_UPD column of the SYSIBM.SYSTABLEPART catalog table.

The sum of the values for PCTFREE smallint and FOR UPDATE smallint must be less than or equal to 99.

The values of FREEPAGE and PCTFREE for a particular partition are given by the first of these choices that applies:

  • The values of FREEPAGE and PCTFREE given in the PARTITION clause for that partition
  • The values given in a free-block that is not in any PARTITION clause
  • The default values are FREEPAGE 0 and PCTFREE 5.
gbpcache-block

In a data sharing environment, specifies which pages of the table space or partition are written to the group buffer pool. In a non-data-sharing environment, you can specify GBPCACHE for a table space, but it is ignored. However, do not specify GBPCACHE for a table space in a work file database in either environment (data sharing or non-data-sharing).

GBPCACHE
Specifies pages of the table space or partition are written to the group buffer pool.
CHANGED
When there is inter-Db2 R/W interest on the table space or partition, updated pages are written to the group buffer pool. When there is no inter-Db2 R/W interest, the group buffer pool is not used. Inter-Db2 R/W interest exists when more than one member in the data sharing group has the table space or partition open, and at least one member has it open for update. GBPCACHE CHANGED is the default.

If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), CHANGED is ignored and no pages are cached to the group buffer pool.

ALL
Indicates that pages are to be cached in the group buffer pool as they are read in from DASD.

Exception: In the case of a single updating Db2 when no other DB2s have any interest in the page set, no pages are cached in the group buffer pool.

If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), ALL is ignored and no pages are cached to the group buffer pool.

NONE
Indicates that no pages are to be cached to the group buffer pool. Db2 uses the group buffer pool only for cross-invalidation.

If you specify NONE, the table space or partition must not be in recover pending status and must be in the stopped state when the CREATE TABLESPACE statement is executed.

The value of GBPCACHE for a particular partition is determined by the first of these choices that applies:

  1. The GBPCACHE specified in the PARTITION clause for the partition. Do not use more than one gbpcache-block in any PARTITION clause.
  2. The value specified in a gbpcache-block for the table space.
  3. The default value CHANGED.
Start of changeINSERT ALGORITHM levelEnd of change
Start of changeSpecifies the algorithm that is used when rows are inserted into tables in this table space. The insert algorithm level is used only where applicable when MEMBER CLUSTER is specified. The default value is 0.
0
Specifies that the insert algorithm level is determined by the DEFAULT_INSERT_ALGORITHM subsystem parameter at the time a row is inserted.
1
Specifies that the basic insert algorithm is used.
2
Specifies that insert algorithm 2 is used.
End of change
LOCKMAX
Specifies the maximum number of page or row locks an application process can hold simultaneously in the table space. If a program requests more than that number, locks are escalated. The page or row locks are released and the intent lock on the table space is promoted to S or X mode.
integer
Specifies the number of locks allowed before escalating, in the range 0 - 2147483647.

Zero (0) indicates that the number of locks on the table or table space are not counted and escalation does not occur.

SYSTEM
Start of changeFL 507 Specifies that Db2 determines the maximum number of locks that a program can hold simultaneously in the table space from the SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable.

For more information see MAX_LOCKS_PER_TABLESPACE.

End of change
The following table summarizes the results of specifying a LOCKSIZE value while omitting LOCKMAX.
LOCKSIZE Resultant LOCKMAX
ANY SYSTEM
TABLESPACE, PAGE, ROW 0

Start of changeIf the lock size is TABLESPACE, LOCKMAX must be omitted, or its operand must be 0.End of change

locksize-block
Specifies the size of locks used for the table space, and thresholds for lock escalation in some cases.
LOCKSIZE
Specifies the size of locks used within the table space and, in some cases, also the threshold at which lock escalation occurs.
ANY
Specifies that Db2 can use any lock size.

Start of changeFL 507In most cases, Db2 uses LOCKSIZE PAGE LOCKMAX SYSTEM. However, when the number of locks acquired for the table space exceeds the maximum number of locks allowed for a table space (the value of the SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable), the page locks are released and partition level locks are used. For more information see MAX_LOCKS_PER_TABLESPACE.End of change

TABLESPACE
Specifies table space locks.
PAGE
Specifies page locks.
ROW
Specifies row locks.
LOGGED or NOT LOGGED
Specifies whether changes that are made to the data in the specified table space are recorded in the log. LOGGED and NOT LOGGED cannot be specified for table spaces in database DSNDB06, which contains the Db2 catalog. This setting applies to the table that is created in the specified table space and to all indexes of the table. XML table spaces and their indexes inherit the logging attribute from the associated base table space. Auxiliary indexes also inherit the logging attribute from the associated base table space.
LOGGED
Specifies that changes that are made to the data in the specified table space are recorded in the log.

LOGGED is the default.

NOT LOGGED
Specifies that changes that are made to data in the specified table space are not recorded in the log.

NOT LOGGED prevents undo and redo information from being recorded in the log; however, control information for the specified table space will continue to be recorded in the log.

MAXROWS integer
Specifies the maximum number of rows that Db2 will consider placing on each data page. The integer can range from 1 through 255. This value is considered for insert operations, LOAD, and REORG. For LOAD and REORG, the PCTFREE specification is considered before MAXROWS; therefore, fewer rows might be stored than the value you specify for MAXROWS.

If you do not specify MAXROWS, the default number of rows is 255.

MEMBER CLUSTER
Specifies that data inserted by an insert operation is not clustered by the implicit clustering index (the first index) or the explicit clustering index. Instead, Db2 chooses where to locate the data in the table space based on available space.
TRACKMOD
Specifies whether Db2 tracks modified pages in the space map pages.
YES
Db2 tracks changed pages in the space map pages to improve the performance of incremental image copy.
NO
Db2 does not track changed pages in the space map pages. It uses the LRSN value in each page to determine whether a page has been changed.

The TRACKMOD clause can be specified for each partition, or for the table space. If the TRACKMOD value is not specified for a partition, the TRACKMOD value for the table space is used. If the TRACKMOD value for the table space is not specified, the default value specified by the IMPTKMOD subsystem parameter is used. For more information, see IMPTKMOD in macro DSN6SYSP.

using-block

Start of changeSpecifies whether Db2 or the user manages the data sets for the table space or its partitions. The USING clause can be specified for each partition or for the table space. If the USING clause is not specified for a partition, the USING clause specified for the table space is used. If the USING clause is not specified for the table space, the storage group of the database is used, with the normal defaults for PRIQTY, SECQTY, and ERASE. Start of changeIf the USING clause for the table space is omitted, the default storage group for database must exist.End of changeEnd of change

VCAT catalog-name
Indicates that the data set for the partition is managed by the user using the naming conventions set forth in Data set naming conventions. As was true for the nonpartitioned case, catalog-name identifies the catalog for the data set and supplies the first-level qualifier for the data set name.

One or more Db2 subsystems could share integrated catalog facility catalogs with the current server. To avoid the chance of having one of those subsystems attempt to assign the same name to different data sets, select a value for catalog-name that is not used by the other Db2 subsystems.

Start of changeUSING VCAT must not be specified for a partition-by-growth table space.End of change

Db2 assumes one and only one data set for each partition.

STOGROUP stogroup-name
Indicates that Db2 will create a data set for the partition with the aid of a storage group named stogroup-name. The data set is defined during the execution of this statement. Db2 assumes one and only one data set for each partition.

The stogroup-name must identify a storage group that exists at the current server and the privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege for the storage group. The integrated catalog facility catalog used for the storage group must not contain an entry for that data set.

When USING STOGROUP is specified for a partition, the defaults for PRIQTY, SECQTY, and ERASE are the values specified in the USING STOGROUP clause that is not in any PARTITION clause. If that USING STOGROUP clause is not specified, the defaults are those specified in the description of PRIQTY, SECQTY, and ERASE.

PRIQTY integer
Specifies the minimum primary space allocation for a Db2-managed data set. integer must be a positive integer, or -1. In general, when you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is the value of integer. However, the following exceptions exist:
  • For 4KB page sizes, if integer is greater than 0 and less than 200, n is 200.
  • For 8KB page sizes, if integer is greater than 0 and less than 400, n is 400.
  • For 16KB page sizes, if integer is greater than 0 and less than 800, n is 800.
  • For 32KB page sizes, if integer is greater than 0 and less than 1600, n is 1600.
  • For any page size, if integer is greater than 67108864, n is 67108864.

If you do not specify PRIQTY, or specify PRIQTY with a value of -1, Db2 uses a default value for the primary space allocation; for information on how Db2 determines the default value, see Rules for primary and secondary space allocation.

If you specify PRIQTY, and do not specify a value of -1, Db2 specifies the primary space allocation to access method services using the smallest multiple of p KB not less than n, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by Db2. For example, it could be the smallest number of tracks that will accommodate the request. The amount of storage space requested must be available on some volume in the storage group based on VSAM space allocation restrictions. Otherwise, the primary space allocation will fail. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

Executing this statement causes only one data set to be created. However, you might have more data than this one data set can hold. Db2 automatically defines more data sets when they are needed. Regardless of the value in PRIQTY, when a data set reaches its maximum size, Db2 creates a new one. To enable a data set to reach its maximum size without running out of extents, it is recommended that you allow Db2 to automatically choose the value of the secondary space allocations for extents.

If you do choose to explicitly specify SECQTY, to avoid wasting space, use the following formula to make sure that PRIQTY and its associated secondary extent values do not exceed the maximum size of the data set:
PRIQTY + (number of extents * SECQTY) <= DSSIZE (implicit or explicit)
SECQTY integer
Specifies the minimum secondary space allocation for a Db2-managed data set. integer must be a positive integer, 0, or -1. If you do not specify SECQTY, or specify SECQTY with a value of -1, Db2 uses a formula to determine a value. For information on the actual value that is used for secondary space allocation, whether you specify a value or not, see Rules for primary and secondary space allocation.

If you specify SECQTY, and do not specify a value of -1, Db2 specifies the secondary space allocation to access method services using the smallest multiple of p KB not less than integer, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by Db2. For example, it could be the smallest number of tracks that will accommodate the request. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.

ERASE
Indicates whether the Db2-managed data sets for the table space are to be erased when they are deleted during the execution of a utility or an SQL statement that drops the table space.
NO
Does not erase the data sets. Operations involving data set deletion will perform better than ERASE YES. However, the data is still accessible, though not through Db2. This is the default.
YES
Erases the data sets. As a security measure, Db2 overwrites all data in the data sets with zeros before they are deleted.

Notes

Table space types
Start of changeFL 504 In application compatibility level V12R1M504 and higher, the type of partitioning of the resulting table space depends on whether the CREATE TABLESPACE statement specifies the MAXPARTITIONS clause and NUMPARTS clauses, as shown in the following table. If the CREATE TABLESPACE statement specifies the NUMPARTS clause but no MAXPARTITIONS clause, the result is a partition-by-range table space table space. Otherwise, the result is a partition-by-growth table space.

However, below application compatibility level V12R1M504, deprecated table space types including segmented (non-UTS) and partitioned (non-UTS) table spaces can be created. In such cases, the type of table space created depends on the SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses that the CREATE TABLESPACE statement specifies.

Table 4. CREATE TABLESPACE clauses for specifying table space types, by application compatibility level.
Table space type APPLCOMPAT(V12R1M504) and higher APPLCOMPAT(V12R1M503) and lower
Partition-by-growth Any of the following combinations:
  • MAXPARTITIONS and NUMPARTS
  • MAXPARTITIONS
  • Omit both
Any of the following combinations:
  • MAXPARTITIONS and NUMPARTS
  • MAXPARTITIONS and SEGSIZE n1
  • MAXPARTITIONS
Partition-by-range NUMPARTS only NUMPARTS and SEGSIZE n1
Segmented (non-UTS) Not supported2 One of the following combinations:
  • SEGSIZE n1
  • Omit MAXPARTITIONS, NUMPARTS, and SEGSIZE
Partitioned (non-UTS) Not supported2 NUMPARTS and SEGSIZE 0
Notes:
  1. Where n is a non-zero value. The DPSEGSZ subsystem parameter determines the default value. For more information, see DEFAULT PARTITION SEGSIZE field (DPSEGSZ subsystem parameter).
  2. Start of changeFL 504 Non-UTS table spaces for base tables are deprecated. In general, for packages bound with APPLCOMPAT(V12R1M504) or higher, the result of a CREATE TABLESPACE statement is always a partition-by-growth or partition-by-range UTS table space, and a CREATE TABLE statement that specifies a non-UTS table space, including existing multi-table segmented table spaces, returns an error. The only exception is a dynamic CREATE TABLESPACE statement that is executed after the CURRENT APPLICATION COMPATIBILITY special register is set to a value lower than V12R1M504. In this case, CREATE TABLESPACE creates a non-UTS table space, and a CREATE TABLE statement can specify a non-UTS table space. Existing tables in non-UTS table spaces remain supported. However, support is likely to be removed in the future.End of change
End of change
DSSIZE value greater than 4 G
For any DSSIZE value greater than 4 G, the data sets for the table space must be associated with a DFSMS data class that is specified with extended format and extended addressability.
Start of changeFL 504 XML table spacesEnd of change
Start of changeIf a partition-by-growth table space contains an XML column, the corresponding XML table space is a partition-by-growth table space that grows independently of the base table space. If a partition-by-range table space contains an XML column, the corresponding XML table space is a partition-by-range table space that is data partitioned. That is, it is partitioned according to the partitioning scheme of the base table data.End of change
Applications that use currently committed access with table spaces that specify LOCKSIZE PAGE
To ensure that readers of data in a table space that is defined with LOCKSIZE PAGE can always access currently committed data, set MAXROWS to 8 or less. If MAXROWS is greater than 8, readers might need to wait for insert or delete operations on tables in the table space to commit before the readers can access rows in the tables.
Table spaces in a work file database
Start of changeFL 504A table space in a work file database, the table space is always a partition-by-growth table space.End of change

The following restrictions apply to table spaces created in a work file database:

  • They can be created for another member only if both the executing Db2 subsystem and the other member can access the work file data sets. That is required whether the data sets are user-managed or in a Db2 storage group.
  • They cannot use 8 KB or 16 KB page sizes. (The buffer pool in which you define the table space determines the page size. For example, a table space that is defined in a 4 KB buffer pool has 4 KB page sizes.)
  • When you create a table space in a work file database, the following clauses are not allowed:
    • CCSID
    • COMPRESS
    • CLOSE YES
    • DEFINE NO
    • FREEPAGE
    • GBPCACHE
    • LARGE
    • LOCKPART
    • LOCKSIZE
    • LOGGED
    • MAXROWS
    • MEMBER CLUSTER
    • NOT LOGGED
    • Start of changePAGENUMEnd of change
    • PCTFREE
    • SEGSIZE (SEGSIZE 16 is always used)
    • TRACKMOD
Table spaces for declared temporary tables
Declared temporary tables and sensitive static scrollable cursors must reside in segmented table spaces in the work file database. At least one table space with a 32KB page size must exist in the work file database before a declared temporary table can be defined and used or before sensitive static scrollable cursors are opened.

Table spaces in the work file database are shared by work files, created and declared global temporary tables and sensitive static scrollable cursor result tables. You cannot specify which table space is to be used for any specific object.

Table space row formats
Start of changeAll newly created table spaces use re-ordered row format.End of change
Rules for primary and secondary space allocation
You can specify the primary and secondary space allocation or let Db2 choose them. Having Db2 choose the values, especially the secondary space quantity, increases the possibility of reaching the maximum data set size before running out of extents. For more information, see Rules for primary and secondary space allocation.
Maximum number of partitions and table space size
Start of changeFor partition-by-range table spaces created with relative page numbering, the maximum number of partitions is 4096. End of change For partition-by-range table spaces with absolute page numbering or partition-by growth table spaces, the following tables show how the maximum number of partitions and the total table space size depend on the buffer pool page size and DSSIZE value, with 5-byte extended addressability (EA) storage.1
4 KB page size
Table 5. Maximum number of partitions and table space size by DSSIZE, with 4 KB page size
DSSIZE Maximum number of partitions Total table space size
1G 4096 4 TB
2G 4096 8 TB
4G 4096 16 TB
8G 2048 16 TB
16G 1024 16 TB
32G 512 16 TB
64G 256 16 TB
128G 128 16 TB
256G 64 16 TB
8 KB page size
Table 6. Maximum number of partitions and table space size by DSSIZE, with 8 KB page size
DSSIZE Maximum number of partitions Total table space size
1G 4096 4TB
2G 4096 8TB
4G 4096 16TB
8G 4096 32TB
16G 2048 32TB
32G 1024 32TB
64G 512 32TB
128G 256 32TB
256G 128 32TB
16 KB page size
Table 7. Maximum number of partitions and table space size by DSSIZE, with 16 KB page size
DSSIZE Maximum number of partitions Total table space size
1G 4096 4 TB
2G 4096 8 TB
4G 4096 16 TB
8G 4096 32 TB
16G 4096 64 TB
32G 2048 64 TB
64G 1024 64 TB
128G 512 64 TB
256G 256 64 TB
32 KB page size
Table 8. Maximum number of partitions and table space size by DSSIZE, with 32 KB page size
DSSIZE Maximum number of partitions Total table space size
1G 4096 4 TB
2G 4096 8 TB
4G 4096 16 TB
8G 4096 32 TB
16G 4096 64 TB
32G 4096 128 TB
64G 2048 128 TB
128G 1024 128 TB
256G 512 128 TB
Notes:
  1. For 5-byte non-EA storage, the maximum values for 4 KB page size are DSSIZE 4 G, 4096 partitions, and 16 TB for total table space.
Alternative syntax and synonyms
Start of changeFor compatibility with previous Db2 releases or function levels, the following keywords are supported:End of change
  • You can specify the LOCKPART clause, but it has no effect. Db2 treats all table spaces as if they were defined as LOCKPART YES. LOCKPART YES specifies the use of selective partition locking. When all the conditions for selective partition locking are met, Db2 locks only the partitions that are accessed. When the conditions for selective partition locking are not met, Db2 locks every partition of the table space.

    LOCKSIZE TABLESPACE and LOCKPART YES are mutually exclusive.

  • When creating a partitioned table space, you can specify PART as a synonym for PARTITION.
  • When specifying the logging attributes for a table space, you can specify LOG YES as a synonym for LOGGED, and you can specify LOG NO as a synonym for NOT LOGGED.
  • DSSIZE is the preferred clause for specifying the partition size, however you can specify the CREATE LARGE TABLESPACE when creating partitioned table spaces.
  • Start of changeFL 504 If neither MAXPARTITIONS nor NUMPARTS is specified, you can specify LOCKSIZE TABLE as a synonym for LOCKSIZE TABLESPACE.End of change

Although these keywords are supported as alternatives, they are not the preferred syntax.

Examples

Example 1: Start of changeusing a storage group for a partition-by-growth table spaceEnd of change
Create a table space DSN8S12D in database DSN8D12A. Let Db2 define the data sets, using storage group DSN8G120. The primary space allocation is 52 kilobytes; the secondary, 20 kilobytes. The data sets need not be erased before they are deleted.
Locking on tables in the space is to take place at the page level. Associate the table space with buffer pool BP1. The data sets can be closed when no one is using the table space.
   CREATE TABLESPACE DSN8S12D
     IN DSN8D12A
     USING STOGROUP DSN8G120
       PRIQTY 52
       SECQTY 20
       ERASE NO
     LOCKSIZE PAGE
     BUFFERPOOL BP1
     CLOSE YES;

For the above example, the underlying data sets for the table space will be created immediately, which is the default (DEFINE YES). If you want to defer the creation of the data sets until data is first inserted into the table space, you would specify DEFINE NO instead of accepting the default behavior.

Start of changeFL 504Because the CREATE TABLESPACE statement omits the NUMPARTS and MAXPARTITIONS clauses, the resulting table spaces is a partition-by-growth table space.End of change

Example 2: Start of changepartition options for a partition-by-range table spaceEnd of change
Assume that a large query database application uses a table space to record historical sales data for marketing statistics. Create large table space SALESHX in database DSN8D12A for the application. Create it with 82 partitions, specifying that the data in partitions 80 through 82 is to be compressed.

Let Db2 define the data sets for all the partitions in the table space, using storage group DSN8G120. For each data set, the primary space allocation is 4000 kilobytes, and the secondary space allocation is 130 kilobytes. Except for the data set for partition 82, the data sets do not need to be erased before they are deleted.

Locking on the table is to take place at the page level. There can only be one table in a partitioned table space. Associate the table space with buffer pool BP1. The data sets cannot be closed when no one is using the table space. If there are no CLOSE YES data sets to close, Db2 might close the CLOSE NO data sets when the DSMAX is reached.
   CREATE TABLESPACE SALESHX
     IN DSN8D12A
     USING STOGROUP DSN8G120
       PRIQTY 4000
       SECQTY 130
       ERASE NO
     NUMPARTS 82
     (PARTITION 80
       COMPRESS YES,
      PARTITION 81
       COMPRESS YES,
      PARTITION 82
       COMPRESS YES
       ERASE YES)
     LOCKSIZE PAGE
     BUFFERPOOL BP1
     CLOSE NO;

Start of changeFL 504 Because the CREATE TABLESPACE statement specifies a NUMPARTS clause and no MAXPARTITIONS clause, a partition-by-range table space is created.End of change

Example 3: partition-by-range table space
The following example creates a partition-by-range table space, TS1, in database DSN8D12A using storage group DSN8G120. The table space has 16 pages per segment and has 55 partitions. It specifies LOCKSIZE ANY.
   CREATE TABLESPACE TS1
     IN DSN8D12A
     USING STOGROUP DSN8G120
     NUMPARTS 55
     SEGSIZE 16
     LOCKSIZE ANY;   
Example 4: partition-by-range table space
The following example creates a partition-by-range table space, TS2, in database DSN8D12A using storage group DSN8G120. The table space has 64 pages per segment and has seven defer-defined partitions, where every other partition is compressed.
   CREATE TABLESPACE TS2
     IN DSN8D12A
     USING STOGROUP DSN8G120
     NUMPARTS 7
     (
      PARTITION 1 COMPRESS YES,
      PARTITION 3 COMPRESS YES,
      PARTITION 5 COMPRESS YES,
      PARTITION 7 COMPRESS YES
     )
     SEGSIZE 64
     DEFINE NO;   
Example 5: partition-by-growth table space
The following example creates a partition-by-growth table space that has a maximum size of 2 GB for each partition, four pages per segment with a maximum of 24 partitions for the table space.
CREATE TABLESPACE TS01TS IN TS01DB USING STOGROUP SG1
  DSSIZE 2G
  MAXPARTITIONS 24
  LOCKSIZE ANY
  SEGSIZE 4;