DB2 Partitioned Tablespace
DB2 Segmented Tablespace DB2 TABLETable 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 .
Practical 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.
DB2 Segmented Tablespace DB2 TABLE