CREATE TABLESPACE

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 implicitly or explicitly specified.

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

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

LOB

Identifies the table space as LOB table space. A LOB table space is used to hold LOB values.

The LOB table space must be in the same database as its associated base table space. Do not specify LOB for a table space in a work file database.

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 serveror 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 for any type of table space
• A work file database for a LOB table space
• 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.

DSNDB04 is the default.

MAXPARTITIONS integer

Specifies that the table space is a partition-by-growth (UTS) table space. The data set for the first partition is allocated unless the DEFINE NO clause is specified for the partition. The data sets for additional partitions are not allocated until they are needed.

integer specifies the maximum number of partitions to which the table space can grow. integer must be in the range of 1 to 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 value for MAXPARTITIONS given the page size or DSSIZE value for the table space

DSSIZE value	4K page size	8K page size	16K page size	32K page size
1G - 4G (1 GB to 4 GB)	4096	4096	4096	4096
8G (8 GB)	2048	4096	4096	4096
16G (16 GB)	1024	2048	4096	4096
32G (32 GB)	512	1024	2048	4096
64G (64 GB)	254	512	1024	2048
128G (128 GB)	128	256	512	1024
256G (256 GB)	64	128	256	512

If MAXPARTITIONS is specified for a table space in a work file database, the table space will be a partition-by-growth (UTS) table space. If the DSSIZE or NUMPARTS clauses are not specified in addition to the MAXPARTITIONS clause for a table space in a work file database, the default values for DSSIZE and NUMPARTS will be used. If MAXPARTITIONS is not specified for a table space in a work file database, the table space will be a segmented table space.

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

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.

Do not specify MEMBER CLUSTER for segmented table spaces.

Do not specify MEMBER CLUSTER for a LOB table space or a table space in a work file database.

MEMBER CLUSTER can be specified with MAXPARTITIONS.

NUMPARTS integer

integer

Specifies the number of partitions. If n is specified for a partition-by-growth (UTS) table space, integer indicates the number of partitions that are initially created unless DEFINE NO is specified. integer must be a value between 1 and 4096 inclusive and must be less than or equal to the value that is specified for the MAXPARTITIONS clause.

The maximum size of each partition depends on the value that is specified for DSSIZE. If DSSIZE is not specified, the number of partitions that are specified determines the maximum size of each partition. For a summary of the values for the maximum size of each partition, see Table 2 and Table 3.

The maximum number of partitions that a table space with absolute numbering can have depends on the page size and DSSIZE. The total table space size depends on how many partitions it has and DSSIZE. Page size affects table size because it affects how many partitions a table space can have. Table 5 shows the maximum number of partitions for DSSIZE and page size and total table space size for both EA-enabled (extended addressability) and non-EA-enabled data sets. (Specifying a DSSIZE greater than 4GB requires EA-enabled data sets.)

For a description of the maximum size of a LOB table space (or the maximum size of LOB data for each column of a base table), see Large object table spaces.

If you omit NUMPARTS and MAXPARTITIONS, the table space is segmented with a SEGSIZE of 4, LOCKSIZE ANY (unless it is explicitly specified), is not partitioned, and initially occupies one data set.

Do not specify NUMPARTS for a LOB table space. Do not specify NUMPARTS for a table space in a work file database unless the MAXPARTITIONS clause is also specified.

PARTITION integer

Specifies to which partition the following using-block or free-block applies. integer can range from 1 to the number of partitions given by NUMPARTS.

You can code the PARTITION clause (and any using-block or free-block that follows it) as many times as needed. If you use the same partition number more than once, only the last specification for that partition is used.

The PARTITION clause must not be specified if the table space is a partition-by-growth (UTS) table space.

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 unless the table space that is being created is a LOB table space. If you do not specify the BUFFERPOOL clause and the table space that is being created is a LOB table space, the default buffer pool is the buffer pool that is specified in the DEFAULT BUFFER POOL FOR USER LOB DATA field on installation panel DSNTIP1.

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

CCSID encoding-scheme

Specifies the encoding scheme for tables stored 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 to be 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 to be encoded using EBCDIC CCSIDs.

UNICODE

Specifies that the data is to be 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.

