Tablespace Create and Template Options

The tablespace create and template options provide all the functions of the DB2 CREATE TABLESPACE command. This statement allocates and formats a tablespace in which one or more tables can be created.The template option allows creation of a new tablespace using an existing tablespace as a template.
carcupdb218
The tablespace create and template options provide all the functions of the DB2 CREATE TABLESPACE command. This statement allocates and formats a tablespace in which one or more tables can be created.The template option allows creation of a new tablespace using an existing tablespace as a template.
The Tablespace Create and Template panels enable you to create or template any type of DB2 tablespace (segmented, partitioned, or universal) that is supported by the version of DB2 and mode on which you are running or for which DDL is to be generated and subsequently executed.
DB2 supports the following different types of tablespaces:
  • Universal (UTS) tablespaces (range-partitioned and partition-by-growth)
    This type of tablespace uses a combination of partitioned and segmented tablespace schemes.
  • EA-enabled tablespaces and indexspaces
  • Large object tablespaces
  • XML tablespaces
  • Simple tablespaces (deprecated)
  • Segmented (non-UTS tablespaces) (deprecated)
  • Partitioned (non-UTS tablespaces) (deprecated)
For more information about these tablespace types, see the IBM DB2 for z/OS documentation.
Review the following topics for information about creating specific tablespace types:
You can set defaults for most tablespace values through the DEF command. For more information, see Object Definition Defaults.
3
3
Once all Create information has been given, a confirmation panel appears. This panel displays the actual DDL that will be used to create the tablespace. You can accept, edit, or reject the tablespace creation.
Create Range-Partitioned Universal Tablespaces
In addition to being partitioned, a range-partitioned tablespace is also segmented.
A range-partitioned DB2 tablespace can contain only one table, which must be a table-controlled partitioned table.
Follow these steps:
  1. Complete fields as follows on the Tablespace Alter screen:
    • Specify a name in the Table Space field.
    • Specify a creator in the Creator field.
    • Type
      RANGE
      in the TS Type field.
    • Specify a non-zero value in the Segment Size field.
      Because a value exists that defines a segment size, you cannot specify YES for Mbr Cluster (which would indicate that inserted data should not be clustered by a clustering index).
      You can use the DEF command to set defaults for all fields except Table Space, Partitioned, and Erase.
    Press Enter.
    Your settings are applied to the tablespace. If a value was not specified for Lockmax, a message appears indicating that a default value was created.
  2. Specify values in the Partition Information fields to define storage-related information about the tablespace, then press the END key to process the creation.
    You can use standard ISPF line commands to create and manipulate partitions. You must enter a VCAT or storage group if you do not want to accept the storage group default (SYSDEFLT). You can set defaults through the DEF command for all of these fields except Erase.
    Your tablespace is defined.
Create Partition-by-Growth Universal Tablespaces
A partition-by-growth (PBG) tablespace is a universal tablespace (UTS) whose size can grow to accommodate data growth. This type of tablespace starts with one partition, and DB2 adds partitions as needed. (A universal tablespace is partitioned and segmented.)
Partitions
cannot
be explicitly added, rotated, or altered. The tablespace
must
be DB2-managed (not user-managed) so that DB2 can create data sets as partitions become full.
A PBG UTS tablespace has no limit keys. All partition data sets share the same partition information.
Follow these steps:
  1. Complete the following fields on the Tablespace Create panel and press Enter:
    • Type the tablespace name in the
      Table Space
      field.
    • Specify the database in which the tablespace is created in the
      Database
      field.
    • Specify YES in the
      Partitions
      field.
    • Specify GROWTH in the
      TS Type
      field. Do
      not
      specify GROWTH for a tablespace that is in a work file database.
    • Specify the tablespace creator in the
      Creator
      field.
    • Specify the name of the buffer pool to use for the tablespace in the
      Buffer Pool
      field. This value also determines the page size. The specified name must identify an activated buffer pool. If you specify DEFAULT, the default buffer pool of the database for the tablespace is used.
    • (Optional) Specify a non-zero value in the
      Segment Size
      field, which determines how many pages to assign to each tablespace segment. If you do not define a value, Segment Size defaults to 4.
    • Specify the maximum number of partitions to which a PBG universal tablespace is allowed to grow in the
      Max Parts
      field. This value must be an integer in the range of 1 to 4096, depending on the corresponding value of DSSIZE and Buffer Pool. Any unacceptable value that is specified for Max Parts is automatically adjusted downward to the nearest acceptable value. When DSSIZE is blank, 4 GB is assumed.
      Note:
      For information about the maximum allowable value for Max Parts in relation to the page size and DSSIZE value for the tablespace, see the
      IBM DB2 for z/OS SQL Reference
      .
    • DSSIZE
      is used to specify a value, in gigabtyes, that indicates the maximum size for each partition (or for each data set for LOB tablespaces) in the
      DSSIZE
      field. For va
    You can use the DEF command to set defaults for all fields except Table Space, Partitions, and Erase.
    Your settings are applied to the tablespace.
  2. Complete the following fields for defining partition characteristics of the PBG tablespace:
    • D
      SSIZE
      Specifies a value, in gigabytes, that indicates the maximum size for each partition (or for each data set for LOB tablespaces). For values over 4 GB, special SMS requirements are in effect.
    • B
      uffer Pool
    Press Enter.
    Partition information is configured for your PBG tablespace. VCAT is
    not
    allowed for a PBG tablespace.
  3. Press the END key.
    The Creation Confirmation panel appears.
  4. Press Enter, and enter
    Y
    on the Execution Confirmation prompt to initiate creation.
    The AUDIT Message File appears, with details about the creation and a description of any problems that occurred.
    Your tablespace is defined.
