When altering a table to add partitions why do we not generate an ALTER statement?
book
Article ID: 141964
calendar_today
Updated On:
Products
CA Database Management for DB2 for z/OS - Administration SuiteCA RC/Migrator for DB2 for z/OSCA RC/Update for DB2 for z/OS
Issue/Introduction
When adding new partitions to a table a DROP/CREATE TABLESPACE is generated. Why is the change not achieved via an ALTER TABLE ADD PARTITION statement?
Environment
Release : 20.0
Component : CA RC/Migrator for DB2 for z/OS
Resolution
For a TS that is currently defined with number of partitions 16 and when DSSIZE is not specified, then the maximum number of partitions that this TS can have is only 16. You cannot add a 17th partition without dropping and recreating the TS.
If you tried to execute an ALTER TABLE ADD PARTITION to add a 17th partition this would fail with SQL error -4701
DSNT408I SQLCODE = -4701, ERROR: THE NUMBER OF PARTITIONS, OR THE COMBINATION OF THE NUMBER OF TABLE SPACE PARTITIONS AND THE CORRESPONDING LENGTH OF THE PARTITIONING LIMIT KEY EXCEEDS THE SYSTEM LIMIT, OR OPERATION IS NOT ALLOWED ON SYSTEM DATABASES; OR THE COMBINATION OF THE NUMBER OF TABLE SPACE PARTITIONS EXCEEDS THE MAXPARTITIONS FOR PARTITION BY GROWTH TABLE SPACES
On r20, rather than generate the ALTER statement, we generate a DROP/CREATE as required.
This requirement is documented in the DB2 V11 SQL Reference Guide
Table 105. Maximum number of partitions when DSSIZE = 0 Type of table space Number of existing partitions Maximum partitions non-large 1 to 16 16 non-large 17 to 32 32 non-large 33 to 64 64 large N/A 4096