Do not specify CCSID for a LOB table space or a table space in a work file database. The encoding scheme for a LOB table space is inherited from the base table space. A table space in a work file database does not have an associated encoding scheme because the table space can contain created and declared temporary tables with a mixture of encoding schemes.

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. This is the default unless the table space is in a work file database.

NO

Eligible for closing after all eligible CLOSE YES data sets are closed.

For a table space in a work file database, DB2 uses CLOSE NO regardless of the value specified.

COMPRESS

Specifies whether data compression applies to the rows of the table space or partition.

Do not specify COMPRESS for a LOB table space or a table space in a work file database.

For partitioned table spaces, the COMPRESS attribute for each partition is the value from the first of the following conditions that apply:

• The value specified in the COMPRESS clause in the PARTITION clause for the partition
• The value specified in the COMPRESS clause that is not in any PARTITION clause
• An implicit COMPRESS NO by default.

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

YES

Specifies data compression. The rows are not compressed until the LOAD or REORG utility is run on the table in the table space or partition, or until an insert operation is performed through the INSERT statement or the MERGE statement.

NO

Specifies no data compression for the table space or partition.

DEFINE

Specifies when the underlying data sets for the table space are physically created.

YES

The data sets are created when the table space is created (the CREATE TABLESPACE statement is executed). YES is the default.

If MAXPARTITIONS is also specified, only the first partition is created when the table space is created. Additional partitions are created as needed.

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

Do not specify DEFINE NO for a table space in a work file database. 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.

DSSIZE integer G

Specifies the maximum size for each partition, or for LOB table spaces, each data set. If you specify DSSIZE, you must also specify the NUMPARTS, MAXPARTITIONS, or LOB clause. Do not specify DSSIZE for a table space in a work file database unless MAXPARTITIONS is also specified.

The following values are valid:

1G

1 gigabyte

2G

2 gigabytes

4G

4 gigabytes

8G

8 gigabytes

16G

16 gigabytes

32G

32 gigabytes

64G

64 gigabytes

128G

128 gigabytes

256G

256 gigabytes

To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.

If NUMPARTS is also specified, the maximum size of each partition depends on the value of NUMPARTS, as shown in the following table. Otherwise, the maximum size of each partition is 4G.

Table 2. Maximum partition size depending on value of NUMPARTS

Value of NUMPARTS	Maximum partition size (default for DSSIZE)
1 to 16	4GB (4G)
17 to 32	2GB (2G)
33 to 64	1GB (1G)
65 to 254	4GB (4G)

If NUMPARTS is greater than 254, the maximum partition size (and the default for DSSIZE) depends on the page size of the table space, as shown in the following table. The partition size shown is not necessarily the actual number of bytes used or allocated for any one partition; it is the largest number that can be logically addressed. Each partition occupies one data set.

Table 3. Maximum partition size depending on page size

Page size	Maximum partition size (default for DSSIZE)
4K	4GB (4G)
8K	8GB (8G)
16K	16GB (16G)
32K	32GB (32G)

If DSSIZE is explicitly specified, the maximum number of partitions that can be specified or is the default is limited by the maximum table space size. For example:

• For a partitioned table space with a 4K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 256.
• For a partitioned table space with an 8K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 512.
• For a partitioned table space with a 32K page size, if DSSIZE 128GB is specified, the maximum NUMPARTS value is 1024.

See Table 5 for more information on the relationship between DSSIZE, NUMPARTS, and the table space size.

For LOB table spaces, if DSSIZE is not specified, the default for the maximum size of each data set is 4GB. The maximum number of data sets is 254.

For a description of the maximum size of a LOB table space (or the maximum size of LOB data for each column of a base table), see Large object table spaces.

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 to 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. However, if the table space is segmented and the integer you specify is not less than the segment size, n is one less than the segment size.

If the table space is segmented, the number of pages left free must be less than the SEGSIZE value. If the number of pages to be left free is greater than or equal to the SEGSIZE value, then the number of pages is adjusted downward to one less than the SEGSIZE value.

The default is FREEPAGE 0, leaving no free pages. Do not specify FREEPAGE for a LOB table space or a table space in a work file database.

For XML table spaces, this change has no effect until data in the table space is reorganized.

FREEPAGE does not apply to hash-organized table spaces.

Related information:

