Modification of existing partitioned tables

 

You can make several changes to your partitioned table by using the clauses of the ALTER TABLE statement.

These clauses are as follows:

• ADD PARTITION This clause adds one or more new hash partitions or a range partition to an existing partitioned table. Make sure that the parameters you specify do not violate the following rules; otherwise, errors occur.

  • Do not use the ADD PARTITION clause for nonpartitioned tables.

  • When adding hash partitions, the number of partitions being added must be specified as a positive integer.

  • If you supply a name or integer to identify the partition, ensure that it is not already in use by another partition.

  • When adding range partitions, the specified ranges must not overlap the ranges of any existing partitions.

  For example, to alter the table PAYROLL in library PRODLIB with partition key EMPNUM to have four additional partitions, use the following code:

  ALTER TABLE PRODLIB.PAYROLL 
  ADD PARTITION 4 HASH PARTITIONS

• ALTER PARTITION

  This clause alters the range for the identified range partition. Ensure that the following conditions are met:

  • The identified partition must exist in the table.

  • The specified ranges must not overlap the ranges of any existing partitions.

  • All existing rows of the partitioned table must fall within the new ranges specified on the ALTER TABLE statement.

• DROP PARTITION

  This clause drops a partition of a partitioned table. If the specified table is not a partitioned table, an error is returned. If the last remaining partition of a partitioned table is specified, an error is returned.

• Restrictions when altering a column's data type
  When altering a column's data type, and that column is part of a partitioning key, there are some restrictions on the target data type.

 

Parent topic:

Modification of existing tables