SnowConvert AI - IBM DB2 - CREATE TABLE¶

Description¶

The complete CREATE TABLE syntax for IBM DB2 is big enough that it does not fit on one page. However, the following image shows an overview of the syntax with some logical grouping that is later referenced.

Grammar Syntax¶

As Result Table¶

Description¶

Specifies that the columns of the new table have the same name, data type, and optionally same data, as the result from the fullselect.

Warning

AS RESULT TABLE is partially supported in Snowflake. The Copy options do not apply in Snowflake.

Click here to navigate to the IBM DB2 documentation page for this syntax.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable1
AS (SELECT * FROM OriginalTable) WITH NO DATA;

Snowflake¶

CREATE TABLE TestTable1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
AS (SELECT * FROM
  OriginalTable
 LIMIT 0
);

IBM DB2¶

 CREATE TABLE TestTable2
AS (SELECT * FROM OriginalTable) WITH DATA
INCLUDING COLUMN DEFAULTS
INCLUDING IDENTITY;

Snowflake¶

CREATE TABLE TestTable2
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
AS (SELECT * FROM
  OriginalTable
 );

Materialized Query Definition¶

Description¶

Materialized query tables (MQTs) are tables whose definition is based on the result of a query.

Currently, translation for the IBM DB2 Materialized Query is not supported by SnowConvert AI

See the DB2 materialized query definition documentation for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

 CREATE TABLE TestTable4 (ACCTID, LOCID, YEAR, CNT) AS
  (SELECT ACCOUNTID, LOCATIONID, YEAR, COUNT(*)
     FROM TRANS
     GROUP BY ACCOUNTID, LOCATIONID, YEAR )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY SYSTEM
     ENABLE QUERY OPTIMIZATION;

Snowflake

  CREATE TABLE TestTable4 (ACCTID, LOCID, YEAR, CNT) AS
(SELECT ACCOUNTID, LOCATIONID, YEAR,
  COUNT(*)
FROM
  TRANS
GROUP BY ACCOUNTID, LOCATIONID, YEAR )
 !!!RESOLVE EWI!!! /*** SSC-EWI-DB0021 - MATERIALIZED QUERY IS NOT SUPPORTED ***/!!!
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY SYSTEM
ENABLE QUERY OPTIMIZATION
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

SSC-EWI-DB0021: NODE NOT SUPPORTED

Of Type

Description

Specifies that the columns of the table are based on the attributes of the structured type.

Click here to navigate to the IBM DB2 documentation page for this syntax.

TYPED TABLES are not supported in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

    CREATE TABLE TestTable5 OF Student_t UNDER Person
   INHERIT SELECT PRIVILEGES;

Snowflake¶

 !!!RESOLVE EWI!!! /*** SSC-EWI-DB0017 - TYPED TABLES ARE NOT SUPPORTED ***/!!!

CREATE TABLE TestTable5 OF Student_t UNDER Person
   INHERIT SELECT PRIVILEGES;

Related EWIs¶

SSC-EWI-DB0017: NODE NOT SUPPORTED

Staging Table Definition¶

Description¶

A staging table allows incremental maintenance support for deferred materialized query table.

STAGING TABLES are not supported in Snowflake.

See the DB2 staging data tables or the staging table definition syntax in the IBM DB2 documentation.

Grammar Syntax

Sample Source Patterns

IBM DB2

create table TestTable6 for emp_summary propagate immediate;

Snowflake

 !!!RESOLVE EWI!!! /*** SSC-EWI-DB0018 - STAGING TABLES ARE NOT SUPPORTED ***/!!!
create table TestTable6 for emp_summary propagate immediate;

Related EWIs

SSC-EWI-DB0018: NODE NOT SUPPORTED

Element List

Check Constraint

Description

Constraints are used to specify rules for the data in a table.

See the DB2 column options documentation for this syntax.

Warning