• Reserving free space for 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 from 0 to 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. Do not specify PCTFREE for a LOB table space or a table space in a work file database.

For XML table spaces, this change has no effect until data in the table space is reorganized.

PCTFREE does not apply to table spaces for hash-organized tables except when AUTOESTSPACE(YES) is specified in a REORG TABLESPACE invocation.

If the table space is partitioned, 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.

Related information:

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

GBPCACHE

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

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.

Recommendation: For LOB table spaces, use the GBPCACHE CHANGED option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and to 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), 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.

SYSTEM

Indicates that only changed system pages within the LOB table space are to be cached to the group buffer pool. A system page is a space map page or any other page that does not contain actual data values.

SYSTEM applies only to LOB table spaces.

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.

If the table space is partitioned, the value of GBPCACHE for a particular partition is given by the first of these choices that applies:

1. The value of GBPCACHE given in the PARTITION clause for that partition. Do not use more than one gbpcache-block in any PARTITION clause.
2. The value given in a gbpcache-block that is not in any PARTITION clause.
3. The default value CHANGED.

LOCKMAX

Specifies the maximum number of page, row, or LOB locks an application process can hold simultaneously in the table space. If a program requests more than that number, locks are escalated. The page, row, or LOB locks are released and the intent lock on the table space or segmented table is promoted to S or X mode. If you specify LOCKMAX for a table space in a work file database, DB2 ignores the value because these types of locks are not used.

integer

Specifies the number of locks allowed before escalating, in the range 0 to 2 147 483 647.

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

SYSTEM

Indicates that the value of LOCKS PER TABLE(SPACE), on installation panel DSNTIPJ, specifies the maximum number of page, row, or LOB locks a program can hold simultaneously in the table or table space.

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

LOCKSIZE	Resultant LOCKMAX
ANY	SYSTEM
TABLESPACE, TABLE, PAGE, ROW, or LOB	0

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

For an application that uses Sysplex query parallelism, a lock count is maintained on each member.

LOCKSIZE

Specifies the size of locks used within the table space and, in some cases, also the threshold at which lock escalation occurs. Do not use this clause for a table space in a work file database.

ANY

Specifies that DB2 can use any lock size.

In most cases, DB2 uses LOCKSIZE PAGE LOCKMAX SYSTEM for non-LOB table spaces and LOCKSIZE LOB LOCKMAX SYSTEM for LOB table spaces. However, when the number of locks acquired for the table space exceeds the maximum number of locks allowed for a table space (the NUMLKTS subsystem parameter ), the page or LOB locks are released and locking is set at the next higher level.

If the table space is segmented, the next higher level is the table. If the table space is segmented and not partitioned, the next higher level is the table. If the table space is partitioned, the next higher level is the partition.

If the table space is implicitly created, DB2 uses LOCKSIZE ROW.

TABLESPACE

Specifies table space locks.

TABLE

Specifies table locks. Use TABLE only for a segmented (non-UTS) table space. Do not use TABLE for a universal table space.

PAGE

Specifies page locks. Do not use PAGE for a LOB table space.

ROW

Specifies row locks. Do not use ROW for a LOB table space.

LOB

Specifies LOB locks. Use LOB only for a LOB table space.

LOGGED or NOT LOGGED

Specifies whether changes that are made to the data in the specified table space are recorded in the log.

LOGGED

Specifies that changes that are made to the data in the specified table space are recorded in the log. This applies to all tables that are created in the specified table space and to all indexes of those tables. 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 cannot be specified for table spaces in DSNDB06 (the DB2 catalog) or in a work file database.

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. This parameter applies to all tables that are created in the specified table space and to all indexes of those tables. XML table spaces and their indexes inherit the logging attribute from the associated base table space. Auxiliary indexes inherit the logging attribute from the associated base table space.

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.

NOT LOGGED cannot be specified for table spaces in the following databases:

• DSNDB06 (the DB2 catalog)
• a work file database

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 (which do not apply for a table space in the work file database), 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.

Do not use MAXROWS for a LOB table space or a table space in a work file database.

SEGSIZE integer

Specifies the type of table space that will be created depending on the values of the SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses. integer specifies the number of pages that are to be assigned to each segment of the table space. integer must be a multiple of 4 between 0 and 64 (inclusive). integer cannot be 0 for an XML table space. Do not specify SEGSIZE for a LOB table space.

