How Db2 determines attributes for added partitions
When an ALTER TABLE statement adds a new table or index partition, the sources for the attributes of the new partition vary depending on the position of the new partition and other factors.
Generally, a partition that is added as the new last logical partition inherits most attributes from the existing last logical partition, and a partition that is added between existing logical partitions inherits most attribute values from the table space.
The following table shows the inheritance of attributes for a new partition, based on the position of the new partition. In the table, Last logical partition
refers to the existing last logical partition before the new partition is added. Attributes that are not shown use default values.
For descriptions of the partition attributes, see SYSTABLEPART catalog table and SYSINDEXPART catalog table.
| Partition attribute | New partitions added between existing partitions | New partitions added after existing logical partitions |
|---|---|---|
| PQTY | Table space or index space 1 | Last logical partition |
| SQTY | Calculated as the lesser of the SECQTYI value or 32767. | Last logical partition |
| STORTYPE | Table space or index space 1 | Last logical partition |
| STORNAME | Table space or index space 1 | Last logical partition |
| VCATNAME | Table space or index space 1 | Last logical partition |
| FREEPAGE | Table space or index space1 | Last logical partition |
| PCTFREE | Table space or index space 1 | Last logical partition |
| GBPCACHE | Table space or index space 1 | Last logical partition |
| TRACKMOD | Table space 1 | Last logical partition |
| SECQTYI | Table space or index space 1 | Last logical partition |
| COMPRESS | Table space 1 | Last logical partition |
| FORMAT | Last logical partition | Last logical partition |
| HASHSPACE | Table space | Table space |
| RBA_FORMAT | Last logical partition | Last logical partition |
| PCTFREE_UPD | Table space 1 | Last logical partition |
| PCTFREE_UPD_CALC | Table space 1 | Last logical partition |
| TYPE | Table space 2 | Table space 2 |
| PAGENUM | Table space 2 | Table space 2 |
| BPOOL | Table space 2 | Table space 2 |
| PGSIZE | Table space 2 | Table space 2 |
| DSSIZE | Table space 2 | Table space or index space 3 |
| MEMBER_CLUSTER | Table space 2 | Table space 2 |
- If the corresponding SYSTABLESPACE or SYSINDEXES value is NULL, for objects created or last altered in Db2 12 at function level 100 or earlier, Db2 updates the space-level value based on the existing last logical partition, and the new partition inherits this value.
- If the corresponding SYSTABLESPACE or SYSINDEXES value is NULL, for objects created or last altered in Db2 12 at function level 100 or earlier, the value for the new partition is NULL.
- If DSSIZE=0 in the SYSTABLESPACE table and the table space is not large, the value for the table partition is 4G.
- If partitions are added by partitioning scheme alteration from PBG to PBR.
