Partitioned Tablespace
Partitioned Tablespace
Summary
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.