13.15.3 Example 3 (allocating a different RDAREA each month)

This example assumes that data for five recent months (May through September 2005) is stored. The example uses the hash function HASH0 to delete the data for the oldest month, May 2005, from the RDAREA and then stores the data for the most recent month, October 2005.

Figure 13-82 provides an overview of Example 3 (method for allocating a different RDAREA each month).

Figure 13-82 Overview of Example 3 (method for allocating a different RDAREA each month)

[Figure]

Organization of this subsection
(1) Identify the RDAREA that contains the data for May 2005, which is to be deleted
(2) Use the pdhold command to shut down the RDAREA to be unloaded
(3) Use the pdrorg command to unload data from RDA1 for the ORDER table
(4) Use the pdload command to perform data loading on RDA1 with a data count of 0
(5) Use the pdrels command to release the RDAREA from shutdown status

(1) Identify the RDAREA that contains the data for May 2005, which is to be deleted

(a) Execute the hash function for table partitioning

Use the HiRDB-provided hash function for table partitioning to identify the RDAREA that contains the data for May 2005, which is to be deleted. The table below shows the arguments of the hash function for table partitioning. For details about the hash function for table partitioning, see the manual HiRDB Version 8 UAP Development Guide.

ArgumentSpecification value
hashcode (hash function code)p_rdb_HASH0
ncol (number of partitioning key columns)1
collst (partitioning key specification order and data type code, and data length code)Data type code: PDSQL_CHAR
Data length code: 8
dadlst (data stored in the partitioning key)Year, month, date
ndiv (number of table partitions)5
ncspace (national character for the double-byte space used at the HiRDB servers)ncspace[0]: 0x81
ncspace[1]: 0x40
flags (space conversion level and whether or not the facility for conversion to a DECIMAL signed normalized number is to be used)0
rdno (partitioning condition specification order or serial number in the partitioning key)None
Note:
This example assumes that the character encoding type, space conversion level, and the facility for conversion to a DECIMAL signed normalized number are all omitted.

When the above hash function for table partitioning is executed, rdno=1 is returned as the partitioning condition specification order.

(b) Execute the SQL statement

Based on the result of the hash function for table partitioning, execute the following SQL statement to find the name of the RDAREA:

SELECT RDAREA_NAME
   FROM MASTER.SQL_DIV_TABLE
     WHERE TABLE_SCHEMA='USER1' /* user name         */
       AND TABLE_NAME='ORDER'   /* name of hash-partitioned table */
       AND DIV_NO=1                 /* partitioning condition specification order */

When this SQL statement is executed, RDA1 is returned as the name of the RDAREA to be deleted.

(2) Use the pdhold command to shut down the RDAREA to be unloaded

pdhold -r RDA1

(3) Use the pdrorg command to unload data from RDA1 for the ORDER table

pdrorg -k unld -t ORDER -r RDA1 /pdrorg/unld03

Explanation
-k: Specifies unld for unloading.
-t: Specifies the name of the table to be unloaded.
-r: Specifies the name of the target RDAREA in order to unload only the specified RDAREA.
/pdrorg/unld03: Specifies the name of the control information file for the pdrorg command. The following shows the contents of the control information file:

unload bes1:/pdrorg/unload_file

/* bes1: Name of server that contains unload data file  */
/* /pdrorg/unload_file: Name of unload data file */

(4) Use the pdload command to perform data loading on RDA1 with a data count of 0

To delete the data for May 2005 (the data in RDA1), which is the oldest data, execute data loading with a data count of 0. If a non-partitioning key index has been defined for the partitioned table, you must create the non-partitioning key index in the batch mode after executing pdload.

pdload -d ORDER /pdload/load03

Explanation
-d: Specifies that the existing data is to be deleted and then data loading is to be performed.
ORDER: Specifies the name of the data on which data loading is to be performed.
/pdload/load03: Specifies the name of the control information file for the pdload command. The following shows the contents of the control information file:

source RDA1 /pdload/load_file

/* RDA1: Name of RDAREA on which data loading is to be performed        */
/* /pdload/load_file: Name of input data file */

(5) Use the pdrels command to release the RDAREA from shutdown status

pdrels -r RDA1

After executing the command, you should check the execution results for errors. For details about how to check the command's execution results, see the manual HiRDB Version 8 Command Reference.

After the command has executed, you can store data in RDA1 by using a program such as a UAP to insert the data for October 2005.