Example 5: Allocating a different RDAREA each day (in a case in which matrix partitioning is not used)

This example assumes that data for 1 month and 2 days (July 31 through September 1, 2011) is stored in the PAID table. The example uses the hash function HASHZ to delete the data for the oldest day, July 31, 2011, from the RDAREA. Then, the example stores the data for the most recent day, September 2, 2011.

The following figure shows an example of allocating a different RDAREA each month (in a case in which matrix partitioning is not used).

Figure 13-90 Allocating a different RDAREA each month (in a case in which matrix partitioning is not used)

[Figure]

Notes:

1. (m) in the RDAREAs indicates the partitioning condition specification order.

2. The PAID table has been defined as follows:

      CREATE TABLE PAID
        (FORMNO CHAR(6), CUSTCODE CHAR(5), PRODCODE CHAR(4), ORDDATE CHAR(8) NOT NULL)
        FIX HASH HASHZ BY ORDDATE IN
        (RDA1,RDA2,RDA3,RDA4,RDA5,RDA6,RDA7,RDA8,
         RDA9,RDA10,RDA11,RDA12,RDA13,RDA14,RDA15,
         RDA16,RDA17,RDA18,RDA19,RDA20,RDA21,RDA22,
         RDA23,RDA24,RDA25,RDA26,RDA27,RDA28,RDA29,
         RDA30 ,RDA31,RDA32,RDA33);

Organization of this subsection: (1) Identify the RDAREA that contains the data for July 31, 2011, 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 of the PAID 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 July 31, 2011, which is to be deleted

Use either of the following methods to identify the RDAREA name.

(a) Using the p_rdb_dbhash hash function for table partitioning

(i) 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 July 31, 2011, 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 HiRDB Version 9 UAP Development Guide.

No.	Argument	Specification value
1	hashcode (hash function code)	p_rdb_HASHZ(99)
2	ncol (number of partitioning key columns)	1
3	collst (partitioning key specification order and data type code, and data length code)	Data type code: PDSQL_CHAR Data length code: 8
4	dadlst (data stored in the partitioning key)	Value of data type in No. 3
5	ndiv (number of table partitions)	33
6	ncspace (national character for the double-byte space used at the HiRDB servers)	ncspace[0]: 0x81 ncspace[1]: 0x40
7	flags (space conversion level and whether facility for conversion to a DECIMAL signed normalized number is to be used)	0
8	rdno (partitioning condition specification order or sequence number in the partitioning key)	None

Note:: This example assumes that the character code 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.

(ii) 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='PAID'    /* 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 as shown in the following:

 
 RDAREA_NAME
 ------------------------------
 RDA1

(b) Method that defines CREATE PUBLIC FUNCTION

(i) Define CREATE PUBLIC FUNCTION

For the hash function HASHZ, you can identify the RDAREA by pre-defining the following SQL statement:

 
CREATE PUBLIC FUNCTION HASHZ
 (IDATE VARCHAR(8)^#,        /* 'YYYYMMDD' */
  NDIV INT)                 /* number of partitioning columns */
RETURNS INT
  BEGIN
    RETURN MOD(DAYS(DATE(IDATE,'yyyymmdd'),NDIV)+1;  /* value returned by HASHZ */
  END
END_FUNC;

#: Although the column data is CHAR(8), the parameter must be VARCHAR. Otherwise, a calling operation with a character string literal specified cannot be executed.

(ii) Execute the SQL statement

Execute the following SQL statement to find where the data for July 31, 2011 is located:

 
SELECT RDAREA_NAME
  FROM MASTER.SQL_DIV_TABLE
    WHERE TABLE_SCHEMA='USER1'   /* user name */
      AND TABLE_NAME='PAID'   /* name of hash-partitioned table */
      AND DIV_NO=HASHZ('20110731',33);      /* RDAREA definition order */

When this SQL statement is executed, RDA1 is returned as the name of the RDAREA to be deleted as shown in the following:

 
 RDAREA_NAME
 ------------------------------
 RDA1

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

(3) Use the pdrorg command to unload data from RDA1 of the PAID table

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

To delete the data for July 31, 2011 (the data in RDA1), which is the oldest data, execute data loading with a data count of 0.

 
pdload -d PAID load_control_file

Explanation

-d: Specifies that the existing data is to be deleted, and then data loading is to be performed.

PAID: Specifies the name of the data on which data loading is to be performed.

load_control_file: Specifies the name of the control information file for the pdrorg command. The following shows the contents of the control information file:

 
source RDA1 bes1:/pdload/load_file
 
/* RDA1: Name of RDAREA on which data loading is to be performed with a data count of 0 */
/* bes1: Name of server that contains data file to be loaded with a data count of 0 */
/* /pdload/load_file: Name of data file to be loaded with a data count of 0 */

If a non-partitioning index that is not partitioned is defined for a partitioned table, batch-create non-partitioning indexes after pdload command execution.