News
Abstract
Ability to create range partitions out of order
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Database Engineer (DBE) Enhancements > Ability to create range partitions out of order
Using CREATE TABLE to create a range partitioned table used to require the ranges be in order. As partitions were added, the new partition was always the last partition. When Generate SQL was used against the table, the CREATE TABLE statement failed with an SQL0636 failure because the ranges were not "in order".
This enhancement applies to range partitions with a single key. The CREATE TABLE statement now permits unordered range partitions.
Example: Adding partitions and then cloning
create table corpdb.sales_table(trans_date timestamp)
partition by range(trans_date)
(starting('2013-01-01 00:00:00.000000')
ending ('2013-03-31 23:59:59.999999'),
starting('2013-07-01 00:00:00.000000')
ending ('2013-09-30 23:59:59.999999'));
-- Oops, forgot a partition, add it in
alter table corpdb.sales_table add partition
starting('2013-04-01 00:00:00.000000')
ending ('2013-06-30 23:59:59.999999');
Later, to re-create the table or clone the table, it is natural to use the IBM i Access Client Solutions Generate SQL feature to extract the SQL statements. Since range partitions no longer need to be "in order", the following SQL0636 is no longer seen and the table will be created.
Figure 1. The SQL0636 failure that is no longer issued
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
11 April 2021
UID
ibm11168444