13.12.5 Splitting an RDAREA (in the case of boundary value specification)

The split facility splits into multiple RDAREAs the data stored in a specified range of a table that is partitioned by boundary values. The details of the split facility are explained below.

Organization of this subsection
(1) Maximum values
(2) Determining the RDAREA for splitting
(3) Determining the RDAREAs to be used after splitting
(4) Correspondence between a table and RDAREAs other than for the table
(5) Handling the data in an RDAREA to be split

(1) Maximum values

Table 13-7 shows maximum values for the split facility.

Table 13-7 Maximum values for the split facility (in the case of boundary value specification)

ItemMaximum valueWhat happens when the maximum value is exceeded
Number of RDAREAs that can be split1Causes an error in ALTER TABLE.
Number of RDAREAs into which an RDAREA can be split in a single operation16
Total number of RDAREAs following splitting1024
Total number of specified boundary values following splitting (including other)3000

(2) Determining the RDAREA for splitting

The RDAREA that satisfies a specified storage condition is selected for splitting, based on a partitioning boundary value that is specified in CHANGE RDAREA of the ALTER TABLE definition SQL. Because specification of a boundary value identifies the target RDAREA, there is no need to specify the RDAREA itself. Table 13-8 shows the specification of ALTER TABLE and how the RDAREA to be split is determined.

Table 13-8 ALTER TABLE specification and determination of RDAREA to be split

SpecificationConditionCan be specified?Determination of RDAREA to be split
Boundary valueThe specified boundary value is specified in the table definitionYRDAREA that stores the specified boundary value range becomes the target for splitting.
The specified boundary value is not specified in the table definitionN[Figure]
'MAX'[Figure]YRDAREA that stores partitioning key values that are greater than the maximum boundary value becomes the target for splitting.
Legend:
Y: Can be specified.
N: Cannot be specified.
[Figure]: Not applicable.

(3) Determining the RDAREAs to be used after splitting

The RDAREAs to be used for storing data after splitting are determined on the basis of the post-splitting boundary values and RDAREAs specified in CHANGE RDAREA of ALTER TABLE. Note that the post-splitting boundary values that are specified must be in ascending order, and the maximum value of the specified boundary values must be greater than the pre-splitting boundary value. Additionally, all partitioned boundary values must be within the range of the pre-splitting storage conditions. Table 13-9 shows the ALTER TABLE specification values and how the RDAREAs to be used after splitting are determined. Figure 13-20 shows the boundary value conditions before and after splitting.

Table 13-9 ALTER TABLE specification values and the method of determining the RDAREAs to be used after splitting

Pre-splitting boundary valuesPost-splitting boundary values specificationConditionCan be specified?Determination of post-splitting RDAREAS
Minimum boundary value (1. in the figure)Boundary valuesPost-splitting boundary value is smaller than the minimum boundary value (4. in the figure)YSpecified storage conditions and RDAREAs are used to select the post-splitting RDAREAs.
Post-splitting boundary value is equal to or greater than the minimum boundary value (1., 2., 3., and 5. in the figure)NCauses an error in ALTER TABLE.
OmittedNoneYRDAREA specified to store the maximum value range among the post-splitting storage conditions
Intermediate or maximum boundary value (2. in the figure)Boundary valuesPost-splitting boundary value is equal to or smaller than the boundary value that precedes the pre-splitting boundary value (1. and 4. in the figure when the pre-splitting boundary value is 200)NCauses an error in ALTER TABLE.
Post-splitting boundary value is smaller than the pre-splitting boundary value and is greater than the preceding boundary value (5. in the figure when the pre-splitting boundary value is 200)YSpecified storage conditions and RDAREAs are used to select the post-splitting RDAREAs.
Post-splitting boundary value is equal to or greater than the pre-splitting boundary value (200 when the pre-splitting boundary value is 200)NCauses an error in ALTER TABLE.
OmittedNoneYRDAREA specified to store the maximum value range among the post-splitting storage conditions
'MAX'
(3. in the figure)
Boundary valuesPost-splitting boundary value is smaller than the maximum boundary value (1., 2., 4., and 5. in the figure)NCauses an error in ALTER TABLE.
Post-splitting boundary value is greater than the maximum boundary value (3. in the figure)YSpecified storage conditions and RDAREAs are used to select the post-splitting RDAREAs.
OmittedNoneYRDAREA specified to store the data that is greater than the maximum boundary value.
Legend:
Y: Can be specified.
N: Cannot be specified.

Figure 13-20 Boundary value conditions before and after splitting

[Figure]

