When altering a table to add partitions why do we not generate an ALTER statement?

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