DB2 - SQL Create Tablespace Statement

Table space creation syntax is as follows:

CREATE TABLESPACE tablespace-name
IN database-name
          using-block
          free-block
          DEFINE YES/NO
          LOGGED | NOT LOGGED
          TRACKMOD YES/NO
          DSSIZE integer
          MAXPARTITIONS integer
          MEMBER CLUSTER
          NUMPARTS integer
          PARTITION integer
          using-block
          free-block
          BUFFERPOOL bp-name
          CCSID ASCII/EBCDIC/UNICODE
          CLOSE YES/NO
          COMPRESS YES/NO
          LOCKMAX integer
          LOCKSIZE ANY/TABLESPACE/TABLE/PAGE/ROW
          MAXROWS integer
          SEGSIZE integer
using-block:
            USING
            VCAT catalog-name
            STOGROUP stogroup-name
            PRIQTY integer
            SECQTY integer
            ERASE YES/NO
            free-block:
            FREEPAGE integer
            PCTFREE integer

Syntax Explanation:

database-name: If you omit database-name, then the default DB, DSNDB04 is used. But it is advisable to specify the database.

USING:

VCAT:

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.

STOGROUP:

Specifies the storage group in which the datasets for the table space will be defined and managed by DB2.

PRIQTY: Specifies the minimum primary space allocation for a DB2-managed data set.
SEQQTY: Specifies the minimum secondary space allocation for a DB2-managed data set.
ERASE: Indicates whether the DB2-managed data sets for the table space are to be erased when the corresponding table space is deleted.

NO: It does not erase the data sets. This is the default.

YES: Erases the data sets.

FREEPAGE:

Specifies how often to leave a page of free space when the table space 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.

PCTFREE:

Indicates what percentage of each page to be left as free space when the table space is loaded or reorganized.

Integer can range from 0 to 99.

The first record on each page is loaded without restriction. When additional records are loaded, at least integer percent of free space is left on each page.

DEFINE:

Specifies at which point (when) the underlying data sets for the table space are physically created.

YES: The data sets are created when the table space is created. This 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 specification). DEFINE NO is ignored for user-managed data sets (USING VCAT specification).

LOGGED:

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

TRACKMOD:

Specifies whether DB2 tracks modified pages in the space map pages of the table space.

YES: Tracked. This is the default.
NO: Not tracked.

DSSIZE:

Specifies the maximum size for each data set.

MAXPARTITIONS:

Specifies the maximum number of partitions in a partitioned table space.

MEMBER CLUSTER:

Specifies that data inserted by an “insert operation” is not clustered by the clustering index. Instead, DB2 chooses where to locate the data in the table space based on available space.

Clustered and Non-Clustered Index:

Indexes are organized with the B-Tree structure.

Clustered Index:

The leaf level (the lowest level of the tree) is the data.
For a table that has a clustered index, the data is actually stored in the order of the index.

Non-clustered Index:

The leaf contains bookmarks to the actual data.
The bookmarks in non-clustered indexes are in RID format (Row ID), that is, direct pointers to the physical location the row is stored in.

NUMPARTS:

Indicates that the table space is partitioned.
Integer is the number of partitions.

PARTITION:

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

BUFFERPOOL:

Identifies the buffer pool to be used for the table space.

CCSID:

Specifies the encoding scheme for tables in the TS.

CLOSE:

When the limit on the number of open data sets is reached, specifies the priority in which data sets to be closed.

YES: Eligible for closing data sets. This is the default.
NO: Eligible for closing after all CLOSE YES data sets is closed.

COMPRESS: Specifies whether data compression applies to the rows of the table space.

YES: Specifies data compression.
NO: Specifies no data compression.

LOCKMAX: Specifies the maximum number of page or row locks an application process can hold simultaneously in the table space.

LOCKSIZE: Specifies the size of locks used within the table space.

MAXROWS: Specifies the maximum number of rows that DB2 will consider placing on each data page.

SEGSIZE:

Specifies that the table space will be a segmented.
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 4 and 64 (inclusive).

Example 1:

Create table space DSN8S91D in database DSN8D91A. Let DB2 define the data sets, using storage group DSN8G910. The primary space allocation is 52 kilobytes; the secondary, 20 kilobytes. The data sets need not be erased before they are deleted. Locking on tables in the space is to take place at the page level. Associate the table space with buffer pool BP1. The data sets can be closed when no one is using the table space.

CREATE TABLESPACE DSN8S91D
        IN DSN8D91A
              USING STOGROUP DSN8G910
                             PRIQTY 52
                             SECQTY 20
                             ERASE NO
              LOCKSIZE PAGE
              BUFFERPOOL BP1
              CLOSE YES;

Example 2:

Assume that a large query database application uses a tablespace to record historical sales data for marketing statistics. Create large tablespace SALESHX in database DSN8D91A for the application. Create it with 82 partitions, specifying that the data in partitions 80 through 82 is to be compressed.

Let DB2 define the data sets for all the partitions in the tablespace, using storage group DSN8G910. For each data set, the primary space allocation is 4000 kilobytes, and the secondary space allocation is 130 kilobytes. Except for the data set for partition 82, the data sets do not need to be erased before they are deleted. Locking on the table is to take place at the page level.

There can only be one table in a partitioned tablespace. Associate the tablespace with buffer pool BP1. The data sets cannot be closed when no one is using the tablespace. If there are no CLOSE YES data sets to close, then DB2 might close the CLOSE NO data sets when the DSMAX is reached.

CREATE TABLESPACE SALESHX
       IN DSN8D91A
              USING STOGROUP DSN8G910
                    PRIQTY 4000
                    SECQTY 130
                    ERASE NO
       NUMPARTS 82
       (PARTITION 80
       COMPRESS YES,
       PARTITION 81
       COMPRESS YES,
       PARTITION 82
          COMPRESS YES
          ERASE YES)
       LOCKSIZE PAGE
       BUFFERPOOL BP1
       CLOSE NO;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!

⇐ Previous ⇑ Back to top Next ⇒

Are you looking for Job Change? Job Portal