When altering a table to add partitions why do we not generate an ALTER statement using RC/Migrator
Article ID: 141964
Updated On:
Products
Issue/Introduction
When adding new partitions via RC/Migrator for Db2 for z/OS (RCM) to a table a DROP/CREATE TABLESPACE is generated.
Why is the change not achieved via an ALTER TABLE ADD PARTITION statement?
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 release r20, rather than generate the ALTER statement, we generate a DROP/CREATE as required.
This requirement is documented in the DB2 11 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
Feedback
