Compare two table-controlled partitioning tables generate ALTER TABLE ADD PARTITION/ALTER TABLE ALTER PARTITION rather than DROP/CREATE
Article ID: 27222
Updated On:
Products
Issue/Introduction
How to perform a RC/Compare for Db2 for z/OS (RCC) compare of two table-controlled partitioning tables, which will generate
an ALTER TABLE ADD PARTITION/ALTER TABLE ALTER PARTITION rather than a DROP/CREATE.
Resolution
When performing an analysis of a RCC compare strategy, comparing two TCP (Table-Controlled Partitioning) tables, the analysis generates a drop/create
of the tablespace and/or indexes 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 setting.
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 setting.
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 because the STOGROUP attribute has been set to "N" above.
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/Migrator Table-Controlled Partitioning.
Feedback
