MSCEWI1048

Sequence warning.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Information from sequences inside CREATE TABLE statements is extracted in order to build a CREATE OR REPLACE SEQUENCE statement in the migrated Snowflake code.

This EWI is added to the original location of the sequence from which the information was originally collected to create that statement.

Example Code

Teradata Input Code:

CREATE SET TABLE T_2031,
     FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
(
     SEQ1_2031 INTEGER FORMAT '--,---,---,--9' NOT NULL GENERATED ALWAYS AS IDENTITY,
     SEQ2_2031 INTEGER GENERATED ALWAYS AS IDENTITY
     (
          START WITH 1
          INCREMENT BY 1
          MINVALUE -2147483647
          MAXVALUE 2147483647
          NO CYCLE
     ),
     SEQ3_2031 GENERATED ALWAYS AS IDENTITY (NO CYCLE)
);

Output Code:

CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ1_2031
COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ1_2031';

CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ2_2031
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ2_2031';

CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ3_2031
COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ3_2031';

/*** MSC-WARNING - MSCEWI2015 - SET TABLE FUNCTIONALITY NOT SUPPORTED ***/
CREATE TABLE PUBLIC.T_2031 (
SEQ1_2031 INTEGER DEFAULT PUBLIC.T_2031_SEQ1_2031.NEXTVAL NOT NULL /*** MSC-WARNING - MSCEWI2040 - FORMAT '--,---,---,--9' NOT SUPPORTED ***/ /*** MSC-WARNING - MSCEWI2031 - SEQUENCE - GENERATED ALWAYS   ***/ ,
SEQ2_2031 INTEGER DEFAULT PUBLIC.T_2031_SEQ2_2031.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS    START WITH 1  INCREMENT BY 1  MINVALUE -2147483647  MAXVALUE 2147483647  NO CYCLE ***/ ,
SEQ3_2031 DEFAULT PUBLIC.T_2031_SEQ3_2031.NEXTVAL /*** MSC-WARNING - MSCEWI2031 - SEQUENCE - GENERATED ALWAYS   NO CYCLE ***/

);

SQL Server Input Code:

CREATE TABLE new_employees  
(  
 id_num int IDENTITY(1,1),  
 fname varchar (20),  
 minit char(1),  
 lname varchar(30)  
); 

Output Code:

CREATE OR REPLACE SEQUENCE PUBLIC.new_employees_id_num
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN PUBLIC.new_employees.id_num';

CREATE OR REPLACE TABLE PUBLIC.new_employees (
id_num INT DEFAULT PUBLIC.new_employees_id_num.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE -  GENERATED BY DEFAULT  START 1 INCREMENT 1 ***/ ,
fname VARCHAR (20),
minit CHAR(1),
lname VARCHAR(30)
);

Observations:

Snowflake has an IDENTITY data type. SnowConvert does not transform IDENTITY columns to this data type because there is no way to update the sequence created by the identity definition. After data is migrated, it might be necessary to update the START WITH value to ensure that old values are not reused that can be already on the source data. In general, the sequence is considered more flexible as identity creates an unalterable sequence behind the scene.

Also, notice that this will affect the database metadata. If a column uses the IDENTITY then the INFORMATION_SCHEMA.COLUMNS reports that columns as IS_IDENTITY when the sequence is used that column will not be reported as such but you can check the sequence usage in COLUMN_DEFAULT.

Recommendations

• No additional user actions are required.

• If you need more support, you can email us at [email protected]