Create a LOB Tablespace
A LOB tablespace is used for holding an auxiliary table, which stores LOB data for a LOB column. If a column is defined as LOB in a table, it requires its own auxiliary tablespace and index.
Follow these steps:
  1. Complete the following required fields on the Tablespace Create panel:
    • Type the tablespace name in the Table Space field.
    • The name of the database in which the tablespace is created is displayed in the Database field. The database must be the same database in which the base table and its tablespace resides.
    • Type
      LOB
      in the TS Type field.
    • Type
      YES
      in the Partitions field.
  2. Specify values in the Partition Information fields to define storage-related information about the tablespace, then press the END key to process the creation.
    Press Enter.
    Partition information is configured for your PBG tablespace. VCAT is
    not
    allowed for a PBG tablespace.
  3. Press the END key.
    The Creation Confirmation panel appears.
Group Bufferpool Cache Setting for Tablespace Partitions
When you issue the G line command next to a partition on a tablespace create, alter, or template panel, the Group Bufferpool Cache Setting panel appears.
To specify what pages are written to the group buffer pool in a data sharing environment, enter one of the following values in the GBP Cache for partition field:
  • A (GBP CACHE ALL)
    Caches both changed and unchanged pages in the group buffer pool.
  • N (GBP CACHE NONE)
    Caches no pages in the group buffer pool. DB2 uses the group buffer pool only for cross-invalidation. If you specify this option, the tablespace or partition must not be in rebuild pending status and must be in the dropped state.
  • S (GBP CACHE SYSTEM)
    Caches only the changed system pages within the LOB tablespace in the group buffer pool. A system page is a space map page or any other page that does not contain actual data values. This is valid only for a LOB tablespace.
  • C (GBP CACHE CHANGED)
    Caches only changed pages in the group buffer pool. This is the default.
Create Simple Tablespaces
A simple tablespace is one that is not segmented or partitioned, and can contain many tables. This type of tablespace is not used frequently because the whole tablespace (instead of only one table) must be locked at once. The whole tablespace must be scanned to scan one table. The space of a dropped table cannot be reused immediately. An advantage of this tablespace type is that you can control the order of the rows that are stored in a table.
Follow these steps:
  1. Enter data in the following required fields on the Tablespace Create panel:
    • Table Space (tablespace name)
    • Creator (the creator of the tablespace)
    Other fields can contain default specifications.
  2. Enter storage-related information about the tablespace in the Partition Information fields. The only valid line commands are V (VSAM Define), S (space calculation), and U (undo changes).
    Because we are creating a simple tablespace, only enter information on the first line, which represents the whole tablespace. A VCAT or storage group must be entered to override the storage group default (SYSDEFT). The rest of the information is optional.
    • VCAT (VSAM catalog specification)
    • STOGROUP (defaults to SYSDEFLT)
    • PRIQTY (valid range is 12 to 4194304 KB, or DEFAULT)
    • SECQTY (valid range is 0 to 4194304, or DEFAULT)
    • ERASE (NO the default or YES)
    • FRPAGE (valid range is 0 to 255, defaults to 0)
    • %FR (valid range is 0 to 99, defaults to 5)
    • COMP (NO or YES)
    • TRKMOD (NO or YES)
  3. Press the End key (F3) to process the creation.
Create Segmented Tablespaces
A segmented tablespace is a non-partitioned tablespace that has been segmented. A segmented tablespace can contain more than one table; but each segment contains rows from only one table. Every segment in a segmented tablespace is the same size. A table uses only as many segments as it needs. Segmented tablespaces provide the following advantages over simple tablespaces.
  • To scan a table, scan the segments containing data from that table.
  • Only one table can be locked in the tablespace.
  • The space of a dropped table can be reused immediately.
One drawback to a segmented tablespace is that some DB2 utilities operate on a tablespace or partition basis only. For example, COPY, REORG, or LOAD REPLACE.
Follow these steps:
  1. Enter data in the following required fields on the Tablespace Create panel:
    • Table Space
    • Creator
    • Partitions (NO)
    • Segment Size (00)
    Other fields are optional and use the default settings unless changed. You can use the DEF command to set defaults for all fields except Table Space and Partitions. For more information about the fields, press the Help key (F1 typically).
  2. Enter storage-related information about the tablespace in the Partition Information fields.
    Because we are creating a segmented tablespace, only enter information on the first line. A VCAT or storage group must be entered to override the storage group default (SYSDEFT).
    Because the tablespace is not partitioned, only the following line commands are valid:
    • V (VSAM Define)
    • S (Space Calculation)
    • U (Undo any changes, set back to old definition)
    Defaults can be set through the DEF command for all fields except ERASE.
  3. Press F3 (End) to process the creation.
Create a Partitioned Tablespace
You can create a partitioned DB2 tablespace.
Follow these steps:
  1. Enter data in the following required fields on the Tablespace Create screen:
    • Tablespace
    • Creator
    Other fields are optional and use the default settings unless changed. You can use the DEF command to set defaults for all fields except Table Space and Partitions. For more information about the fields, press the Help key (F1 typically).Other fields contain default specifications.
    You can set defaults through the DEF command for all these fields except Table Space and Partitioned.
    Your settings are applied to the tablespace.
  2. Specify values in the Partition Information fields to define storage-related information about the tablespace, then press the END key to process the creation.
    You can use standard ISPF line commands to create and manipulate partitions. You must enter a VCAT or storage group if you do not want to accept the storage group default (SYSDEFLT). You can set defaults through the DEF command for all of these fields except Erase.
    Your tablespace is defined.