When altering a table to add partitions why do we not generate an ALTER statement?
Article ID: 141964
Updated On:
Products
CA Database Management for DB2 for z/OS - Administration Suite
CA RC/Migrator for DB2 for z/OS
CA 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
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
Feedback
Powered by
