Create a PBR table with specified physical partition order

Sometimes it is required to create a copy of an existing table that will exactly match the existing table. And as physical and logical partitions in PBR can be out of order, that includes the existing order of physical partition numbers.

CREATE TABLE with PARTITION BY clause only allows to create a new table with matching physical and logical partitions. Some tools like Cloning Tool and Administration Tool have an ability to generate CREATE TABLE followed by a set of ALTER TABLE statements that will reorder physical partitions. But this approach has it's limitations. So it would be nice to have a way to specify physical partition numbers directly in the CREATE TABLE statement. For example:

CREATE TABLE ...

PARTITION BY (...)

(PHYSICAL PARTITION 1 ENDING AT (10),

PHYSICAL PARTITION 3 ENDING AT (20),

PHYSICAL PARTITION 2 ENDING AT (30),

PHYSICAL PARTITION 4 ENDING AT (40))

Or like that:

(PARTITION 1 ENDING AT (10) PHYSICAL 1,

PARTITION 2 ENDING AT (20) PHYSICAL 3,

PARTITION 3 ENDING AT (30) PHYSICAL 2,

PARTITION 4 ENDING AT (40) PHYSICAL 4)

Alternatively, Db2 could improve the way this can be done by ADD PARTITION statements. The example above can be created by:

CREATE TABLE ...

(PARTITION 1 ENDING AT (10),

PARTITION 2 ENDING AT (30));

ALTER TABLE ... ADD PARTITION ENDING AT (20);

ALTER TABLE ... ADD PARTITION ENDING AT (40);

But if tablespace is created with DEFINE YES, the last ALTER fails with SQLCODE = -20385 because Db2 always treats ADD PARTITION to the end as an immediate change. If Db2 could make it a pending change when there are already other pending changes on the tablespace then all partition changes could be stacked and materialized with one REORG.

If tablespace is created with DEFINE NO then there is no problem with SQLCODE = -20385. But in this case we need a simple way to define all datasets for the tablespace after partition changes. Something like ALTER TABLESPACE ... DEFINE YES;