7.3 Creating a table with a LOB column

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

Notes
  • Only one LOB column is stored in a user LOB RDAREA. If a table contains multiple LOB columns, they must be stored in separate user LOB RDAREAs.
  • For a row partitioned table that has LOB columns, there must be a one-to-one correspondence between the user LOB RDAREAs for the LOB columns and the user RDAREAs for storing the table.
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
(5) Notes

(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,
PPICT BLOB(64K) IN ((LOBAREA01),(LOBAREA02))
)IN ((RDAREA01) PNO<='10000',(RDAREA02));

Specification of boundary value:

CREATE TABLE PRODUCT
(PNO CHAR(5),
PNAME NCHAR(15),
LPRICE INTEGER,
QTY INTEGER,
PPICT BLOB(64K) IN ((LOBAREA01),(LOBAREA02))
)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,
PPICT BLOB(6000) IN ((LOBAREA01),(LOBAREA02))
)[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:

CREATE INDEX INDX1 ON PRODUCT (PNO)
   IN ((RDAREA03),(RDAREA04));

(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-RDAREA04 and LOBAREA01-LOBAREA02).
  2. Use the pdload command to load the input data file into the table. Because only the target data storage table and index are stored in the RDAREAs, and because this is an 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.
  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 manual HiRDB Version 8 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 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 (pddbst) can obtain the following information:

(5) Notes

When executing data loading on a table with a LOB column, you can load the LOB column structure base table and LOB data separately.

Set the database update log acquisition mode to the no-log mode, and set the index creation method to batch index creation (the default value).

Procedure
  1. Use the pdhold command to shut down the target data storage RDAREAs (RDAREA01-RDAREA04 and LOBAREA01-LOBAREA02).
  2. Use the pdload command to load the input data file into the table (LOB column structure base table and index). At this time, the target data storage RDAREAs are RDAREA01-RDAREA04. Output to the LOB middle file the information required for data storage of the LOB column. For details about the options of the pdload command, see the manual HiRDB Version 8 Command Reference.
  3. Use the pdload command to perform data storage in user LOB RDAREAs LOBAREA01-LOBAREA02. Specify the LOB input file and the LOB middle file specified in step 2.
  4. Because the pdload command was 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.
  5. 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.