Using CREATE OR REPLACE TABLE
The OR REPLACE option on the CREATE TABLE statement can be used to change an existing table definition.
Using CREATE OR REPLACE TABLE lets you consolidate the master definition of a table into one statement. You do not need to maintain the source for the original CREATE TABLE statement plus a complex list of ALTER TABLE statements needed to recreate the most current version of a table. This CREATE TABLE statement can be executed to deploy the current definition of the table either as a new table or to replace a prior version of the table.
There are options to either keep the existing data in the table or to clear the data from the table during the replace. The default is to keep all data. If you elect to clear all the data, your new table definition does not need to be compatible with the original version. In all cases, other objects that depend on the table, such as referential constraints, triggers, and views, must remain satisfied or the replace will fail.
CREATE TABLE INVENTORY
(PARTNO SMALLINT NOT NULL,
DESCR VARCHAR(24),
QONHAND INT,
PRIMARY KEY(PARTNO))
Perhaps over time, you
have updated the column names to be more descriptive, changed the
DESCR column to be a longer Unicode column, and added a timestamp
column for when the row was last updated. The following statement
reflects all of these changes and can be executed against any prior
version of the table, as long as the column names can be matched to
the prior column names and the data types are compatible.CREATE OR REPLACE TABLE INVENTORY
(PART_NUMBER FOR PARTNO SMALLINT NOT NULL,
DESCRIPTION FOR DESCR VARGRAPHIC(500) CCSID 1200,
QUANTITY_ON_HAND FOR QONHAND INT,
LAST_MODIFIED FOR MODIFIED TIMESTAMP
NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
PRIMARY KEY(PARTNO))
Suppose your original table was defined to have 3 partitions
CREATE TABLE PARTITIONED
(KEYFLD BIGINT,
DATAFLD VARCHAR(200))
PARTITION BY RANGE (KEYFLD)
(PARTITION FIRST STARTING 0 ENDING 100,
PARTITION SECOND STARTING 100 EXCLUSIVE ENDING 200,
PARTITION THIRD STARTING 200 EXCLUSIVE ENDING 300)
To
break the second partition into 3 pieces, modify the original CREATE
TABLE statement to redefine the partitions.CREATE OR REPLACE TABLE PARTITIONED
(KEYFLD BIGINT,
DATAFLD VARCHAR(200))
PARTITION BY RANGE (KEYFLD)
(PARTITION FIRST STARTING 0 ENDING 100,
PARTITION SECOND STARTING 100 EXCLUSIVE ENDING 150,
PARTITION SPLIT1 STARTING 151 EXCLUSIVE ENDING 175,
PARTITION SPLIT2 STARTING 176 EXCLUSIVE ENDING 200,
PARTITION THIRD STARTING 200 EXCLUSIVE ENDING 300)
Now
the table will have 5 partitions with the data spread among them according
to the new definition.This example uses the default of ON REPLACE PRESERVE ALL ROWS. That means that all data for all rows is guaranteed to be kept. If data from an existing partition doesn't fit in any new partition, the statement fails. To remove a partition and the data from that partition, omit the partition definition from the CREATE OR REPLACE TABLE statement and use ON REPLACE PRESERVE ROWS. This will preserve all the data that can be assigned to the remaining partitions and discard any that no longer has a defined partition.