Example 4: Allocating a different RDAREA each month (in the case of matrix partitioning)

This example assumes that data for five recent months (May through September 2011) is stored in the ORDER table. The example uses the hash function HASH0 to delete the data for the earliest month, May 2011, from the RDAREA. Then, the example stores the data for the most recent month, October 2011. It is assumed that the matrix-partitioned table is partitioned as follows:

The first dimension is key range partitioned by the PRODCODE column. The number of partitions for the first dimension is 2, and 100 is specified as the boundary value.
The second dimension is hash partitioned by the ORDDATE column. The number of partitions for the second dimension is 5, and HASH0 is specified as the hash function.
The data type of the ORDDATE column is CHAR(8), and data is stored in the YYYYMMDD format.

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

Figure 13-89 Allocating a different RDAREA each month (in the case of matrix partitioning)

[Figure]

Notes:

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

2. The ORDER table has been defined as follows:

       CREATE TABLE ORDER
         (FORMNO CHAR(6), CUSTCODE CHAR(5), PRODCODE CHAR(4) NOT NULL, ORDDATE CHAR(8) NOT NULL)
           PARTITIONED BY MULTIDIM
           (PRODCODE(('100')),
           FIX HASH HASHZ BY ORDDATE) IN
           ((RDA1,RDA2,RDA3,RDA4,RDA5), (RDA6,RDA7,RDA8,RDA9,RDA10));

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

(1) Identify the RDAREA that contains the data for May 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 May 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_HASH0(100)
2	ncol (number of partitioning key columns)	1
3	collst (partitioning key specification order, 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)	5
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 the 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 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,DCVALUES
    FROM MASTER.SQL_DIV_TABLE
      WHERE TABLE_SCHEMA='USER1'     /* user name */
        AND TABLE_NAME='ORDER'       /* name of hash-partitioned table */
          AND MOD(DIV_NO-1,5)+1 = 1  /* RDAREA definition order */

When this SQL statement is executed, RDA1 and RDA6 are returned as the names of the RDAREAs to be deleted, as shown in the following:

RDAREA_NAME DCVALUES^#
-------------------------- ----------------------
RDA1 100
RDA6 *********************

#: Searching through the DCVALUES column at the same time can also provide the information indicating the partition in the first dimension to which the RDAREA corresponds.

(b) Method that defines CREATE PUBLIC FUNCTION

(i) Define CREATE PUBLIC FUNCTION

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

 
CREATE PUBLIC FUNCTION HASH0
 (DIVKEY VARCHAR(8)^#,          /* 'YYYYMMDD' or 'YYYYMM' */
  NDIV INT)                    /* number of partitioning columns */
  RETURNS INT
    BEGIN
      DECLARE DIVDATE DATE;
      SET DIVDATE = CASE LENGTH(DIVKEY)
             WHEN 6 THEN DATE(SUBSTR(DIVKEY,1,6)||'01','YYYYMMDD')
             ELSE DATE(DIVKEY,'YYYYMMDD')
             END;
  RETURN MOD(YEAR(DIVDATE)*12+MONTH(DIVDATE), NDIV)+1;    /* return value */
  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 May 2011 is located:

 
SELECT RDAREA_NAME,DCVALUES
  FROM MASTER.SQL_DIV_TABLE
    WHERE TABLE_SCHEMA='USER1'  /* user name */
      AND TABLE_NAME='ORDER'  /* name of hash-partitioned table */
      AND MOD(DIV_NO-1,5)+1=HASH0('20110531',5)  /* RDAREA definition order */

When this SQL statement is executed, RDA1 and RDA6 are returned as the names of the RDAREAs to be deleted, as shown in the following:

RDAREA_NAME DCVALUES^#
-------------------------- ----------------------
RDA1 100
RDA6 *********************

#: Searching through the DCVALUES column at the same time can also provide the information indicating the partition in the first dimension to which the RDAREA corresponds.

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

(3) Use the pdrorg command to unload data from RDA1 and RDA6 of the ORDER table

 
pdrorg -k unld -t ORDER -r RDA1 unload_control_file1

 
pdrorg -k unld -t ORDER -r RDA6 unload_control_file6

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

RDA1:

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

RDA6

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

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

To delete the data for May 2011 (the data in RDA1 and RDA6), which is the earliest data, execute data loading with a data count of 0.

 
pdload -d ORDER load_control_file1

 
pdload -d ORDER load_control_file6

Explanation: -d: Specifies that the existing data is to be deleted, and that after the deletion data loading is to be performed.; ORDER: 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: