Creating non-UTS table spaces (deprecated)

In Db2® 13, CREATE TABLESPACE and CREATE TABLE statements that run at application compatibility level V12R1M504 or higher cannot create or use deprecated non-UTS table spaces. However, you can still create or use the deprecated table space types by running the CREATE statements at a lower application compatibility level.

Before you begin

For best results, use the non-deprecated table space types for all new table spaces whenever possible, and develop plans for converting existing table spaces to the non-deprecated UTS types. Although the deprecated types remain supported in Db2 13, many of the new capabilities and enhancements introduced in DB2® 10 and later are supported only for UTS. Also, the non-deprecated types are likely to be unsupported eventually. For more information, see Converting deprecated table spaces to the UTS types.

Procedure

  1. Change the application compatibility level of the CREATE statement to V12R1M503 or lower, by using one of the following approaches:
  2. Use one of the following specifications in the CREATE statement.
    • To create a segmented (non-UTS) table space, issue a CREATE TABLESPACE statement and specify a non-zero SEGSIZE value. Do not specify NUMPARTS or MAXPARTITIONS. For the syntax and descriptions, see segmented-non-uts-specification.
    • To create a partitioned (non-UTS) table space, issue a CREATE TABLESPACE statement and specify NUMPARTS, omit MAXPARTITIONS, and specify SEGSIZE 0. For the syntax and descriptions, see paritioned-non-UTS-specification.
    • To create a new table in an existing segmented table space, specify the existing table space in the IN clause of the CREATE TABLE statement. For the syntax and descriptions, see CREATE TABLE statement.

Syntax and descriptions for creating non-UTS table spaces (deprecated)

The following table summarize the options that control the resulting table space type when you issue a CREATE TABLESPACE statement at application compatibility level V12R1M503 or lower.

Table 1. Table space types and related clauses below application compatibility level V12R1M504
Table space type to create Clauses to specify
Partition-by-growth Any of the following combinations:
  • MAXPARTITIONS and NUMPARTS
  • MAXPARTITIONS and SEGSIZE n1
  • MAXPARTITIONS
Partition-by-range NUMPARTS and SEGSIZE n1
Segmented (non-UTS) 2 One of the following combinations:
  • SEGSIZE n1
  • Omit MAXPARTITIONS, NUMPARTS, and SEGSIZE
Partitioned (non-UTS) 2 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. Non-UTS table spaces for base tables are deprecated and will be unsupported in the future.

segmented-non-uts-specification

At application compatibility level V12R1M503 or lower, segmented-non-UTS-specification has the following syntax and descriptions.

Read syntax diagramSkip visual syntax diagramSEGSIZE 4SEGSIZEinteger
segmented-non-UTS-specification (deprecated)
If MAXPARTITIONS and NUMPARTS are both omitted, a segmented (non-UTS) table space is created. It is not partitioned, and initially occupies one data set.
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.

Because segmented (non-UTS) table spaces are not partitioned, the description of the using-block specification also differs from the description for UTS.

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 are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions in SQL.

More than one Db2 subsystem can share the integrated catalog facility catalogs with the current server. To avoid the chance of those subsystems attempting to assign the same name to different data sets, specify a catalog-name value 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 alias1 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 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.
  • FL 507 For any page size, if integer is greater than 1073741824, n is 1073741824.

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 Primary space allocation for Db2 table spaces and indexes.

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.

FL 507 For a large PRIQTY, the data sets for the table space are recommended to be associated with a DFSMS data class that is specified with multivolume data sets. This allows Db2 to create the data set spanning multiple volumes as needed.

FL 507 To define multiple volumes for a data class, VOLUME COUNT or DYNAMIC VOLUME COUNT may need to be modified to specify a value larger than 1. For more information, see Defining volume and data set attributes for data classes.

FL 507 In addition, the SPACE RELIEF CONSTRAINT may also need to be modified. For more information, see Specifying attributes to handle space constraints during allocation.

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 Primary space allocation for Db2 table spaces and indexes.

FL 507 If integer is greater than 209715200, n is 209715200.

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.

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

paritioned-non-UTS-specification

At application compatibility level V12R1M503 or lower, partitioned-non-UTS-specification has the following syntax and descriptions.

Read syntax diagramSkip visual syntax diagram NUMPARTS integer 1(,PARTITIONintegerusing-blockfree-blockgbpcache-blockCOMPRESS NOCOMPRESS YESTRACKMODimptkmod-parameterTRACKMOD YESTRACKMOD NO)DSSIZEintegerG2SEGSIZE 03
Notes:
  • 1 Group multiple PARTITION clauses. Other clauses must not be specified more than one time.
  • 2 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 the tables in "Maximum number of partitions and table space size" in CREATE TABLESPACE statement.
  • 3 SEGSIZE 0 must be specified unless the DPSEGSZ subsystem parameter value is 0. For more information, see DPSEGSZ subsystem parameter.
partitioned-non-UTS-specification (deprecated)
Specifies a NUMPARTS value and SEGSIZE 0 to create a partitioned (non-UTS) table space.
NUMPARTS integer

The integer value specifies the number of partition 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.

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. See the tables in "Maximum number of partitions and table space size" in CREATE TABLESPACE statement.

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.

SEGSIZE 0
Specifies that the table space is a partitioned (non-UTS) table space, which does not use segmented organization. SEGSIZE 0 must be specified unless the DPSEGSZ subsystem parameter is 0. For more information, see DPSEGSZ subsystem parameter.
DSSSIZE integerG
When DSSIZE is specified for a partitioned (non-UTS) 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). The default value depends on the NUMPARTS value and the buffer pool page size, as shown in the following table.
Table 2. DSSIZE defaults for paritioned (non-UTS) table spaces
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. See the tables in "Maximum number of partitions and table space size" in CREATE TABLESPACE statement.

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.

1 The alias of an integrated catalog facility catalog.