7.2 Creating a row partitioned table
This section describes the creation of a PRODUCT table. The following are the creation conditions:
- Partition the PRODUCT table by row. Store the PRODUCT table in user RDAREAs RDAREA01 and RDAREA02.
- Define a partitioning key index (INDX1) for the PRODUCT table. Store INDX1 in user RDAREAs RDAREA03 and RDAREA04.
- Define a non-partitioning key index (INDX2) for the PRODUCT table. Store INDX2 in user RDAREA RDAREA05. For a HiRDB/Parallel Server, store INDX2 in user RDAREAs RDAREA05 and RDAREA06.
- Store by means of initial storage in RDAREA01-RDAREA06 only the target data storage table (and indexes).
- In executing data storage, use batch creation (the default value) for the indexes.
- Perform data storage in the no-log mode.
For details about the partitioning key index and non-partitioning key index, see 14.3 Index row partitioning.
![[Figure]](figure/zu062010.gif)
- 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),
PNAME NCHAR(15),
LPRICE INTEGER,
QTY INTEGER
)IN ((RDAREA01)PNO<='10000',(RDAREA02));
- Specification of boundary value:
CREATE TABLE PRODUCT
(PNO CHAR(5),
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),
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
- Use the pdhold command to shut down the target data storage RDAREAs (RDAREA01-RDAREA05). For a HiRDB/Parallel Server, shut down RDAREA01-RDAREA06.
- 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 8 Command Reference.
- 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 manual HiRDB Version 8 System Operation Guide.
- 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 8 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 7.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:
- Data storage status of each user RDAREA
- Data storage status of each table or index