DB2 supports different types of table spaces. Each type of table space has different characteristics. Universal table spaces, which combine the benefits of segmented and partitioned table spaces, are the preferred type. Other types are deprecated. They are supported in DB2 10, but support might be removed in the future.
Table space will be represented as Segments and data will be stored in the segments. all the segments are of same size. segment’s is nothing but the set of pages. The page number can be defined by SEGSIZE during the TABLESPACE definition. One Table can be stored in one or more segments. one segment can contain only one table rows. at any point of time, none of the two tables rows couldn’t stored on same segment. SEGSIZE will be used to define the table space as segmented. if the SEGSIZE value provided during the declaration of table space, then the table space is segmented table space.
Syntax
CREATE TABLESPACE tablespace-name
IN database-name
USING STOGROUP storage-group-name
PRIQTY priqty-value
SECQTY secqty-value
ERASE {NO/YES}
FREEPAGE freepage-value
PCTFREE pctfree-value
COMPRESS {YES/NO }
SEGSIZE segment-size
BUFFERPOOL bufferpool-value
LOCKSIZE {ANY/locksize-value}
LOCKMAX {SYSTEM/lockmax-value}
CLOSE {YES/NO}
MAXROWS {1-255}
A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table.
Segmented table spaces are deprecated. They are supported in DB2® 10, but support might be removed in the future. Use universal table spaces instead.
Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets. A table space can be larger if either of the following conditions is true, the table space is a partitioned table space that you create with the DSSIZE option. the table space is a LOB table space.
Table space pages can be 4 KB, 8 KB, 16 KB, or 32 KB in size. The pages hold segments, and each segment holds records from only one table. Each segment contains the same number of pages, and each table uses only as many segments as it needs. when you run a statement that searches all the rows for one table, DB2 does not need to scan the entire table space. Instead, DB2 can scan only the segments of the table space that contain that table. The following figure shows a possible organization of segments in a segmented table space.
A segmented (non-UTS) table space consists of segments that hold the records of one table. yYou define a segmented (non-UTS) table space by using the CREATE TABLESPACE statement with a SEGSIZE clause. If you use this cluse, the value that you specify represents the number of pages in each segment. The value must be a multiple of 4 (from 4 to 64). The choice of the value depends on the size of the tables that you store. The following table summarizes the recommendations for SEGSIZE.
| Number of pages | SEGSIZE recommendation |
|---|---|
| ≤ 28 | 4 to 28 |
| > 28 < 128 pages | 32 |
| ≥ 128 pages | 64 |
Another clause of the CREATE TABLESPACE statement is LOCKSIZE TABLE. This clause is valid only for tables that are in segmented table spaces. DB2, therefore, can acquire locks that lock a single table, rather than the entire table space. if you want to leave pages of free space in a segmented (non-UTS) table space, you must have at least one free page in each segment. Specify the FREEPAGE clause with a value that is less than the SEGSIZE value.