Partitioned Tablespace

Table space will be represented as partitions. Partition is a storage unit used to store the table rows. Only one table can be stored for partition. Each partition can contain one data set.

One data set contains only one table information. Max 64 partitions can be stored for the table. NUMPART will represent the partitions during the table space creation. If NUMPART is coded during the declaration, then the table space is partitioned table space.

Syntax -

CREATE TABLESPACE Table-space          
       IN database-space                    
       NUMPARTS num-parts                    
    (PART 1 USING STOGROUP storage-group-name
       PRIQTY Priqty-value                   
       SECQTY Secqty-value                    
       FREEPAGE Integer                   
       PCTFREE Pctfree-value,                  
     PART 2 USING STOGROUP storage-group-name
       PRIQTY Priqty-value                   
       SECQTY Secqty-value                    
       FREEPAGE Integer                   
       PCTFREE Pctfree-value,                 
      .
      .
      .
	PART n USING STOGROUP storage-group-name
       PRIQTY Priqty-value                   
       SECQTY Secqty-value                    
       FREEPAGE Integer                   
       PCTFREE Pctfree-value                       
      )                 
       BUFFERPOOL Bufferpool-value
       MAXROWS max-rows-value      
        CLOSE {YES/NO }

The above parameters are explained in table space concept and find the page here .

Example -

Below example is to create MFTHTSP partitioned table space in MTH1DB under the storage group MFTCHP

CREATE TABLESPACE MFTHTSP          
       IN MTH1DB                    
       NUMPARTS 3                    
     (PART 1 USING STOGROUP MFTCHP
       PRIQTY 250                   
       SECQTY 100                    
       FREEPAGE 5                   
       PCTFREE 10,                  
      PART 2 USING STOGROUP MFTCHP 
       PRIQTY 250                   
       SECQTY 100                    
       FREEPAGE 5                   
       PCTFREE 10,                  
      PART 3 USING STOGROUP MFTCHP 
       PRIQTY 250                   
       SECQTY 100        
       FREEPAGE 5       
       PCTFREE 10       
      )                 
       BUFFERPOOL MF
       MAXROWS 200      
       CLOSE YES         

If NUMPARTS keyword used while creating tablespace, it will create partitioned table space.