MSCEWI1048

Sequence warning.

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 datatype. SnowConvert does not transform IDENTITY columns to this datatype because there is now 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.

  • For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.