Summary -
In this topic, we described about the Segmented Table Space with detailed example.
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}
The above parameters are explained in table space concept and find the page here .
Practical Example:
Below example is to create MFTHTSS segmented table space in MTH1DB under the storage group MFTCHP
CREATE TABLESPACE MFTHTSS IN MTH1DB USING STOGROUP MFTCHP PRIQTY 250 SECQTY 100 ERASE NO FREEPAGE 10 PCTFREE 10 COMPRESS NO SEGSIZE 20 BUFFERPOOL MF LOCKSIZE ANY LOCKMAX SYSTEM CLOSE YES MAXROWS 255
If the segment size is mentioned in the creation of table space, then segmented table space will be created.