Creating table spaces explicitly

Db2 can create table spaces for you. However, you might also create table spaces explicitly by issuing CREATE TABLESPACE statements if you manage your own data sets, among other reasons.

Before you begin

For information about how Db2 can create table spaces for you, see Implicitly defined table spaces.

About this task

Begin general-use programming interface information.

Start of changeFL 504 You can create partition-by-range or partition-by-growth table spaces. For base tables, table spaces of other types are deprecated, creating them is not supported, and support for such existing tables might be removed in the future. For more information about the different types, see Table space types and characteristics in Db2 for z/OS.End of change

Tip: You can alter table spaces after they are created, but the application of some statements, such as ALTER TABLESPACE with MAXPARTITIONS, prevent access to the database until alterations complete. Consider future growth when you define new table spaces.

Procedure

To explicitly create a table space:

Issue a CREATE TABLESPACE statement and specify the type of table space to create and other attributes.
  1. Specify the table space type to create.
    Start of changeFor instructions for creating the supported types, see Creating partition-by-range table spaces and Creating partition-by-growth table spaces. End of change
    Start of changeFL 504The following table shows the resulting table space types.
    Table 1. 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 n2.a.i
    • MAXPARTITIONS
    Partition-by-range NUMPARTS only NUMPARTS and SEGSIZE n2.a.i
    Segmented (non-UTS) Not supported2.a.ii One of the following combinations:
    • SEGSIZE n2.a.i
    • Omit MAXPARTITIONS, NUMPARTS, and SEGSIZE
    Partitioned (non-UTS) Not supported2.a.ii 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. 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).
    End of change
  2. Specify other attributes for the table space.
    The following list introduces some CREATE TABLESPACE statement clauses that define the attributes of a table space. For the complete list, see CREATE TABLESPACE.
    table-space-name
    The table space name is an identifier of up to 8 characters. You can qualify a table space name with a database name. Consider the following facts about naming guidelines for table spaces:
    • If you do not qualify an explicit table space with a database name, the default database name is DSNDB04.
    • If you do not explicitly specify a table space, Db2 implicitly creates the table space with a derived name. The name is derived based on the name of the table that is being created.
    • Db2 either implicitly creates a new database for the table space, or uses an existing implicitly created database.
    BUFFERPOOL bpname
    Identifies the buffer pool that this table space is to use and determines the page size of the table space. The buffer pool is a portion of memory in which Db2 temporarily stores data for retrieval. For more information, see Tuning database buffer pools.
    Start of changePAGENUMEnd of change
    Start of change

    Specifies the type of page numbering used for partition-by-range (PBR) table spaces. PBR spaces with relative page numbering support larger partition sizes than PBR table spaces with absolute page numbering, and greater flexibility in growing your partitions. Instead of restricting partition growth to gigabytes in powers of two, PBR table spaces with relative page numbering support the growth of partitions by any number of gigabytes. DSSIZE can also be increased for individual partitions as an immediate ALTER, without requiring a REORG.

    The PAGESET_PAGENUM subsystem parameter specifies the default value. See PAGE SET PAGE NUMBERING field (PAGESET_PAGENUM subsystem parameter).

    End of change
    MAXPARTITIONS
    Specifies the maximum number of partitions for a partition-by-growth table space. Within this clause, you can specify the NUMPARTS clause to specify the number of partitions that you want to create initially.
    NUMPARTS
    Specifies the number of partitions to initially create for the table space.
    COMPRESS
    Specifies whether to compress the data. You can compress data in a table space to store more data on each data page. For details, see Compressing your data.
    FREEPAGE integer
    Specifies how often Db2 is to leave a page of free space when the table space or partition is loaded or reorganized. You specify that Db2 is to set aside one free page for every integer number of pages. Using free pages can improve performance for applications that perform high-volume inserts or that update variable-length columns. For details, see Reserving free space in table spaces.
    PCTFREE integer
    Specifies the percentage of each page that Db2 leaves as free space when the table is loaded or reorganized. Specifying PCTFREE can improve performance for applications that use high-volume inserts or that update variable-length columns. For details, see Reserving free space in table spaces.
    LOCKSIZE
    Specifies the size of locks that Db2 is to use within the table space. Db2 uses locks to protect data integrity. Use of locks results in some processing costs, so choose the lock size carefully. For details, see Specifying the size of locks for a table space.
    MAXROWS
    Specifies the maximum number of rows that Db2 places on each data page. The integer can range from 1 through 255. 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.
    MEMBER CLUSTER
    Specifies that data that is inserted by an INSERT operation is not clustered by the implicit clustering index (the first index), or the explicit clustering index. Db2 locates the data in the table space based on available space. You can use the MEMBER CLUSTER keyword on partition-by-range table spaces and partition-by-growth table spaces. For details, see Member affinity clustering.
    DSSIZE
    Specifies the maximum size in GB for each partition. The size of the table space depends on how many partitions are in the table space and the size of each partition. For a partition-by-growth table space, the maximum number of partitions depends on the value that is specified for the MAXPARTITIONS clause.
    Start of changeSEGSIZEEnd of change
    Start of changeFL 504 An integer value specifies the number of pages that are to be assigned to each segment of the table space. integer must be a multiple of 4 from 4 to 64 (inclusive). Do not specify SEGSIZE for a LOB table space.

    If SEGSIZE is not specified, the value of SEGSIZE is determined as follows:

    • If the DPSEGSZ subsystem parameter value is greater than 0, the SEGSIZE value for the table space is equal to the DPSEGSZ value.
    • If the DPSEGSZ value is 0, the SEGSIZE for the tables space is 32.
    • If the table space is created in work file database, the DPSEGSZ value is not applicable and the SEGSIZE value for the table space is 16.
    End of change

