How to modify DDL when UPI is on IDENTITY COLUMN & RI imposed

Database
Enthusiast

How to modify DDL when UPI is on IDENTITY COLUMN & RI imposed

Hi Forum,

I have a table created as SET table using IDENTITY column as UPI and it has data in it.

Now, the user wants us to change it to MULTISET, but, as the PI is on Identity column how can I do this?

(I am unable to create the copy of that table using Create table as ….. (CREATE TABLE Failed. 5788:  CREATE TABLE AS currently does not support Identity Columns. ),

even at least to test any scenario or logic on the actual table.

The major concern here is this table is having RI constraint imposed on the UPI column and needs to have the same values after modifying.

Table Structure:

CREATE SET TABLE P_INTERNAL_ORGANIZATION.SLS_AR ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      SLS_AR_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY

           (START WITH 1

            INCREMENT BY 1

            MINVALUE -2147483647

            MAXVALUE 2147483647

            NO CYCLE),

      Col2 VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Col3  VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,

      Col4 DATE FORMAT 'yyyy-mm-dd',

      Col5_PR DATE FORMAT 'yyyy-mm-dd',

CONSTRAINT UI_DVS_DSTRBT_CHNN_SL_ORGNZT UNIQUE ( DSTRBTN_CHNNL ,DVSN ,SLS_ORGNZTN ),

CONSTRAINT FK2_RPRTNG_ORGNZTN FOREIGN KEY ( RPRTNG_ORGNZTN ) REFERENCES P_INTERNAL_ORGANIZATION.RPRTNG_ORGNZTN ( INTRNL_ORGNZTN_ID ))

UNIQUE PRIMARY INDEX PI_SLS_AR ( SLS_AR_ID )

INDEX SI_ACTV ( ACTV );

Your assistance on this would be highly appreciated.

Thanks in advance!

Sravan Kumar Bodla.