If SEGSIZE is not specified and only the NUMPARTS clause is specified, either a partitioned (non-UTS) table space or a partition-by-range (UTS) table space is created depending on the value of the DPSEGSZ subsystem parameter:

• If the value of DPSEGSZ is greater than 0 (zero), the table space will be a partition-by-range (UTS) table space with a SEGSIZE value that is equal to the value of DPSEGSZ.
• If the value of DPSEGSZ is equal to 0 (zero), the table space will be a partitioned (non-UTS) table space.

If SEGSIZE is not specified and only the MAXPARTITIONS clause is specified, a partition-by-growth (UTS) table space is created with a SEGSIZE that depends on the value of the DPSEGSZ subsystem parameter:

• If the value of DPSEGSZ is greater than 0 (zero), the table space will have a SEGSIZE value that is equal to the value of DPSEGSZ.
• If the value of DPSEGSZ is equal to 0 (zero), the table space will have a SEGSIZE value of 32.

The DPSEGSZ subsystem parameter has no effect on segmented table spaces.

The following table lists the type of table space depending on the specification of the SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses:

Table 4. Type of table space depending on value of SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses

SEGSIZE clause	MAXPARTITIONS clause	NUMPARTS clause	Type of table space
specified	specified	not specified	partition-by-growth (UTS) table space
specified	not specified	not specified	segmented (non-UTS) table space
specified	not specified	specified	partition-by-range (UTS) table space
specified with a value of 0	not specified	specified	partitioned (non-UTS) table space
not specified	specified	specified	partition-by-growth (UTS) table space SEGSIZE = 32 if DPSEGSZ = 0 SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified	specified	not specified	partition-by-growth (UTS) table space SEGSIZE = 32 if DPSEGSZ = 0 SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified	not specified	specified	One of the following: partitioned (non-UTS) table space if DPSEGSZ is specified with a value of 0. partition-by-range (UTS) table space with a SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified	not specified	not specified	segmented (non-UTS) table space with an implicit specification of SEGSIZE 4

TRACKMOD

Specifies whether DB2 tracks modified pages in the space map pages of the table space or partition. Do not specify TRACKMOD for a LOB table space. Do not specify TRACKMOD for a table space in a work file database.

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.

If the table space is partitioned, the value of TRACKMOD for a particular partition is given by the first of these choices that applies:

1. The value of TRACKMOD given in the PARTITION clause for that partition.
2. The value given in a trackmod-block that is not in any PARTITION clause.
3. The default value YES.

If TRACKMOD is not specified, the default value as specified in the subsystem parameter IMPTKMOD is used.

The components of the USING block are discussed below, first for nonpartitioned table spaces and then for partitioned table spaces. If you omit USING, the default storage group of the database must exist.

USING block for nonpartitioned table spaces:

For nonpartitioned table spaces, the USING clause indicates whether the data set for the table space is defined by you or by DB2. If DB2 is to define the data set, the clause also gives space allocation parameters and an erase rule.

If you omit USING, DB2 defines the data sets using the default storage group of the database and the defaults for PRIQTY, SECQTY, and ERASE.

VCAT catalog-name

Specifies that the first data set for the table space is managed by the user, and following data sets, if needed, are also managed by the user.

The data sets defined for the table space are linear VSAM data sets cataloged in an integrated catalog facility catalog identified by catalog-name. An alias¹ must be used if the catalog name is longer than eight characters.

Conventions for table space data set names are given in DB2 Administration Guide. catalog-name is the first qualifier for each 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.

VCAT must not be specified if MAXPARTITIONS is also specified.

STOGROUP stogroup-name

Specifies that DB2 will define and manage the data sets for the table space. Each data set will be defined on a volume of the identified storage group. The values specified (or the defaults) for PRIQTY and SECQTY determine the primary and secondary allocations for the data set. The storage group supplies the name of a volume for the data set and the first-level qualifier for the data set name. The first-level qualifier is also the name of, or an alias¹ for, the integrated catalog facility catalog on which the data set is to be cataloged. The naming conventions for the data set are the same as if the data set is managed by the user. As was mentioned above for VCAT, the first-level qualifier could cause naming conflicts if the local DB2 can share integrated catalog facility catalogs with other DB2 subsystems.