Examples

The following examples illustrate how to use SQL statements to create different types of table spaces.

Creating partition-by-growth table spaces
The following example CREATE TABLE statement implicitly creates by a partition-by-growth table space. 
CREATE TABLE TEST02TB( 
C1 SMALLINT, 
C2 DECIMAL(9,2), 
C3 CHAR(4)) 
PARTITION BY SIZE EVERY 4G 
IN TEST02DB; 
COMMIT;

The following SQL statement creates a partition-by-growth table space that has a maximum size of 2 GB for each partition, 4 pages per segment, with a maximum of 24 partitions for table space. 

CREATE TABLESPACE TEST01TS IN TEST01DB USING STOGROUP SG1 
DSSIZE 2G 
MAXPARTITIONS 24 
LOCKSIZE ANY 
SEGSIZE 4; 
COMMIT;
Start of changeCreating partition-by-range table spaces with relative page numberingEnd of change
Start of change

The following example SQL statement creates a partition-by-range table space with relative page numbering. The maximum partition size is 64G for partition 1, and 4G for each the other seven partitions .

CREATE TABLESPACE TS1 
  IN DB1
  USING STOGROUP SG1
    NUMPARTS 7 
     (PARTITION 1 DSSIZE 64G
     )
PAGENUM RELATIVE;
End of change
Creating partition-by-range table spaces
The following example SQL statement defines a partition-by-range table space with 16 pages per segment and 55 partitions. This universal table space uses a storage group SG1 and has LOCKSIZE ANY.
CREATE TABLESPACE TS1 IN DB1 USING STOGROUP SG1
NUMPARTS 55 SEGSIZE 16
LOCKSIZE ANY;
The following example SQL statement defines a partition-by-range table space with 64 pages per segment and 7 defer-defined partitions. This table space uses a storage group SG1 and compresses every odd-numbered partition. 
CREATE TABLESPACE TS1 IN DB1 USING STOGROUP SG1
NUMPARTS 7
(
PARTITION 1 COMPRESS YES,
PARTITION 3 COMPRESS YES,
PARTITION 5 COMPRESS YES,
PARTITION 7 COMPRESS YES
)
SEGSIZE 64
DEFINE NO;

What to do next

Generally, when you use the CREATE TABLESPACE statement with the USING STOGROUP clause, Db2 allocates data sets for the table space. However, if you also specify the DEFINE NO clause, you can defer the allocation of data sets until data is inserted or loaded into a table in the table space.

End general-use programming interface information.