Some CONSTRAINT options are migrated as is to Snowflake but some of them are removed because of platform differences. Check the code example to learn more.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable7(
    COL1 VARCHAR(1),
    CONSTRAINT CN1 CHECK(COL1<1),
    CONSTRAINT CN2 CHECK(SOMENAME DETERMINED BY OTHERNAME), 
    CONSTRAINT CN2 CHECK((SOMENAME1, SOMENAME2) DETERMINED BY (SOMENAME3, SOMENAME4))
    );

Snowflake¶

CREATE TABLE TestTable7 (
    COL1 VARCHAR(1),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
    CONSTRAINT CN1 CHECK(COL1<1),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
    CONSTRAINT CN2 CHECK(SOMENAME DETERMINED BY OTHERNAME),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!
    CONSTRAINT CN2 CHECK((SOMENAME1, SOMENAME2) DETERMINED BY (SOMENAME3, SOMENAME4))
    )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Related EWIs¶

SSC-EWI-0035: Check Statement Not Supported.

Period Definition¶

Description¶

Defines a period of time in which the data of a row is valid.

Warning

PERIOD-DEFINITION does not have a functional equivalent in Snowflake.

Note

Snowflake allows the storage of historical table data for up to 90 days, to know more about this see Understanding & Using Time Travel.

Click here to navigate to the IBM DB2 documentation page for this syntax.

Grammar Syntax¶

Sample Source Patterns¶

CREATE TABLE TestTable8(
COL1 DATE,
COL2 DATE,
PERIOD SYSTEM_TIME (COL1, COL2));
)