stogroup-name must identify a storage group that exists at the current server. SYSADM or SYSCTRL authority, or the USE privilege on the storage group, is required.

The description of the storage group must include at least one volume serial number, or it must indicate that the choice of volumes is left to Storage Management Subsystem (SMS). If volume serial numbers appear in the description, each must identify a volume that is accessible to z/OS® for dynamic allocation of the data set, and all identified volumes must be of the same device type.

The integrated catalog facility catalog used for the storage group must not contain an entry for the first data set of the table space. If the integrated catalog facility catalog is password protected, the description of the storage group must include a valid password.

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 non-LOB table spaces, the exceptions are:

• For 4KB page sizes, if integer is greater than 0 and less than 12, n is 12.
• For 8KB page sizes, if integer is greater than 0 and less than 24, n is 24.
• For 16KB page sizes, if integer is greater than 0 and less than 48, n is 48.
• For 32KB page sizes, if integer is greater than 0 and less than 96, n is 96.
• For any page size, if integer is greater than 67108864, n is 67108864.

For LOB table spaces, the exceptions are:

• 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 or partition 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.

USING block for partitioned table spaces:

If the table space is partitioned, there is a USING clause for each partition; either one you give explicitly or one provided by default. Except as explained below, the meaning of the clause and the rules that apply to it are the same as for a nonpartitioned table space.

The USING clause for a particular partition is the first of these choices that can be found:

• A USING clause in the PARTITION clause for the partition
• A USING clause that is not in any PARTITION clause
• An implicit USING STOGROUP clause that identifies the default storage group of the database and accepts the defaults for PRIQTY, SECQTY, and ERASE

VCAT catalog-name

Indicates that the data set for the partition is managed by the user using the naming conventions set forth in DB2 Administration Guide. 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.

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.

Notes

Segmented table spaces:

If neither LOB, NUMPARTS, nor SEGSIZE are specified, the table space that is created is a segmented table space.

Related links: Types of DB2 table spaces

Universal (UTS) table spaces:

If NUMPARTS and SEGSIZE are specified, the table space that is created is a partition-by-range (UTS) table space. If MAXPARTITIONS or MAXPARTITIONS and SEGSIZE are specified, the table space that is created is a partition-by-growth universal table space. If a partition-by-range (UTS) table space contains an XML column, the corresponding XML table space will also be partition-by-range (UTS) table space.

Related links: Universal (UTS) table spaces

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.

Related links: Accessing currently committed data to avoid lock contention

Table spaces in a work file database:

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 DEFINE NO DSSIZE1 FREEPAGE GBPCACHE	LARGE LOB LOCKPART LOCKSIZE LOGGED MAXROWS	MEMBER CLUSTER NOT LOGGED NUMPARTS1 PCTFREE TRACKMOD
  1: DSSIZE and NUMPARTS must not be specified for a table space in a work file database unless MAXPARTITIONS is also specified.

  

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.

When you create table spaces in the work file database, it is recommended that you give each table space the same segment size, with the same minimum primary and secondary space allocation values for the data sets, to maximize the use of all the table spaces for all objects in all application processes.

Creating LOB table spaces:

When you create a LOB table space, the following clauses are not allowed:

CCSID COMPRESS FREEPAGE LOCKSIZE TABLE

LOCKSIZE PAGE LOCKSIZE ROW MAXPARTITIONS NUMPARTS

PCTFREE SEGSIZE TRACKMOD

Recommended GBPCACHE setting for LOB table spaces:

For LOB table spaces, use the GBPCACHE CHANGED option instead of the GBPCACHE SYSTEM option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and the group buffer pool.

Altering the logging attribute of a table space:

See ALTER TABLESPACE for information about altering the logging attributes of a table space.

Table space row formats:

Depending on the value of the RRF subsystem parameter, newly created table spaces will be in either re-ordered row format or basic row format. When the value of the RRF parameter is ENABLE, table spaces will be created in re-ordered row format. When the value of the RRF parameter is DISABLE, newly created table spaces will be created in basic row format. This includes universal table spaces, except for XML table spaces and hash table spaces, which are always created in re-ordered row format, regardless of the value of the RRF parameter.

