1

Can we create a table which is range partitioned where partition keys are not in order? For eg.

PARTITION 00001 ENDING AT ('2018-07-02') INCLUSIVE 

PARTITION 00002 ENDING AT ('2018-07-03') INCLUSIVE 

PARTITION 00003 ENDING AT ('2018-07-08') INCLUSIVE 

PARTITION 00004 ENDING AT ('2018-07-05') INCLUSIVE 

PARTITION 00005 ENDING AT ('2018-07-20') INCLUSIVE

is this a valid DDL for creating partitioned table in DB2?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Tejasvi Rao
  • 53
  • 1
  • 6

1 Answers1

2

No, partitions must be "in order". See here for more detail (search for partition-element). I've highlighted the relevant part.

The key values are subject to the following rules:

  • The first value corresponds to the first column of the key, the second value to the second column, and so on. Using fewer values than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.

  • The highest value of the key in any partition must be lower than the highest value of the key in the next partition for ascending cases.

  • The values specified for the last partition are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. Any key values greater than the value specified for the last partition are out of range.

  • If the concatenation of all the values exceeds 255 bytes, only the first 255 bytes are considered.

  • If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, 17 bytes of the constant that is specified for the corresponding ROWID column are considered.

  • If a null value is specified for the partitioning key and the key is ascending, an error is returned unless MAXVALUE is specified. If the key is descending, an error is returned unless MINVALUE is specified.
bhamby
  • 15,112
  • 1
  • 45
  • 66