The post-splitting RDAREAs may include (reuse) the pre-splitting RDAREA or may be all newly created. Furthermore, after splitting, multiple (non-contiguous) storage ranges can be stored in the same RDAREA. During this process, depending on the specifications, the system can automatically combine boundary values or cause an error in ALTER TABLE. The following describes the system actions:

  1. Splits into RDAREAs as specified.
    If it is specified that contiguous storage ranges after splitting not be stored in the same RDAREA, the system splits the storage ranges as specified.
  2. Automatically combines storage ranges.
    If the RDAREAs storing the storage ranges preceding and succeeding the storage range to be split are specified as the RDAREAs to be used after splitting, the system automatically combines contiguous boundary values in the following cases:
    • When the RDAREA storing the range preceding the storage range to be split is the same as the RDAREA for storing the beginning storage range after splitting
    • When the RDAREA storing the range succeeding the storage range to be split is the same as the RDAREA for storing the last storage range after splitting
  3. Causes an error in ALTER TABLE.
    You cannot execute a split that stores multiple contiguous boundary values in the same RDAREA after the specified splitting. If such an attempt is made, the system causes an error in ALTER TABLE. In this case, you must modify and re-execute ALTER TABLE so that it combines the boundary value ranges and stores them in a single RDAREA.

Table 13-10 shows the system's action for storing multiple storage ranges in the same RDAREA.

Table 13-10 System action for storing multiple storage ranges in the same RDAREA

Specification of RDAREAs after splittingOne of the specified RDAREAs that is being used is outside the splitting target storage rangeResults of splitting/combiningSystem action
If the focus is placed only on the specified post-splitting RDAREAs, multiple contiguous boundary values are not stored in the same RDAREA.

RDAREAs that are not used in the pre-splitting table are specified.[Figure][Figure][Figure]Splits into the RDAREAs as specified.
Corresponds to Example 1-1 in Figure 13-21.
RDAREAs that are used in the pre-splitting table are specified.

Being used only in the splitting target storage range.[Figure][Figure]Splits into the RDAREAs as specified.
Corresponds to Example 1-2 in Figure 13-21.
Being used outside the splitting target storage range.Specified as the leading RDAREA after splitting.Same as the RDAREA immediately preceding the splitting target storage rangeCombines the storage range with the immediately preceding boundary value.
Corresponds to Example 2-1 in Figure 13-23.
Different from the RDAREA immediately preceding the splitting target storage rangeSplits into the RDAREAs as specified.
Corresponds to Example 1-3 in Figure 13-21.
Specified in the middle of the splitting.[Figure]Splits into the RDAREAs as specified.
Corresponds to Example 1-4 in Figure 13-22.
Specified at the end of the splitting.Same as the RDAREA immediately succeeding the splitting target storage rangeCombines the storage range with the immediately succeeding boundary value.
Corresponds to Example 2-2 in Figure 13-23.
Different from the RDAREA immediately succeeding the splitting target storage rangeSplits into the RDAREAs as specified.
Corresponds to Example 1-5 in Figure 13-22.
If the focus is placed only on the specified post-splitting RDAREAs, multiple contiguous boundary values can be stored in the same RDAREA.[Figure][Figure][Figure][Figure]Causes an error in ALTER TABLE.
Corresponds to Example 3-1 in Figure 13-24.

Legend:

[Figure]: Not applicable.

Figure 13-21 Example 1 of system action when storing multiple storage ranges in the same RDAREA (1 of 2)

[Figure]

Figure 13-22 Example 1 of system action when storing multiple storage ranges in the same RDAREA (2 of 2)

[Figure]

Figure 13-23 Example 2 of system action when storing multiple storage ranges in the same RDAREA

[Figure]

Figure 13-24 Example 3 of system action when storing multiple storage ranges in the same RDAREA

[Figure]

(4) Correspondence between a table and RDAREAs other than for the table

When, for example, a partitioning key index is defined for a table, the index data also must be stored in RDAREAs. If the partitioning storage conditions are changed for the table, the index must be split in the same way that the table RDAREAs are partitioned. Table 13-11 shows how to specify a table and RDAREAs other than for the table (combining storage conditions when partitioning boundary values). If more than one unit of a resource such as is shown in Table 13-11 is defined, the specification method must be applied to each of them. If any specification is incorrect, the system causes an error in ALTER TABLE. Figure 13-25 shows an example of the correspondence between a table and RDAREAs other than for the table. In this example, the table's contents and the contents of the RDAREAs other than for the table are split into three.

Table 13-11 Specifying a table and RDAREAs other than for the table (combining storage conditions when partitioning boundary values)

Resource nameSpecification method
ColumnBLOB columnSpecify these resources so that they correspond to the table RDAREAs on a one-to-one basis.
If a table RDAREA is specified more than once, specify the resources more than once also so that they correspond to the specified tables. If the existing table RDAREA is to be used after the change, you must specify the RDAREAs for storing the existing indexes and LOB data so that they correspond to the same boundary values.
IndexCluster key index
Primary key index (including an index for which the primary key and a cluster key are defined)
B-tree index

Figure 13-25 Example of the correspondence between a table and RDAREAs other than for the table

[Figure]

(5) Handling the data in an RDAREA to be split