Making a partitioned table space larger:

Depending on the needs of your application, you might need to increase the size of a partitioned table space to hold more data by either adding more partitions or by increasing the size of the existing partitions:

• To add more partitions, use the ALTER TABLE statement with the ADD PARTITION clause.
• To increase the size of the partitions, use the following steps:
  • If the table space is a partition-by-range (UTS) table space, specify a larger DSSIZE using the ALTER TABLESPACE statement if the DSSIZE of the table space is not already at the maximum.
  • If the table space is not a partition-by-range (UTS) table space:
    1. Convert the table space to a partition-by-range (UTS) table space by specifying a SEGSIZE value and a NUMPARTS value using the ALTER TABLESPACE statement
    2. Run the REORG utility with the SHRLEVEL CHANGE or SHRLEVEL REFERENCE option on the table space to apply the new SEGSIZE value
    3. Specify a larger DSSIZE using the ALTER TABLESPACE statement
  Related links:

  • ALTER TABLESPACE
  • REORG TABLESPACE
  

Redistributing data between existing partitions:

If you need to redistribute the data between the existing partitions to make better use of the space within the existing table, you can use either of these two methods:

• Use the ALTER TABLE statement with the ALTER PARTITION clause. You can alter the partitions to specify new partition boundaries to explicitly specify how to redistribute the data. Any affected partitions are set to REORG-pending status.
• Use the REORG utility with the REBALANCE keyword. REBALANCE specifies that the data is evenly redistributed across the partitions that are reorganized. SeeREORG TABLESPACE for information about using the REORG utility.

Rules for primary and secondary space allocation:

You can specify the primary and secondary space allocation for table spaces and indexes or allow DB2 to 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.

In the following rules that describe how allocation works, these terms are used:

PRIQTY, SECQTY

The keywords for CREATE TABLESPACE, ALTER TABLESPACE, CREATE INDEX, and ALTER INDEX.

specified-priqty

The user-specified value for PRIQTY.

specified-secqty

The user-specified value for SECQTY.

actual-priqty

The actual primary space allocation, in kilobytes.

actual-priqty-cylinders

The actual primary space allocation, in cylinders.

actual-secqty

The actual secondary space allocation, in kilobytes.

actual-secqty-cylinders

The actual secondary space allocation, in cylinders.

calculated-extent-cylinders

A value that is calculated by DB2 using a sliding scale. A sliding scale means that the first secondary extent allocations are smaller than later secondary allocations. For example, Figure 1 shows the sliding scale of secondary extent allocations that DB2 uses for a 64-GB data set. The size of each secondary extent is larger for each secondary extent that is allocated up to the 127th extent. For the 127th secondary extent and any subsequent extents, the secondary size allocation is 559 cylinders.

The rules are:

• Rule 1 (for primary space allocation)

  If PRIQTY is specified and specified-priqty is not equal to -1, actual-priqty is at least specified-priqty KB.

  If PRIQTY is not specified or specified-priqty is equal to -1, actual-priqty is determined as follows:

  • For a table space, if the TSQTY subsystem parameter value is specified and is greater than 0, actual-priqty is at least the value of TSQTY.

    If the TSQTY subsystem parameter is not specified or is 0, actual-priqty is one cylinder for a non-LOB table space. actual-priqty is 10 cylinders for a LOB table space.

  • For an index, if the IXQTY subsystem parameter value is specified and is greater than 0, actual-priqty is at least the value of IXQTY.

    If the IXQTY subsystem parameter is not specified or is 0, actual-priqty is one cylinder.

• Rule 2 (for secondary space allocation)
  If SECQTY is not specified, the following formulas determine actual-secqty:

  • If the maximum size of a data set in the table space or index is less than 32 GB, the formula is:

    actual-secqty-cylinders=
    MAX(0.1*actual-priqty-cylinders, MIN(calculated-extent-cylinders, 127))

  • If the maximum size of a data set in the table space or index is 32 GB or greater, the formula is:

    actual-secqty-cylinders=
    MAX(0.1*actual-priqty-cylinders, MIN(calculated-extent-cylinders, 559))

• Rule 3 (for secondary space allocation)

  If SECQTY is 0, actual-secqty is 0.

