Adding partitions

You can use ALTER TABLE statements to add partitions to all types of partitioned table spaces.

About this task

You do not need to allocate extra partitions for expected growth when you create partitioned table spaces because you can add partitions as needed.

You can add a partition as the last logical partition of any table in any type of partitioned table space.

FL 500Adding a partition as the last logical partition of a table specifies that a partition is added to the table and each partitioned index on the table. A partition added as the last logical partition is always an immediate definition change. A partition cannot be added if the table space definition is incomplete because a partitioning key or partitioning index is missing. If the table uses index-controlled partitioning, it is converted to use table-controlled partitioning. In addition, adding a partition to the end of the table is not allowed if there are any outstanding pending definition changes on the partitions.

Start of change For tables in partition-by-range table spaces, you can also add a partition between existing logical partitions. Adding a partition between existing partitions is a pending data definition change if the data sets for the added partition are already defined. Each affected partition is placed in advisory REORG-pending (AREOR) status. End of change

FL 500Adding a partition between existing logical partitions is supported only for partition-by-range table spaces. When inserting a new partition between existing partitions, the following rules apply:

If ADD PARTITION ENDING with the optional ALTER PARTITION clause is used to add a new partition between existing partitions, the ALTER PARTITION clause must specify the very next logical partition to the partition being added. The high limit key value specified in the ALTER PARTITION clause must be the existing high limit key value for the very next logical partition. The high limit key value cannot be altered in the same statement when inserting a new partition.
Any pending definition changes for the high limit key of the last logical partition must be materialized before a partition can be added between existing partitions in the same table.
After a new partition is added between existing partitions of a table, altering the limit key is not allowed for any partition in the same table until the newly inserted partition is materialized by a REORG execution.
A partition cannot be inserted on any table that contains a LOB column, or a distinct type column that is based on a LOB data type.
A partition cannot be inserted on any table that contains an XML column.

When you add partitions Db2 always uses the next physical partition that is not already in use, until you reach the maximum number of partitions for the table space. Start of change The next physical partition is used even if the new partition is added between existing logical partitions. End of change

When Db2 manages your data sets, the next available data set is allocated for the table space and for each partitioned index. When you manage your own data sets, you must first define the data sets for the table space and the partitioned indexes before you add a partition.

You cannot add or alter a partition for a materialized query table.

Procedure

Start of change To add partitions, use the following approaches: End of change

Add a partition after the last existing logical partition by issuing an ALTER TABLE statement. In the ADD PARTITION clause, specify an ENDING AT value beyond the existing limit of the last logical partition.

In most cases, you can use the new partition immediately after the ALTER statement completes. In this case, the partition is not placed in REORG-pending (REORP) status because it extends the high-range values that were not previously used.

However, for non-large table spaces, the partition is placed in REORP status because the last partition boundary was not previously enforced. A non-large table space has 4-byte row identifiers (RIDs) and is limited to a maximum size of 64 GB. For more information about non-large table spaces, see Table space types and characteristics in Db2 for z/OS.
Add a partition between existing logical partitions, by completing the following steps:
1. Issue an ALTER TABLE statement with an ADD PARTITION clause that specifies an ENDING AT value between existing partition limits.
  This method is supported only for partition-by-range table spaces.
  
  Adding partitions between existing partitions results in a pending data definition change if the data sets for the added partition already exist. The existing logical partition that previously contained range of the new partition is also placed in advisory REORG-pending (AREOR) status.
2. Resolve the pending data definition change by taking one of the following actions:
  - Run a partition-level REORG for each affected partition, including newly added partitions and adjacent affected partitions. You can identify the affected partitions by querying the SYSIBM.SYSPENDINGDDL catalog table for rows inserted when the ALTER TABLE statement was run. The REORG_SCOPE_LOWPART and REORG_SCOPE_HIGHPART columns indicate the existing boundaries of the affected partitions.
  - Run a table space-level REORG and specify the SCOPE PENDING option.

Examples

Begin general-use programming interface information.

For example, consider a table space that contains a transaction table named TRANS. The table is divided into 10 partitions, and each partition contains one year of data. Partitioning is defined on the transaction date, and the limit key value is the end of each year. The following table shows a representation of the table space.

