Suppose you have the following PPI base table definition.
CREATE SET TABLE sales_table, NO FALLBACK, CHECKSUM = DEFAULT , NO BEFORE JOURNAL, NO AFTER JOURNAL ( product_code CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, sales_date DATE FORMAT 'YYYY-MM-DD' NOT NULL NOT NULL, agent_id CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, quantity_sold INTEGER, product_desc VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX (product_code, sales_date, agent_id) PARTITION BY RANGE_N(sales_date BETWEEN DATE '2001-01-01' AND DATE '2003-12-31' EACH INTERVAL '1' MONTH , NO RANGE);
You then define the following PPI base table to act as the save table for an ALTER TABLE request in which you will drop one or more row partitions from sales_table.
CREATE SET TABLE sales_table_1, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( product_code CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, sales_date DATE FORMAT 'YYYY-MM-DD' NOT NULL, agent_id CHARACTER(8) CHARACTER SET LATIN NOT CASESPECIFIC, quantity_sold INTEGER, product_desc VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL) PRIMARY INDEX (product_code, sales_date, agent_id) PARTITION BY RANGE_N(sales_date BETWEEN DATE '2001-01-01' AND DATE '2001-12-31' EACH INTERVAL '1' MONTH , NO RANGE);
You then populate sales_table with the following rows.
INSERT INTO sales_table VALUES ('PC2',DATE '2001-01-10','AG2',5,'PC'); INSERT INTO sales_table VALUES ('PC3',DATE '2001-03-10','AG2',5,'PC'); INSERT INTO sales_table VALUES ('PC4',DATE '2002-05-10','AG2',5,'PC'); INSERT INTO sales_table VALUES ('PC5',DATE '2003-07-10','AG2',5,'PC'); INSERT INTO sales_table VALUES ('PC5',DATE '2004-07-10','AG2',5,'PC');
The following SELECT request indicates that the 5 intended rows were successfully inserted into sales_table:
SELECT partition, product_code, sales_date, agent_id, quantity_sold, product_description FROM sales_table ORDER BY 1;
Result:
PARTITION product_code sales_date agent_id quantity_sold product_desc --------- ------------ ---------- -------- ------------- ------------ 1 PC2 2001-01-10 AG2 5 PC 3 PC3 2001-03-10 AG2 5 PC 17 PC4 2002-05-10 AG2 5 PC 31 PC5 2003-07-10 AG2 5 PC 37 PC5 2004-07-10 AG2 5 PC
Use an ALTER TABLE MODIFY request to drop a range of row partitions from sales_table with the intent of saving any deleted rows in sales_table1, by specifying a WITH INSERT INTO sales_table1 clause.
ALTER TABLE sales_table MODIFY DROP RANGE BETWEEN DATE '2001-01-01' AND DATE '2001-12-31' WITH INSERT INTO sales_table1;
Result:
*** Table has been modified. *** Total elapsed time was 1 second.
Select the contents of sales_table to check whether the rows within the row partition you dropped were deleted from the table.
SELECT PARTITION, product_code, sales_date, agent_id, quantity_sold, product_desc FROM sales_table ORDER BY 1;
Result:
*** Query completed. 5 rows found. 6 columns returned. *** Total elapsed time was 1 second. PARTITION product_code sales_date agent_id quantity_sold product_desc --------- ------------ ---------- -------- ------------- ------------ 5 PC4 2002-05-10 AG2 5 PC 19 PC5 2003-07-10 AG2 5 PC 25 PC2 2001-01-10 AG2 5 PC<<<<< 25 PC3 2001-03-10 AG2 5 PC<<<<< 25 PC5 2004-07-10 AG2 5 PC<<<<<
Because the base table sales_table includes a NO RANGE partition, rows are not deleted when you drop the range of row partitions. Those rows are moved to the NO RANGE partition, which is identified by partition number 25, and retained in sales_table.
To do what you had intended, you should have either defined sales_table without specifying a NO RANGE partition or deleted the rows before submitting the ALTER TABLE request.
Now assume that you create a column-partitioned version of sales_table, sales_table_cp, in another database, and also assume that you create a column-partitioned version of sales_table1, sales_table1_cp, in the same database. You submit an ALTER TABLE MODIFY request to drop a range of partitions from sales_table_cp with the intent of saving any dropped rows in sales_table1_cp, specifying a WITH INSERT INTO sales_table1_cp clause to do so.
ALTER TABLE sales_table_cp MODIFY DROP RANGE BETWEEN DATE '2001-01-01' AND DATE '2001-12-31' WITH INSERT INTO sales_table1_cp;
Result:
*** Table has been modified. *** Total elapsed time was 1 second.
Submit the same SELECT PARTITION request that you used for the PPI version of sales_table (except retrieve the rows from sales_table1_cp rather than sales_table1 ) to check whether the rows within the partition that was dropped were deleted from the table, and the request returns the identical result set.
SELECT PARTITION, product_code, sales_date, agent_id, quantity_sold, product_desc FROM sales_table_cp ORDER BY 1;