Migrating existing tables and views to partitioned tables

You can migrate a nonpartitioned table or a UNION ALL view to an empty partitioned table.

Before you begin

Attaching a data partition is not allowed if SYSCAT.COLUMNS.IMPLICITVALUE for a specific column is a nonnull value for both the source column and the target column, and the values do not match. In this case, you must drop the source table and then recreate it.

A column can have a nonnull value in the SYSCAT.COLUMNS IMPLICITVALUE field if any one of the following conditions is met:

The IMPLICITVALUE field is propagated from a source table during an attach operation.
The IMPLICITVALUE field is inherited from a source table during a detach operation.
The IMPLICITVALUE field is set during migration from V8 to V9, where it is determined to be an added column, or might be an added column. An added column is a column that is created as the result of an ALTER TABLE...ADD COLUMN statement.

Always create the source and target tables involved in an attach operation with the same columns defined. In particular, never use the ALTER TABLE statement to add columns to the target table of an attach operation.

For advice on avoiding a mismatch when working with partitioned tables, see Guidelines for attaching data partitions to partitioned tables.

About this task

When migrating regular tables, unload the source table by using the EXPORT command or high performance unload. Create a new, empty partitioned table, and use the LOAD command to populate that partitioned table. To move the data from the old table directly into the partitioned table without any intermediate steps, use the LOAD FROM CURSOR command (see Step 1.

You can convert nonpartitioned data in a UNION ALL view to a partitioned table (see Step 2). UNION ALL views are used to manage large tables and achieve easy roll-in and roll-out of table data while providing the performance advantages of branch elimination. Using the ALTER TABLE...ATTACH PARTITION statement, you can achieve conversion with no movement of data in the base table. Nonpartitioned indexes and dependent views or materialized query tables (MQTs) must be recreated after the conversion. The recommended strategy to convert UNION ALL views to partitioned tables is to create a partitioned table with a single dummy data partition, then attach all of the tables of the union all view. Be sure to drop the dummy data partition early in the process to avoid problems with overlapping ranges.

Procedure

Migrate a regular table to a partitioned table.
Use the LOAD FROM CURSOR command to avoid any intermediate steps. The following example shows how to migrate table T1 to the SALES_DP table.
1. Create and populate a regular table T1.
```
CREATE TABLE t1 (c1 int, c2 int);
INSERT INTO t1 VALUES (0,1), (4, 2), (6, 3);
```
2. Create an empty partitioned table.
```
CREATE TABLE sales_dp (c1 int, c2 int)
  PARTITION BY RANGE (c1)
  (STARTING FROM 0 ENDING AT 10 EVERY 2); 
```
3. Use the LOAD FROM CURSOR command to pull the data from an SQL query directly into the new partitioned table.
```
   SELECT * FROM t1;
   DECLARE c1 CURSOR FOR SELECT * FROM t1; 
   LOAD FROM c1 of CURSOR INSERT INTO sales_dp;SELECT * FROM sales_dp;
```
Convert nonpartitioned data in a UNION ALL view to a partitioned table.
The following example shows how to convert the UNION ALL view named ALL_SALES to the SALES_DP table.
1. Create the UNION ALL view.
```
	CREATE VIEW all_sales AS
		(
		SELECT * FROM sales_0198
		WHERE sales_date BETWEEN '01-01-1998' AND '01-31-1998'
		UNION ALL
		SELECT * FROM sales_0298
		WHERE sales_date BETWEEN '02-01-1998' AND '02-28-1998'
		UNION ALL
		...
		UNION ALL
		SELECT * FROM sales_1200
		WHERE sales_date BETWEEN '12-01-2000' AND '12-31-2000'
		);
```
2. Create a partitioned table with a single dummy partition. Choose the range so that it does not overlap with the first data partition to be attached.
```
	CREATE TABLE sales_dp (
		sales_date DATE NOT NULL,
		prod_id INTEGER,
		city_id INTEGER,
		channel_id INTEGER,
		revenue DECIMAL(20,2))
		PARTITION BY RANGE (sales_date)
		(PART dummy STARTING FROM '01-01-1900' ENDING AT '01-01-1900');
```
3. Attach the first table.
```
ALTER TABLE sales_dp ATTACH PARTITION
  STARTING FROM '01-01-1998' ENDING AT '01-31-1998'
  FROM sales_0198;
```
4. Drop the dummy partition.
```
ALTER TABLE sales_dp DETACH PARTITION dummy
  INTO dummy;
DROP TABLE dummy;
```
5. Attach the remaining partitions.
```
ALTER TABLE sales_dp ATTACH PARTITION
  STARTING FROM '02-01-1998' ENDING AT '02-28-1998'
  FROM sales_0298;
...
ALTER TABLE sales_dp ATTACH PARTITION
  STARTING FROM '12-01-2000' ENDING AT '12-31-2000'
  FROM sales_1200;
```
6. Issue the SET INTEGRITY statement to make data in the newly attached partition accessible to queries.
```
SET INTEGRITY FOR sales_dp IMMEDIATE CHECKED
FOR EXCEPTION IN sales_dp USE sales_ex;
```
  Tip: If data integrity checking, including range validation and other constraints checking, can be done through application logic that is independent of the data server before an attach operation, newly attached data can be made available for use much sooner. You can optimize the data roll-in process by using the SET INTEGRITY...ALL IMMEDIATE UNCHECKED statement to skip range and constraints violation checking. In this case, the table is brought out of SET INTEGRITY pending state, and the new data is available for applications to use immediately, as long as there are no nonpartitioned user indexes on the target table.
7. Create indexes, as appropriate.