6.2 Creating a row-partitioned table

This section describes the creation of a PRODUCT table. The following are the creation conditions:

For details about the partitioning key index and non-partitioning key index, see 13.3 Index row partitioning.

Data can also be loaded to a row-partitioned table using the parallel loading facility. For details about the parallel loading facility, see 6.1.6 Loading data into a row-partitioned table (using the parallel loading facility).

[Figure]

Organization of this section
(1) Defining the PRODUCT table
(2) Defining an index
(3) Storing data in the table
(4) Checking the data storage status

(1) Defining the PRODUCT table

Define the PRODUCT table with CREATE TABLE. The following shows an example:

(a) Key range partitioning
Specification of storage condition:

CREATE TABLE PRODUCT
(PNO CHAR(5) NOT NULL,
PNAME NCHAR(15),
LPRICE INTEGER,
QTY INTEGER
)IN ((RDAREA01)PNO<='10000',(RDAREA02));

Specification of boundary value:

CREATE TABLE PRODUCT
(PNO CHAR(5) NOT NULL,
PNAME NCHAR(15),
LPRICE INTEGER,
QTY INTEGER
)PARTITIONED BY PNO
IN ((RDAREA01)'10000',(RDAREA02));

(b) Flexible hash partitioning or FIX hash partitioning

CREATE TABLE PRODUCT
(PNO CHAR(5) NOT NULL,
PNAME NCHAR(15),
LPRICE INTEGER,
QTY INTEGER
)[FIX]# HASH HASH6 BY PNO
IN (RDAREA01,RDAREA02);

#: This specification is applicable to FIX hash partitioning.

(2) Defining an index

Define an index for the PRODUCT table using CREATE INDEX. The following shows an example:

(a) HiRDB/Single Server

CREATE INDEX INDX1 ON TABLE (PNO)
   IN ((RDAREA03),(RDAREA04));
CREATE INDEX INDX2 ON PRODUCT (QTY)
   IN (RDAREA05);

(b) HiRDB/Parallel Server

CREATE INDEX INDX1 ON PRODUCT (PNO)
   IN ((RDAREA03),(RDAREA04));
CREATE INDEX INDX2 ON PRODUCT (QTY)
   IN ((RDAREA05),(RDAREA06));

(3) Storing data in the table

To use the database load utility (pdload) to store data in the table.

Procedure
  1. Use the pdhold command to shut down the target data storage RDAREAs (RDAREA01-RDAREA05). For a HiRDB/Parallel Server, shut down RDAREA01-RDAREA06.
  2. Use the pdload command to load the input data file into the table. Because only the target data storage table and indexes are stored in the RDAREAs, and because this is the initial storage, select the no-log mode as the database update log acquisition mode. For the index creation method, select the batch index creation mode (the default value). For details about the options of the pdload command, see the manual HiRDB Version 9 Command Reference.
  3. Because the pdload command is executed in the no-log mode, make a backup of the target data storage RDAREAs. For details about how to make backups in units of RDAREAs, see the HiRDB Version 9 System Operation Guide.
  4. Use the pdrels command to release the target data storage RDAREAs from shutdown status.

For details about these commands and utilities, and about how to verify the command and utility execution results, see the manual HiRDB Version 9 Command Reference.

Supplemental notes
  • Because the pdload command executes in the no-log mode, the target data storage RDAREAs must remain on shutdown status during steps 1-3.
  • In the case of a falsification prevented table, when data storage is performed with the pdload command, the -d option cannot be specified.
  • For details about error handling during batch index creation, see 6.6 Handling errors during batch index creation.

(4) Checking the data storage status

If you have executed data loading, you should execute the database condition analysis utility (pddbst) next to check the data storage status. This utility enables you to check whether the database has been created exactly as designed. The database condition analysis utility can obtain the following information: