This section explains how to split a data storage range by creating new RDAREAs when a space shortage occurs in an RDAREA for a given storage range. You might use the original RDAREAs as post-splitting RDAREAs, or you might create completely new RDAREAs. This subsection provides an example of the basic split operation for a matrix-partitioned table (in which the original RDAREA is used). In this example, because a space shortage has occurred in RD13, the storage range for the second-dimension RD13 is split, and the post-split storage ranges are stored in RD13 and in a newly created RDAREA (RD14).

Note: The numbers in the figure (1 through 3) correspond to the numbers in the following procedure.

Procedure

1. Use the database reorganization utility (pdrorg) to unload the table data from the RDAREAs (RD13 and RD23) whose storage condition is to be changed. Save the data in a format that can be used as the input to the database load utility (pdload).
2. Use ALTER TABLE to specify new partitioning boundary values. During this process, data in the RDAREAs (RD13 and RD23) is deleted.
3. Using the unload data file created in step 1 as the input file, execute the database load utility (pdload) to load data in the addition mode into the split RDAREAs for storing the partitioned boundary values, one RDAREA at a time.
   Because the unload data file contains data that does not match the split storage conditions, data error information is output. If you do not need the data error information, you can suppress its output by specifying divermsg=off in the option statement.
   
4. Check the number of data items after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
5. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.

This section explains how to create new RDAREAs for future use for data in RDAREAs that increase in size over time. In this case, it is assumed that the RDAREA to be split does not store data with a larger value than the post-splitting boundary value.

This subsection provides an example of splitting an RDAREA without deleting its data, and then using the pre-split RDAREA after splitting. In this example, the registration date is used as the partitioning key, and it is assumed that no data for 2005 and beyond is stored in RD13 or RD23. The example splits the storage range for the second-dimension RD13, and creates new RDAREAs (RD14 and RD24) for storing data for 2005 and beyond.

Note: The numbers in the figure (1 and 2) correspond to the numbers in the following procedure.

Procedure

1. Confirm that the RDAREA containing the boundary value to be split contains only data for the post-splitting storage condition (SELECT MAX(partitioning-key-column-name) FROM table-name WHERE partitioning-key-column-name > post-splitting-boundary-value). If data other than data that satisfies the post-splitting storage condition (data for 2005 and beyond) is present, use the procedure in 13.13.3(1) Example 1 (basic split operation) above.
   If you do not execute this step and data not satisfying the post-splitting storage condition remains, you can implement a recovery procedure. For details about this procedure, see 13.13.10(2) Recovery procedure when data not satisfying the post-splitting storage condition remains.
   
2. Use ALTER TABLE to specify the new partitioning boundary values. For this step, specify WITHOUT PURGE.
3. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.

If the RDAREA to be split is being used for another boundary value, the data for the other boundary value is also deleted. For this reason, when the RDAREA to be split is being used for another boundary value, you must be careful when you use the database load utility (pdload) to load the data that was unloaded by the database reorganization utility (pdrorg) before splitting. This example splits the first-dimension boundary value of an RDAREA (RD23) being used for another boundary value.

Note: The numbers in the figure (1 through 3) correspond to the numbers in the following procedure.

Procedure

1. Use the database reorganization utility (pdrorg) to unload the table data from the RDAREAs (RD21, RD22, and RD23) whose storage condition is to be changed, and save it in a format that can be used as the input to the database load utility (pdload). During this process, data for 2000 or earlier is also unloaded.
2. Use ALTER TABLE to specify the new partitioning boundary values. During this process, data in the RDAREAs to be split (RD21, RD22, and RD23) (including data for other storage ranges in RD23) is deleted.
3. Using the unload data file created in step 1 as the input file, execute the database load utility (pdload) to load data in the addition mode to the split RDAREAs for storing the partitioned boundary values, one RDAREA at a time. During this process, you must load data to RD23 as well because the data in RD23 was also deleted in step 2.
   Because the unload data file contains data that does not match the split storage conditions, data error information is output. If you do not need the data error information, you can suppress its output by specifying divermsg=off in the option statement.
   
4. Check the number of data items after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
5. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.

This section explains how to combine data storage ranges into a single RDAREA group storing specific boundary values, when RDAREA groups storing specific boundary values in contiguous storage have ample free space. The post-combination RDAREA might either use one of the pre-combination RDAREAs, or some other RDAREA. In this example, the second-dimension storage conditions that have been split into RD12, RD13, RD22, and RD23 are combined into RD12 and RD22.

Note: The numbers in the figure (1 through 3) correspond to the numbers in the following procedure.

Procedure

1. Use the database reorganization utility (pdrorg) to unload the table data from all combination-target RDAREAs that will not be used after combining (RD13 and RD23). Save the data in a format that can be used as the input to the database load utility (pdload).
2. Use ALTER TABLE to specify the boundary values to be combined. Specify WITHOUT PURGE for RD12 and RD22, because these are the RDAREAs that will be used again after combining.
3. Using all unload data files created in step 1 as input files, execute the database load utility (pdload) to load data in the addition mode into the combined RDAREA for storing the combined boundary values. Use care during this process, because if you load data in the creation mode, the data in RD12 and RD22 before combining will be deleted. If you load data in the creation mode by mistake, use a backup to restore the system to its status before you executed ALTER TABLE, and then start over from step 1.
4. Check the number of data items after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
5. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.

In this example, the second-dimension storage conditions that were split into RD12, RD13, RD22, and RD23 are combined into the newly-created RD15 and RD25.

Note: The numbers in the figure (1 through 3) correspond to the numbers in the following procedure.

Procedure

1. Use the database reorganization utility (pdrorg) to unload the table data from all combination-target RDAREAs (RD12, RD13, RD22, and RD23), and save it in a format that can be used as the input to the database load utility (pdload).
2. Use ALTER TABLE to specify the boundary values to be combined.
3. Using all the unload data files created in step 1 as input files, execute the database load utility (pdload) to load data in the addition mode into the combined RDAREA for storing the combined boundary values, one RDAREA at a time.
4. Check the number of data items after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
5. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.

If an RDAREA to be combined is also being used for another boundary value, the data for the other boundary value is also deleted. After combining, you must therefore be careful to use the database load utility (pdload) to load the data that was unloaded by the database reorganization utility (pdrorg) before combining into the RDAREA whose data was deleted. This example combines the second-dimension boundary value of two RDAREAs (RD11 and RD21) being used for another boundary value.

Note: The numbers in the figure (1 through 3) correspond to the numbers in the following procedure.

Procedure

1. Use the database reorganization utility (pdrorg) to unload the table data from all RDAREAs to be combined (RD11, RD12, RD22, and RD23), and save it in a format that can be used as the input to the database load utility (pdload). During this process, data for 2000 or earlier is also unloaded.
2. Use ALTER TABLE to specify the boundary values to be combined. During this process, data in the RDAREAs to be combined (as well as data in other storage ranges in RD11 and RD21) is deleted.
3. Using all unload data files created in step 1 as the input files, execute the database load utility (pdload) to load data in the addition mode into the combined RDAREA for storing the combined boundary values, one RDAREA at a time. Because the data in RD11 and RD21 was also deleted in step 2, use the addition mode to load data in RD11 and RD21 as well.
4. Check the number of data items after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
5. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.