Segmented Tablespace


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 .

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 specified in the creation of table space, then segmented table space will be created.