Perform a compare of two table-controlled partitioning tables to generate an ALTER TABLE ADD PARTITION/ALTER TABLE ALTER PARTITION rather than a DROP/CREATE?

Document ID : KB000027222
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

How do I perform a compare of two table-controlled partitioning tables, which will generate an ALTER TABLE ADD PARTITION/ALTER TABLE ALTER PARTITION rather than a DROP/CREATE?

Answer:

When performing an analysis of a compare strategy, comparing two TCP (Table-Controlled Partitioning) tables, the analysis generates a drop/create of the tablespace and/or indices rather than an 'ALTER TABLE ADD PARTITION'. The results depend on the object level of the compare.

Comparing two TCP tables at the tablespace level.

The source DDL is changing the limit of one partition and adding a new partition.

The analysis generates the following DDL:

       DROP     TABLESPACE 
CREATE TABLESPACE
CREATE TABLE
CREATE INDEX

The DDL syntax is correct but it will be more efficient to generate an ALTER TABLE statement rather than the DROP/CREATE.

To achieve this we need to eliminate the comparing of the tablespace PARTITIONS attribute and its' sub-attributes through a ruleset.

The ruleset needs to be amended as follows:

Object            Attribute           Rule 
------ --------- ----
TABLESPACE NAME N
PARTITIONS N
VCAT N
STOGROUP N
PRIMARY N
SECONDARY N
ERASE N
FREEPAGE N
PCTFREE N
COMPRESS N
GBPCACHE N
VSAM_TYPE N
VSAM_UNIT N
VSAM_PRIM N
VSAM_SEC N
VSAM_VOLS N
VSAM_MPSW N
VSAM_CPSW N
VSAM_ERASE N
TRACKMOD N
TABLE TABLESPACE N

Using this ruleset will result in the changes to the table controlled partitions being generated with the ALTER TABLE statement rather than the DROP/CREATE.

ALTER TABLE ALTER PARTITION

ALTER TABLE ADD PARTITION

Scenario 2

Comparing two TCP tables at the table level.

The source DDL is adding a new partition.

The analysis generates the following DDL:

       ALTER      TABLE DROP PRIMARY KEY 
DROP PRIMARY INDEX
DROP PARTITIONED INDEX
ALTER TABLE ADD PARTITION
CREATE PRIMARY INDEX
CREATE PARTITIONED INDEX
ALTER TABLE ADD PRIMARY KEY
REORG TABLESPACE

The DDL syntax is correct but it would be more efficient to generate an ALTER TABLE statement rather than the DROP/CREATE/REORG.

To achieve this we need to eliminate the comparing of the index PARTITIONS attribute and its' sub-attributes through a ruleset.

The ruleset needs to be amended as follows:

OBJECT            ATTRIBUTE           RULE 
------ --------- ----
INDEX PARTITIONS N
VCAT N
STOGROUP N
PRIMARY N
SECONDARY N
ERASE N
FREEPAGE N
PCTFREE N
VALUES N
GBPCACHE N
VSAM_TYPE N
VSAM_UNIT N
VSAM_PRIM N
VSAM_SEC N
VSAM_VOLS N
VSAM_MPSW N
VSAM_CPSW N
VSAM_ERASE N

Using this ruleset will result in the changes to the table controlled partitions being generated with the ALTER TABLE statement without the need for the DROP/CREATE/REORG.

       ALTER     TABLE ADD PARTITION 

Additional Information:

Setting these ruleset attributes to N, will mean that any changes specifically relating to these attributes that were intended to be compared will be ignored.

So, if for example, the source DDL also included a change to the STOGROUP used for the partitions as defined in the tablespace, then this change will be ignored by the compare.

In order to also compare these attributes then a second compare would need to be performed, after the ALTER to the table-controlled partitions has been completed. For this second compare you would need to use a ruleset with the tablespace PARTITIONS attribute and its sub-attributes set to Y.

In this example, the tablespace PARTITIONS STOGROUP attribute would need to be set to Y.

The same would apply to index PARTITIONS attributes.

For further details on Table-Controlled Partitioning tables Considerations/Restrictions please refer to RC/Compare Table-Controlled Partitioning.