CREATE TABLESPACE

Invocation for CREATE TABLESPACE

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 for CREATE TABLESPACE

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
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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.

Description for CREATE TABLESPACE

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.

FL 504 If database-name identifies a work file database, the table space must be a partition-by-growth table space.

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 can specify neither 8KB buffer pools nor 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. FL 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. 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–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–256 G (1, 2, 4, 8, 16, 32, 64, 128, or 256).

integer can be separated from G by 0 or more spaces.

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

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

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

NUMPARTS integer

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

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

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

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 1–1024G. The default value is 4G.

integer can be separated from G by 0 or more spaces.

PAGENUM

Identifies 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 for PAGESET_PAGENUM is ABSOLUTE. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).

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.

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

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 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–1024 G, and the default value is 4 G.

integer can be separated from G by 0 or more spaces.

If absolute page numbers are used, the DSSIZE value must be a power-of-two integer in the range 1–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.

integer can be separated from G by 0 or more spaces.

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

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.

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.

An EBCDIC table in the table space can include Unicode columns.

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.

YES

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

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

The default is FREEPAGE 0, leaving no free pages.

For more information, see:

• Reserving free space in table spaces
• Reserving free spaces for indexes

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

For more information, see:

• Reserving free space in table spaces
• Reserving free spaces for indexes

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.

INSERT ALGORITHM level

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

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

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

The following table summarizes the results of specifying a LOCKSIZE value while omitting LOCKMAX.

LOCKSIZE	Resultant LOCKMAX
ANY	SYSTEM
TABLESPACE, PAGE, ROW	0

If the lock size is TABLESPACE, LOCKMAX must be omitted, or its operand must be 0.

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.

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

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

Specifies 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. If the USING clause for the table space is omitted, the default storage group for database must exist.

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.

USING VCAT must not be specified for a partition-by-growth table space.

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 for CREATE TABLESPACE

Table space types

FL 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: 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). FL 504 Non-UTS table spaces for base tables are deprecated. CREATE TABLESPACE statements that run at application compatibility level V12R1M504 or higher always create a partition-by-growth or partition-by-range table space, and CREATE TABLE statements that specify a non-UTS table space (including existing multi-table segmented table spaces) return an error. However, you can use a lower application compatibility level to create table spaces of the deprecated types if needed, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).

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.

FL 504 XML table spaces

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

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.

For more information, see Accessing currently committed data to avoid lock contention.

Table spaces in a work file database

FL 504A table space in a work file database is always a partition-by-growth table space.

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
  • PAGENUM
  • 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 table spaces in the work file database. At least one table space with a 32 KB 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

All newly created table spaces use re-ordered row format.

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

For partition-by-range table spaces created with relative page numbering, the maximum number of partitions is 4096. 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.¹

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

For compatibility with previous Db2 releases or function levels, the following keywords are supported:

• 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.
• FL 504 If neither MAXPARTITIONS nor NUMPARTS is specified, you can specify LOCKSIZE TABLE as a synonym for LOCKSIZE TABLESPACE.

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