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?
Release : 20.0
Component : CA RC/Migrator for DB2 for z/OS
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
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