Table 1. An example table space with 10 partitions
Limit value	Physical partition number	Data set name that backs the partition
12/31/2010	1	catname.DSNDBx.dbname.psname.I0001.A001
12/31/2011	2	catname.DSNDBx.dbname.psname.I0001.A002
12/31/2013	3	catname.DSNDBx.dbname.psname.I0001.A003
12/31/2013	4	catname.DSNDBx.dbname.psname.I0001.A004
12/31/2014	5	catname.DSNDBx.dbname.psname.I0001.A005
12/31/2015	6	catname.DSNDBx.dbname.psname.I0001.A006
12/31/2016	7	catname.DSNDBx.dbname.psname.I0001.A007
12/31/2017	8	catname.DSNDBx.dbname.psname.I0001.A008
12/31/2018	9	catname.DSNDBx.dbname.psname.I0001.A009
12/31/2019	10	catname.DSNDBx.dbname.psname.I0001.A010

Example 1: adding a partition after the last logical partition

To add a partition for the next year, you can issue the following statement:

ALTER TABLE TRANS ADD PARTITION ENDING AT ('12/31/2020');

End general-use programming interface information.

The following table shows a representative excerpt of the table space after the partition for the year 2020 is added.

Table 2. An excerpt of the table space, showing the added partition 11
Limit value	Physical partition number	Data set name that backs the partition
12/31/2018	9	catname.DSNDBx.dbname.psname.I0001.A009
12/31/2019	10	catname.DSNDBx.dbname.psname.I0001.A010
12/31/2020	11	catname.DSNDBx.dbname.psname.I0001.A011

Example 2: adding a partition between existing logical partitions

The following statement adds a new partition for the first half of the year 2020 to the TRANS table from the previous example:

ALTER TABLE TRANS 
ADD PARTITION ENDING AT ('06/30/2020');

Table 3. An excerpt of the table space, showing the added partition 12
Limit value	Physical partition number	Data set name that backs the partition
12/31/2018	9	catname.DSNDBx.dbname.psname.I0001.A009
12/31/2019	10	catname.DSNDBx.dbname.psname.I0001.A010
06/30/2020	12	catname.DSNDBx.dbname.psname.I0001.A012
12/31/2020	11	catname.DSNDBx.dbname.psname.I0001.A011

The following table shows a representative excerpt of the table space after the extra partition for the first half of year 2020 is added by either of the preceding example statements. Even though the partition is added logically between existing partitions 10 and 11, notice that it is partition 12, the next available physical partition.

Optionally, you can also specify an ALTER PARTITION clause that explicitly references the subsequent existing partition after the new one. However, the ALTER PARTITION clause must specify the existing limit value. For example, the following statement has the same result as the previous example:

ALTER TABLE TRANS 
ADD PARTITION ENDING AT ('06/30/2020')
ALTER PARTITION 11 ENDING AT ('12/31/2020');

End general-use programming interface information.

What to do next

After you add partitions, you might need to complete any of the following actions.

Alter the attributes of added partitions

You might need to alter the attributes of the added partition. The attributes of the new partition are either inherited or calculated. If it is necessary to change specific attributes for the new partition, you must issue separate ALTER TABLESPACE and ALTER INDEX statements after you add the partition. Examine the catalog to determine whether the inherited values require changes.

The source used for each inherited attribute depend on the position of the new partition, and other factors. However, with certain exceptions, the following general pattern is used:

A partition that is added between existing logical partitions inherits most attribute values from the table space.
A partition that is added as the new last logical partition inherits most attribute values from the previous last logical partition.

For a detailed description of how Db2 determines the attributes of the added partitions, see How Db2 determines attributes for added partitions. End of change

Begin general-use programming interface information. For example, if you want to specify the space attributes for a new partition, use the ALTER TABLESPACE and ALTER INDEX statements. For example, suppose that the new partition is PARTITION 11 for the table space and the index. Issue the following statements to specify quantities for the PRIQTY, SECQTY, FREEPAGE, and PCTFREE attributes:

ALTER TABLESPACE tsname ALTER PARTITION 11
   USING STOGROUP stogroup-name 
    PRIQTY 200 SECQTY 200
    FREEPAGE 20 PCTFREE 10;

ALTER INDEX index-name ALTER PARTITION 11
   USING STOGROUP stogroup-name 
    PRIQTY 100 SECQTY 100
    FREEPAGE 25 PCTFREE 5;

End general-use programming interface information.

Create auxiliary objects for LOB columns

For partitioned tables, each partition of the base table requires a separate LOB table space, auxiliary table, and auxiliary index for each LOB column.

Db2 sometimes implicitly creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column in a table or partition. For more information, see LOB table space implicit creation.

If Db2 does not implicitly create the LOB table spaces, auxiliary tables, and indexes on the auxiliary tables, you must create these objects by issuing CREATE TABLESPACE, CREATE AUXILIARY TABLE, and CREATE INDEX statements.