• Rule 4 (for secondary space allocation)

  This is the only rule that depends on the value of subsystem parameter MGEXTSZ (field OPTIMIZE EXTENT on installation panel DSNTIP7).

  If MGEXTSZ is YES:

  • If SECQTY is specified and specified-secqty is not equal to -1 or 0, the following formulas determine actual-secqty:
    • If the maximum size of a data set in the table space or index is less 32 GB, the formula is:

      actual-secqty-cylinders=
      MAX(MIN(calculated-extent-cylinders, 127),specified-secqty-cylinders)

    • If the maximum size of a data set in the table space or index is 32 GB or greater, the formula is:

      actual-secqty-cylinders=
      MAX(MIN(calculated-extent-cylinders, 559),specified-secqty-cylinders)

  If MGEXTSZ is NO:

  • For a table space, if SECQTY is n, the secondary space allocation is at least n kilobytes, with the following exceptions:
    • If SECQTY is greater than 4194304, n is 4194304 kilobytes.
    • For LOB table spaces:
      • 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 4194304, n is 4194304.
  • For an index, if SECQTY is integer, the secondary space allocation is at least n kilobytes, where n is:

    12

    If SECQTY and PRIQTY are omitted

    4194304

    If integer is greater than 4194304

    integer

    If integer is not greater than 4194304

• Rule 5 (for secondary space allocation): When a table space requires a new piece, the primary allocation quantity of the new piece is determined as follows:
  • If the value of subsystem parameter MGEXTSZ is NO, the primary quantity from rule 1 is used.
  • Otherwise, the maximum of the following values is used:
    • The quantity that is calculated through sliding scale methodology
    • The primary quantity from rule 1
    • The specified SECQTY value

Determining the maximum number of partitions that a table space with absolute numbering can have

Table 5. Table space size given page size and partitions (for table spaces with absolute numbering)

Type of RID	Page size	DSSIZE	Maximum number of partitions	Total table space size
5-byte EA	4KB	1GB	4096	4TB
5-byte EA	4KB	2GB	4096	8TB
5-byte EA	4KB	4GB	4096	16TB
5-byte EA	4KB	8GB	2048	16TB
5-byte EA	4KB	16GB	1024	16TB
5-byte EA	4KB	32GB	512	16TB
5-byte EA	4KB	64GB	256	16TB
5-byte EA	4KB	128GB	128	16TB
5-byte EA	4KB	256GB	64	16TB
5-byte EA	8KB	1GB	4096	4TB
5-byte EA	8KB	2GB	4096	8TB
5-byte EA	8KB	4GB	4096	16TB
5-byte EA	8KB	8GB	4096	32TB
5-byte EA	8KB	16GB	2048	32TB
5-byte EA	8KB	32GB	1024	32TB
5-byte EA	8KB	64GB	512	32TB
5-byte EA	8KB	128GB	256	32TB
5-byte EA	8KB	256GB	128	32TB
5-byte EA	16KB	1GB	4096	4TB
5-byte EA	16KB	2GB	4096	8TB
5-byte EA	16KB	4GB	4096	16TB
5-byte EA	16KB	8GB	4096	32TB
5-byte EA	16KB	16GB	4096	64TB
5-byte EA	16KB	32GB	2048	64TB
5-byte EA	16KB	64GB	1024	64TB
5-byte EA	16KB	128GB	512	64TB
5-byte EA	16KB	256GB	256	64TB
5-byte EA	32KB	1GB	4096	4TB
5-byte EA	32KB	2GB	4096	8TB
5-byte EA	32KB	4GB	4096	16TB
5-byte EA	32KB	8GB	4096	32TB
5-byte EA	32KB	16GB	4096	64TB
5-byte EA	32KB	32GB	4096	128TB
5-byte EA	32KB	64GB	2048	128TB
5-byte EA	32KB	128GB	1024	128TB
5-byte EA	32KB	256GB	512	128TB
5-byte (non-EA) LARGE	4KB	(4GB)	4096	16TB

Alternative syntax and synonyms:

For compatibility with previous releases of DB2, the following keywords are supported:

• You can specify the LOCKPART clause, but it has no effect. Starting with Version 8, 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.
• You can specify the LARGE clause when creating partitioned table spaces, but DSSIZE is the preferred clause to use when specifying the partition size.

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