This section explains how to split a data storage range by creating new RDAREAs when a space shortage occurs in the 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 describes an example of the basic split operation (in which the original RDAREA is used).

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 RDAREA (R02) 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).
2. Use ALTER TABLE to specify the new partitioning boundary values. During this process, data in the splitting-target RDAREAs (R02 and RI02) 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 on an RDAREA-by-RDAREA basis.
   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 items of data 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 describes an example of splitting an RDAREA without deleting its data and 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 R03. The example creates in the table a new RDAREA 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 splitting-target boundary value 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.1(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 splitting-target RDAREA is being used for another boundary value (storage range), the data for the other boundary value is also deleted. For this reason, when the splitting-target RDAREA 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 subsection describes an example of the operation when the split-target RDAREA is 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 RDAREA 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 '2002-12-31' or earlier is also unloaded.
2. Use ALTER TABLE to specify the new partitioning boundary values. During this process, data in the splitting-target RDAREAs (R01 and RI01) (including data for '2002-12-31' or earlier) 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 on an RDAREA-by-RDAREA basis. During this process, you must load data into R01 as well because the data in R01 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 items of data 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 when RDAREAs in a contiguous storage have much free space. The post-combination RDAREA might use one of the pre-combination RDAREAs or some other RDAREA.

This subsection describes an example of the operation when one of the pre-combination RDAREAs is used as the post-combination RDAREA.

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 (R03 and R04) 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. Specify WITHOUT PURGE for R02 because this is the RDAREA that will be used again after combining.
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. You must be careful during this process because if you load data in the creation mode, the data in R02 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, then start over from the beginning.
4. Check the number of items of data 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 example combines a data storage range into a single RDAREA because there is a large amount of free space in the RDAREAs for the particular contiguous storage range. One of the pre-combination RDAREAs might or might not be used as the post-combination RDAREA.

This subsection describes an example of the operation when no pre-combination RDAREA is used as the post-combination RDAREA.

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 (R02, R03, and R04) 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 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, on an RDAREA-by-RDAREA basis.
4. Check the number of items of data 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 a combination-target RDAREA is also being used for another boundary value (storage range), the data for the other boundary value is also deleted. Therefore, you must be careful to use the database load utility (pdload) after combining to load the data that was unloaded by the database reorganization utility (pdrorg) before combining into the RDAREA whose data was deleted.

This subsection describes an example of the operation when a combination-target RDAREA is being used for another boundary value.

Note: The numbers in the figure (1 through 4) 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 and save it in a format that can be used as the input to the database load utility (pdload). During this process, data for '2002-12-31' or earlier is also unloaded.
2. Use ALTER TABLE to specify the boundary values to be combined. During this process, data in the RDAREA (R01) being used for the other boundary value is also 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, on an RDAREA-by-RDAREA basis.
4. The data in R01, which is being used for the other boundary value, has been deleted. Therefore, using the unload data file containing the data from R01 as the input, use the database load utility (pdload) to add the data in the addition mode on an RDAREA-by-RDAREA basis.
5. Check the number of items of data after the data has been loaded. For details, see 13.13.9 Checking the number of items of data following splitting or combining.
6. Use ALTER ROUTINE to re-create the routines and triggers that were invalidated in step 2.