CREATE TABLE TestTable8 (
COL1 DATE,
    COL2 DATE,
    !!!RESOLVE EWI!!! /*** SSC-EWI-DB0003 - PERIOD SPECIFICATION IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
    PERIOD SYSTEM_TIME (COL1, COL2))
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';
 CREATE OR REPLACE TABLE TestTable9 (
    COL1 VARCHAR(1)
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
 ;

Related EWIs¶

SSC-EWI-DB0003: Period definition is not applicable in Snowflake.

Referential Constraint¶

Description¶

Foreign Key Constraints are migrated through ALTER TABLE statements to remove dependencies at the table creation time and therefore facilitate database deployment.

See the DB2 column options documentation for this syntax.

Grammar Syntax¶

Sample Source Patterns¶

CREATE TABLE TestTable9(
    COL1 VARCHAR(1),
    CONSTRAINT FKCOL1 FOREIGN KEY (COL1) REFERENCES T1,
    CONSTRAINT FKCOL2 FOREIGN KEY (COL1) REFERENCES T1(COL1),
    CONSTRAINT FKCOL3 FOREIGN KEY (COL1) REFERENCES T1(COL1) ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT FKCOL4 FOREIGN KEY (COL1) REFERENCES T1(COL1) ENFORCED DISABLE QUERY OPTIMIZATION,
    FOREIGN KEY (COL1) REFERENCES T1
);

 CREATE OR REPLACE TABLE TestTable9 (
    COL1 VARCHAR(1)
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
 ;
 
 ALTER TABLE TestTable9
 ADD
    CONSTRAINT FKCOL1 FOREIGN KEY (COL1) REFERENCES T1 ;
 
 ALTER TABLE TestTable9
 ADD
    CONSTRAINT FKCOL2 FOREIGN KEY (COL1) REFERENCES T1 (COL1) ;
 
 ALTER TABLE TestTable9
 ADD
    CONSTRAINT FKCOL3 FOREIGN KEY (COL1) REFERENCES T1 (COL1) ON DELETE CASCADE ON UPDATE NO ACTION;
 
 ALTER TABLE TestTable9
 ADD
    CONSTRAINT FKCOL4 FOREIGN KEY (COL1) REFERENCES T1 (COL1) ENFORCED;
 
 ALTER TABLE TestTable9
 ADD CONSTRAINT TestTable9_COL1_T1
    FOREIGN KEY (COL1) REFERENCES T1 ;

QUERY OPTIMIZATION¶

Description¶

Specifies whether the constraint or functional dependency can be used for query optimization under appropriate circumstances.

See the DB2 WITHOUT OVERLAPS documentation for this syntax.

Warning

ENABLE QUERY OPTIMIZATION Constraint attributes are removed because they are not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable11
(
COL1 VARCHAR(10),
COL2 VARCHAR(10),
CONSTRAINT ConstraintName UNIQUE (COL1, COL2) ENABLE QUERY OPTIMIZATION
);

Snowflake¶

CREATE TABLE TestTable11
(
COL1 VARCHAR(10),
    COL2 VARCHAR(10),
    CONSTRAINT ConstraintName UNIQUE (COL1, COL2)
    )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

WITHOUT OVERLAPS¶

Description¶

BUSINESS_TIME WITHOUT OVERLAPS means that for the other specified keys, the values are unique with respect to time for the BUSINESS_TIME period

See the DB2 column options documentation for this syntax.

Warning

BUSINESS_TIME WITHOUT OVERLAPS Constraint attribute is removed because they are not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable12
(
COL1 VARCHAR(10),
CONSTRAINT ConstraintName UNIQUE (COL1, COL2, BUSINESS_TIME WITHOUT OVERLAPS)
);

Snowflake¶

 CREATE TABLE TestTable12
(
COL1 VARCHAR(10),
    CONSTRAINT ConstraintName UNIQUE (COL1, COL2)
    )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Column Options¶

COMPRESS¶

Description¶

Specifies that system default values are to be stored using minimal space.

Click here to navigate to the IBM DB2 documentation page for this syntax.

Warning

COMPRESS SYSTEM DEFAULT is removed because it is not applicable in Snowflake

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable13
(
COL1 VARCHAR(10) COMPRESS SYSTEM DEFAULT
);

Snowflake¶

 CREATE TABLE TestTable13
(
COL1 VARCHAR(10)
)
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Known issues¶

There are no known issues.

HIDDEN¶

Description¶

Specifies whether the column is to be defined as hidden. The hidden attribute determines whether the column is included in an implicit reference to the table, or whether it can be explicitly referenced in SQL statements.

See the DB2 NOT HIDDEN documentation for this syntax.

Warning

HIDDEN Option is removed because it is not applicable in Snowflake

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable14
(
COL1 VARCHAR(10) IMPLICITLY HIDDEN
);

Snowflake¶

 CREATE TABLE TestTable14
(
COL1 VARCHAR(10)
)
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

INLINE LENGTH¶

Description¶

Identifies the Inline Length of the reference type column.

See the DB2 INLINE LENGTH documentation for this syntax

Warning

INLINE LENGTH is removed because it is not applicable in Snowflake.

Grammar Syntax¶

CREATE TABLE T1
(
COL1 VARCHAR(10) INLINE LENGTH 1024
);

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable15
(
COL1 VARCHAR(10) INLINE LENGTH 1024
);

Snowflake¶

 CREATE TABLE TestTable15
(
COL1 VARCHAR(10)
)
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Known issues¶

There are no known issues.

LOB OPTIONS¶

Description¶

Options for the LOB (Large Object Binary) data types

Click here to navigate to the IBM DB2 documentation page for this syntax.

Warning

LOB OPTIONS are removed because they are not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable16
(
COL1 VARCHAR(10) LOGGED,
COL2 VARCHAR(10) NOT LOGGED,
COL3 VARCHAR(10) COMPACT,
COL4 VARCHAR(10) NOT COMPACT
)

Snowflake¶

 CREATE TABLE TestTable16
(
COL1 VARCHAR(10),
    COL2 VARCHAR(10),
    COL3 VARCHAR(10),
    COL4 VARCHAR(10)
    )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

SCOPE¶

Description¶

Identifies the scope of the reference type column.

For this syntax, see the IBM CREATE TABLE statement documentation.

Warning

SCOPE options are removed because they are not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

 CREATE TABLE TestTable17
(
COL1 VARCHAR(10) SCOPE TABLE2,
COL2 VARCHAR(10) SCOPE VIEW1
);

Snowflake¶

 CREATE TABLE TestTable17
(
COL1 VARCHAR(10),
    COL2 VARCHAR(10)
    )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

SECURED¶

Description¶

Identifies a security label that exists for the security policy that is associated with the table.

See the DB2 security label documentation for this syntax.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable18
(
COL1 VARCHAR(10) COLUMN SECURED WITH securityLabel,
COL2 VARCHAR(10) COLUMN SECURED WITH securityLabel
);

Snowflake¶

 CREATE TABLE TestTable18
(
COL1 VARCHAR(10),
    COL2 VARCHAR(10)
    )
 WITH ROW ACCESS POLICY securityLabel ON (
    COL1,
    COL2
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Known issues¶

If multiple security labels are declared an SSC-EWI-DB0001will appear in the Snowflake output code as shown below

IBM DB2¶

CREATE TABLE TestTable19
(
COL1 VARCHAR(10) COLUMN SECURED WITH securityLabel1,
COL2 VARCHAR(10) COLUMN SECURED WITH securityLabel2
);

Snowflake¶

CREATE TABLE TestTable19
(
COL1 VARCHAR(10),
    COL2 VARCHAR(10)
    )
 WITH ROW ACCESS POLICY securityLabel1 ON (
    COL1
 )
 !!!RESOLVE EWI!!! /*** SSC-EWI-DB0001 - WITH ROW ACCESS POLICY CLAUSE DOES NOT SUPPORT MULTIPLE DECLARATION IN SNOWFLAKE ***/!!!
 WITH ROW ACCESS POLICY securityLabel2 ON (
    COL2
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Related EWIs¶

SSC-EWI-DB0001Multiple Row Access policies

Table Options¶

CCSID¶

Description¶

Specifies the encoding scheme for string data that is stored in the table.

Click here to navigate to the IBM DB2 documentation page for this syntax.

Warning

CCSID is not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable20 (
COL1 INT
) CCSID ASCII;

Snowflake¶

 CREATE TABLE TestTable20 (
COL1 INT
)
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
-- CCSID ASCII
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Related EWIs¶

SSC-FDM-0027: REMOVED STATEMENT, NOT APPLICABLE IN SNOWFLAKE.

Compression Options¶

Description¶

Specifies whether row compression is to be used for the table.

Click here to navigate to the IBM DB2 documentation page for this syntax.

Warning

The Compression Options are not applicable in Snowflake.

Grammar Syntax¶

Sample Source Patterns¶

IBM DB2¶

CREATE TABLE TestTable21_01 (
COl1 INT,
COL2 INT
) 
COMPRESS YES
;


CREATE TABLE TestTable21_02 (
COl1 INT,
COL2 INT
) 
COMPRESS YES ADAPTIVE
;

CREATE TABLE TestTable21_03 (
COl1 INT,
COL2 INT
) 
COMPRESS YES STATIC
;


CREATE TABLE TestTable21_04 (
COl1 INT,
COL2 INT
) 
COMPRESS NO
;


CREATE TABLE TestTable21_05 (
COl1 INT,
COL2 INT
) 
VALUE COMPRESSION
;

Snowflake¶

CREATE TABLE TestTable21_01 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--COMPRESS YES
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
;


CREATE TABLE TestTable21_02 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--COMPRESS YES ADAPTIVE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
;

CREATE TABLE TestTable21_03 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--COMPRESS YES STATIC
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
;


CREATE TABLE TestTable21_04 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--COMPRESS NO
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
;


CREATE TABLE TestTable21_05 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--VALUE COMPRESSION
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}'
;

Related EWIs¶

SSC-FDM-0027: REMOVED STATEMENT, NOT APPLICABLE IN SNOWFLAKE.

Data Capture¶

Description¶

Indicates whether extra information for inter-database data replication is to be written to the log.

Click here to navigate to the IBM DB2 documentation page for this syntax.

DATA CAPTURE is not supported

Grammar Syntax

Sample Source Patterns

IBM DB2

 CREATE TABLE TestTable22
(
	COL1 INT
) DATA CAPTURE CHANGES;

Snowflake

 CREATE TABLE TestTable22
(
	COL1 INT
)
!!!RESOLVE EWI!!! /*** SSC-EWI-DB0020 - DATA CAPTURE IS NOT SUPPORTED ***/!!!
 DATA CAPTURE CHANGES
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Related EWIs

SSC-EWI-DB0020: NODE NOT SUPPORTED

REMOVED CLAUSES

Description

The following clauses are removed in SnowConvert AI since they are not applicable in Snowflake:

Distribution Clause
Not Logged Initially Clause
Options Clause
Organize by Clause
Partition by Clause
Security Policy Clause
In Clause
Long In Clause
Index In Clause
With Restrict On Clause

Sample Source Patterns

IBM DB2

-- Distribution Clause
 CREATE TABLE TestTable23
(
	COL1 INT
) DISTRIBUTE BY REPLICATION;

-- Not Logged Initially Clause
 CREATE TABLE TestTable24 (
COL1 INT
) NOT LOGGED INITIALLY;

-- Options Clause
 CREATE TABLE TestTable25 (
COL1 INT
) OPTIONS(tableOptionName 'stringConst', tableOptionName2 'stringConst');

-- Organize By Clause
 CREATE TABLE TestTable26
(
	COL1 INT,
	COL2 INT,
	COL3 INT
) ORGANIZE BY ROW;

-- Partition By Clause
 CREATE TABLE TestTable27_01 (
COl1 INT,
COL2 INT
) 
PARTITION BY RANGE (COL1 NULLS LAST, COL2 NULLS FIRST) 
(PARTITION partitionName STARTING FROM (MINVALUE, MAXVALUE, 3) EXCLUSIVE ENDING AT MAXVALUE EXCLUSIVE IN tablespaceName INDEX IN tablespaceName LONG IN tablespaceName);

-- Partition By Clause
CREATE TABLE TestTable27_02 (
COl1 INT,
COL2 INT
) PARTITION BY (COL1 NULLS LAST) (STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE IN tablespaceName);

-- Partition By Clause
CREATE TABLE TestTable27_03 (
COL1 INT,
COL2 INT
) PART BY (COL1) (STARTING 1 ENDING 3);

-- Partition By Clause
CREATE TABLE TestTable27_04 (
COL1 INT,
COL2 INT
) PART BY (COL1) (PARTITION 5 STARTING 1 ENDING 3);

-- Partition By Clause
CREATE TABLE TestTable27_05 (
COL1 INT,
COL2 INT
) PARTITION BY (COL1 NULLS LAST) 
(STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE EVERY 3 YEAR);

-- Partition By Clause
CREATE TABLE TestTable27_06 (
COL1 INT,
COL2 INT
) 
PARTITION BY (COL1 NULLS LAST) 
(STARTING MINVALUE INCLUSIVE VALUES 3 EXCLUSIVE);

-- Partition By Clause
CREATE TABLE TestTable27_07 (
JYEARS INT
) 
PARTITION BY RANGE (SKACDY_DAY ASC) 
(
PARTITION 1 ENDING AT ('16.10.2019') HASH SPACE 2G, 
PARTITION 2 ENDING AT ('17.10.2019')
);

-- Partition By Clause
CREATE TABLE TestTable27_08 (
TRANS_DATE DATE NOT NULL
) 
PARTITION BY RANGE ("TRANS_DATE") 
(
PART "PART_2019_03_01" STARTING ('2019-03-01') ENDING ('2019-03-01') IN "SLTPAYMFACTD1903", 
PART "PART_2021_08_19" STARTING ('2021-08-19') ENDING ('2021-08-19') IN "SLTPAYMFACTD2108", 
PARTITION "PART_2021_08_19" STARTING ('2021-08-19') ENDING ('2021-08-19') IN "SLTPAYMFACTD2108"
);

-- Security Policy Clause
 CREATE TABLE TestTable28 (
COL1 INT
) SECURITY POLICY PolicyName;

-- In Clause
 CREATE TABLE TestTable29
(
	COL1 INT
) IN TablescapeName;

-- Long In Clause
 CREATE TABLE TestTable29
(
	COL1 INT
) LONG IN TablespaceName;

-- Index In Clause
 CREATE TABLE TestTable30
(
	COL1 INT
) INDEX IN TablespaceName;

-- With Restrict On Drop Clause
 CREATE TABLE TestTable31 (
COL1 INT
) WITH RESTRICT ON DROP;

Snowflake

 -- Distribution Clause
 CREATE TABLE TestTable23
 (
	COL1 INT
)
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
-- DISTRIBUTE BY REPLICATION
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Not Logged Initially Clause
 CREATE TABLE TestTable24 (
COL1 INT
)
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
-- NOT LOGGED INITIALLY
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Options Clause
 CREATE TABLE TestTable25 (
COL1 INT
)
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
-- OPTIONS(tableOptionName 'stringConst', tableOptionName2 'stringConst')
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Organize By Clause
 CREATE TABLE TestTable26
 (
	COL1 INT,
COL2 INT,
COL3 INT
)
-- --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
-- ORGANIZE BY ROW
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
 CREATE TABLE TestTable27_01 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY RANGE (COL1 NULLS LAST, COL2 NULLS FIRST)
--(PARTITION partitionName STARTING FROM (MINVALUE, MAXVALUE, 3) EXCLUSIVE ENDING AT MAXVALUE EXCLUSIVE IN tablespaceName INDEX IN tablespaceName LONG IN tablespaceName)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_02 (
COl1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY (COL1 NULLS LAST) (STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE IN tablespaceName)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_03 (
COL1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PART BY (COL1) (STARTING 1 ENDING 3)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_04 (
COL1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PART BY (COL1) (PARTITION 5 STARTING 1 ENDING 3)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_05 (
COL1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY (COL1 NULLS LAST)
--(STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE EVERY 3 YEAR)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_06 (
COL1 INT,
COL2 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY (COL1 NULLS LAST)
--(STARTING MINVALUE INCLUSIVE VALUES 3 EXCLUSIVE)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_07 (
JYEARS INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY RANGE (SKACDY_DAY ASC)
--(
--PARTITION 1 ENDING AT ('16.10.2019') HASH SPACE 2G,
--PARTITION 2 ENDING AT ('17.10.2019')
--)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Partition By Clause
CREATE TABLE TestTable27_08 (
TRANS_DATE DATE NOT NULL
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY RANGE ("TRANS_DATE")
--(
--PART "PART_2019_03_01" STARTING ('2019-03-01') ENDING ('2019-03-01') IN "SLTPAYMFACTD1903",
--PART "PART_2021_08_19" STARTING ('2021-08-19') ENDING ('2021-08-19') IN "SLTPAYMFACTD2108",
--PARTITION "PART_2021_08_19" STARTING ('2021-08-19') ENDING ('2021-08-19') IN "SLTPAYMFACTD2108"
--)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Security Policy Clause
 CREATE TABLE TestTable28 (
COL1 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--SECURITY POLICY PolicyName
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- In Clause
 CREATE TABLE TestTable29
(
	COL1 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--IN TablescapeName
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Long In Clause
--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR TestTable29. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
 CREATE TABLE TestTable29
(
	COL1 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--LONG IN TablespaceName
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- Index In Clause
 CREATE TABLE TestTable30
(
	COL1 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--INDEX IN TablespaceName
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

-- With Restrict On Drop Clause
 CREATE TABLE TestTable31 (
COL1 INT
)
----** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--WITH RESTRICT ON DROP
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "db2",  "convertedOn": "09/01/2025",  "domain": "no-domain-provided" }}';

Related EWIs

SSC-FDM-0027: REMOVED STATEMENT, NOT APPLICABLE IN SNOWFLAKE.