Partitioning Clause

Description

Specifies how the data is partitioned within a database partition

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

The Partitioning Clause is not applicable in Snowflake.

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

CREATE TABLE T1 (
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);

CREATE TABLE T2 (
COl1 INT,
COL2 INT
) PARTITION BY (COL1 NULLS LAST) (STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE IN tablespaceName);

CREATE TABLE T3 (
COL1 INT,
COL2 INT
) PART BY (COL1) (STARTING 1 ENDING 3);

CREATE TABLE T4 (
COL1 INT,
COL2 INT
) PART BY (COL1) (PARTITION 5 STARTING 1 ENDING 3);

CREATE TABLE T5 (
COL1 INT,
COL2 INT
) PARTITION BY (COL1 NULLS LAST) 
(STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE EVERY 3 YEAR);

CREATE TABLE T6 (
COL1 INT,
COL2 INT
) 
PARTITION BY (COL1 NULLS LAST) 
(STARTING MINVALUE INCLUSIVE VALUES 3 EXCLUSIVE);

CREATE TABLE T7 (
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')
);

CREATE TABLE T8 (
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"
);

Snowflake

Code 1

CREATE TABLE PUBLIC.T1 (
COl1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - 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
--)
 ;

CREATE TABLE PUBLIC.T2 (
COl1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--  PARTITION BY (COL1 NULLS LAST) (STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE IN tablespaceName)
                                                                                                   ;

CREATE TABLE PUBLIC.T3 (
COL1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--  PART BY (COL1) (STARTING 1 ENDING 3)
                                      ;

CREATE TABLE PUBLIC.T4 (
COL1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--  PART BY (COL1) (PARTITION 5 STARTING 1 ENDING 3)
                                                  ;

CREATE TABLE PUBLIC.T5 (
COL1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--  PARTITION BY (COL1 NULLS LAST)
--(STARTING MINVALUE INCLUSIVE ENDING 3 EXCLUSIVE EVERY 3 YEAR)
                                                             ;

CREATE TABLE PUBLIC.T6 (
COL1 INT,
COL2 INT
)
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
--PARTITION BY (COL1 NULLS LAST)
--(STARTING MINVALUE INCLUSIVE VALUES 3 EXCLUSIVE)
                                                ;

CREATE TABLE PUBLIC.T7 (
JYEARS INT
)
-- ** MSC-WARNING - MSCEWI1002 - 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')
--)
 ;

CREATE TABLE PUBLIC.T8 (
TRANS_DATE DATE NOT NULL
)
-- ** MSC-WARNING - MSCEWI1002 - 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"
--)
 ;

Related EWIs

1. MSCEWI1002: REMOVED STATEMENT, NOT APPLICABLE IN SNOWFLAKE.