Creation of table spaces
Often you do not need to create a table space before you create a table. You only need to create a table space explicitly when you define a declared temporary table or if you manage all of your own data sets.
Advantages of universal table spaces
Universal table spaces (UTS) combine the benefits of data partitions and segmented organization. Each UTS always contains only a single table.
Universal table spaces offer the following advantages, when compared to the deprecated non-UTS types:
- A choice of partitioning methods:
- Partitions based on ranges of data values, with Partition-by-range table spaces (PBR)
- Partitions based on data growth and automatically managed by Db2, with Partition-by-growth table spaces (PBG)
- Improved space management for varying-length rows because a segmented space-map page has more information about free space than a partitioned space-map page
- Improved mass delete performance because mass delete in a segmented table space organization tends to be faster than in non-segmented table space organizations
- Table scans that are localized to segments
- Immediate reuse of all or most of the segments of a table after the table is dropped or mass deleted
Comparison of table space types
The following table compares the characteristics of the various table space types that Db2 for z/OS® supports.
| Type | Segmented? | Partitioned? | Remarks |
|---|---|---|---|
| Partition-by-range table space | Yes | Yes, based on data value ranges | Can use absolute or relative page numbering. |
| Partition-by-growth table space | Yes | Yes, based on data growth | |
| LOB table space | No | No | For auxiliary tables that contain the data for LOB columns. |
| XML table space | Yes | Yes, based on the partitioning method of the base table. | UTS table spaces that are created implicitly for XML data. |
| Partitioned (non-UTS) table space | No | Yes, based on data value ranges. | This type is deprecated.1 |
| Segmented (non-UTS) table space | Yes | No | This type is deprecated.1 |
| Simple table space | No | No | This type is deprecated.2 |
- 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).
- Db2 13 does not support creating simple table spaces. Existing simple table spaces remain supported, but they are likely to be unsupported in the future.
Implicit creation of table spaces
Db2 creates a table space implicitly if you issue a CREATE TABLE statement without specifying an existing table space name. Db2 derives the table space name from the name of your table. The table space is created in the either the specified database or, if the database is not specified, in an implicitly created database. For more information, see Implicitly defined table spaces.
Explicit creation of table spaces
If you must create table spaces explicitly, you can issue CREATE TABLESPACE statements.
When you create or alter a table space, the clauses that you specify in a CREATE TABLESPACE or ALTER TABLESPACE statement determine the type of table space. For more information, see Creating table spaces explicitly.