The general rule is that when a storage range is split based on a boundary value, the system automatically deletes the applicable table's existing table data from the RDAREA. However, under some conditions, you can specify that the data is to be retained.

  1. Deleting data
    When a storage range is split based on a boundary value, some of the data in the RDAREA to be split will likely become data that will not be stored in that RDAREA after splitting. Therefore, all the data in the RDAREA in the storage range that is to be split must be deleted. Note that only the data in the table whose partitioning storage conditions are to be changed is deleted; data of other tables contained in that RDAREA is not deleted. One of the following methods is used to delete the data from the RDAREA:
    • Deleting all definition information
      If the pre-splitting RDAREA is not used for the table after splitting has taken place, all information about the pre-splitting RDAREA is deleted from the dictionary (MASTER.SQL_DIV_TABLE) containing the RDAREA information used by the table for each of the storage conditions. Table information that is managed within the RDAREA is also deleted. As a result, all data from the splitting-target table that existed in the RDAREA is deleted. In concept, this is equivalent to executing DROP TABLE for the RDAREA.
    • Deleting data only
      If the pre-splitting RDAREA will still be used for the table after splitting, the dictionary information and information managed within the RDAREA is not deleted, and only the table's data in the RDAREA is deleted. If an RDAREA that is to be used after splitting is already being used for another storage range, the data in this other storage range is also deleted. In concept, this is equivalent to executing PURGE TABLE for that RDAREA.
    Note that when data in an RDAREA is deleted, all data in the following corresponding RDAREAs is also deleted:
    • Index keys in an index RDAREA
    • Data in a BLOB column RDAREA
    Additionally, if the inner replica facility is being used, all generation data is deleted.
  2. Saving data
    As explained in 1. Deleting data, when a storage range is split based on a boundary value, the general rule is that the table's data in the pre-splitting RDAREA is deleted. However, the data in the RDAREA can be used as is if all the conditions listed below are satisfied, and therefore it is possible to avoid deletion of such data:
    • The pre-splitting RDAREA is to be used without modification as a post-splitting RDAREA.
    • All the existing data is at or below the boundary value for the pre-splitting RDAREA.
    • All data in the pre-splitting RDAREA will be within the storage range for the same RDAREA after splitting.
    To instruct that the data in the RDAREA is not to be deleted, you must specify the WITHOUT PURGE clause in ALTER TABLE. Table 13-12 shows whether or not the data is deleted depending on the specification of the WITHOUT PURGE clause. Note that the system does not check whether or not all the data in the RDAREA matches the storage ranges after splitting.

    Table 13-12 WITHOUT PURGE clause specification and data handling

    Use of the RDAREA after splittingCan the WITHOUT PURGE clause be specified?Action when the WITHOUT PURGE clause is specifiedAction when the WITHOUT PURGE clause is not specified
    Pre-splitting RDAREA will be used as a post-splitting RDAREA.YesDoes not delete the data from the pre-splitting RDAREA.Deletes the data from the pre-splitting RDAREA.
    Pre-splitting RDAREA will not be used as a post-splitting RDAREA.NoNot applicable
    Figure 13-26 shows an example of not specifying the WITHOUT PURGE clause. In this example, the table's data is deleted from the pre-splitting RDAREA because the WITHOUT PURGE clause is not specified.

    Figure 13-26 Example of an RDAREA from which data is deleted because WITHOUT PURGE is not specified

    [Figure]

  3. Notes on cases in which data is not deleted
    There may be data in the pre-splitting RDAREA that will not be within the applicable storage range after splitting (this type of data validity is not checked during execution of ALTER TABLE). Consequently, as a result of a change in partitioning storage conditions, data outside the new storage range may exist in the post-splitting RDAREA. When this happens, HiRDB may not function correctly during SQL execution.
    For this reason, you must be careful about using the WITHOUT PURGE clause when you change partitioning storage conditions. If you cannot guarantee that all data in the pre-splitting RDAREA will be within the new storage range of that RDAREA after splitting, you should unload the data from the pre-splitting RDAREA, execute the change in partitioning storage conditions without specifying the WITHOUT PURGE clause, and then load the unloaded data into the split RDAREAs. For details about how to recover from a mistake in a splitting operation, see 13.13.9(2) Recovery procedure when data not satisfying the post-splitting storage condition remains.
    Figure 13-27 shows examples of handling data in the post-splitting RDAREAs when WITHOUT PURGE is specified. These examples illustrate valid and invalid specifications of WITHOUT PURGE.

    Figure 13-27 Examples of handling data in the post-splitting RDAREAs (valid and invalid specifications of WITHOUT PURGE)

    [Figure]

  4. Notes on deleting data
    If the pre-splitting RDAREA contains data that will be stored in another storage range, data in this other storage range will also be deleted. This holds true, regardless of whether or not the pre-splitting RDAREA will be used as a post-splitting RDAREA. Figure 13-28 shows examples of RDAREA data deletion.

    Figure 13-28 Examples of RDAREA data deletion

    [Figure]