Why does Altering the INCREMENT BY clause of a sequence object cause the analysis to generate a DROP/CREATE instead of an ALTER using RC/Migrator
Article ID: 22692
Updated On:
Products
Issue/Introduction
Why does Altering the INCREMENT BY clause of a Sequence object cause the RC/Migrator for Db2 for z/OS (RCM) analysis to generate
a DROP/CREATE instead of an ALTER?
Resolution
Creating an Alter strategy to modify the INCREMENT BY parameter of a Sequence object generated a DROP/CREATE on the object and not an ALTER statement.
.SYSTEM SQLDDL
.CONNECT ssid
.AUTH authid1
DROP SEQUENCE xxx.xxxxxxxx;
.SYNC 5 'DROP SEQUENCE'
-- **************************************************************
-- * *
-- * SEQUENCE CREATE AND ALTER STATEMENTS *
-- * *
-- **************************************************************
-- authid2.xxxxxxxx WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
.AUTH authid1
SET CURRENT SQLID = 'authid1';
CREATE SEQUENCE authid2.xxxxxxxx AS INTEGER
START WITH 1
INCREMENT BY 2
MINVALUE 1
MAXVALUE 2147483647
CYCLE
CACHE 25
ORDER
;
-- Sequence Changes for:
-- Name: xxxxxxxx
--
-- Attribute Status Value
-- --------- ----------- ----+----1----+----2----+----3--
-- SCHEMA Currently: xxx
-- Changed To: authid2
-- INCREMENT Currently: 1
-- Changed To: 2
In this strategy Global Changes were used with a change specified for CODE SQSC which is the Sequence Object Schema.
--Global Changes:
--
-- SET NAME ===> xxxxxxxx
-- SET CREATOR ===> authid1
--
-- CODE DESCRIPTION
--
-- SQSC SCHEMA
-- FROM * TO authid2
When there is a Global Change applied to the Sequence Object via the Global Change SQSC (Sequence Schema), this forces the DROP/CREATE
of the Sequence Object because the schema is changing.
When the Alter is used to only change the INCREMENT BY clause without a SQSC Global change, an ALTER statement is generated in the analysis output.
.AUTH authid1
SET CURRENT SQLID = 'authid1';
ALTER SEQUENCE xxx.xxxxxxxx
INCREMENT BY 2
;
.SYNC 5 'ALTER SEQUENCE xxx.xxxxxxxx'
-- Sequence Changes for:
-- Name: xxxxxxxx
--
-- Attribute Status Value
-- --------- ----------- ----+----1----+----2----+----3--
-- INCREMENT Currently: 1
-- Changed To: 2
Additional Information
Feedback
