7.4 Creating a table containing a plug-in-provided abstract data type

This section describes the procedure for creating a table with abstract data type (SGMLTEXT type) that is provided by the HiRDB Text Search Plug-in.

A MEDICINE_MANAGEMENT_TABLE is created here. The following are the creation conditions:

Explanation:
This example stores MEDICINE_ID (MCHAR type) in the user RDAREAs and OPERATION_MANUAL (SGMLTEXT type) in the user LOB RDAREAs.
Organization of this section
(1) Defining the MEDICINE_MANAGEMENT_TABLE
(2) Defining a plug-in index
(3) Storing data in the table
(4) Checking the data storage status
(5) Loading data in units of RDAREAs into a table for which partitioning conditions are specified with a hash function

(1) Defining the MEDICINE_MANAGEMENT_TABLE

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

(a) Key range partitioning
Specification of storage condition:

CREATE TABLE MEDICINE_MANAGEMENT_TABLE(
MEDICINE_ID MCHAR(15),
OPERATION_MANUAL SGMLTEXT                               1.
 ALLOCATE(SGMLTEXT IN((LOBAREA01),(LOBAREA02)))        2.
 PLUGIN'<DTD>medicine.dtd</DTD>'                       3.
 )IN((RDAREA01)MEDICINE_ID<='MEDICINE 10',(RDAREA02)); 4.

Specification of boundary value:

CREATE TABLE MEDICINE_MANAGEMENT_TABLE(
MEDICINE_ID MCHAR(15),
OPERATION_MANUAL SGMLTEXT                               1.
 ALLOCATE(SGMLTEXT IN((LOBAREA01),(LOBAREA02)))        2.
 PLUGIN'<DTD>medicine.dtd</DTD>'                       3.
 )PARTITIONED BY MEDICINE_ID
 IN((RDAREA01)'MEDICINE 10',(RDAREA02));               4.

Explanation:

  1. Specifies the data type provided by the plug-in module.
  2. The SGMLTEXT LOB column in MEDICINE_MANAGEMENT_TABLE is divided among and stored in user LOB RDAREAs LOBAREA01 and LOBAREA02.
  3. Specifies the plug-in portion. For details about specification, see the applicable plug-in documentation.
  4. MEDICINE_MANAGEMENT_TABLE's LOB column structure base table is divided and stored in user RDAREAs RDAREA01 and RDAREA02.
(b) Flexible hash partitioning or FIX hash partitioning

CREATE TABLE MEDICINE_MANAGEMENT_TABLE(
MEDICINE_ID MCHAR(15),
OPERATION_MANUAL SGMLTEXT                              1.
 ALLOCATE(SGMLTEXT IN((LOBAREA01),(LOBAREA02)))       2.
 PLUGIN'<DTD>medicine.dtd</DTD>'                      3.
 )[FIX]* HASH HASH6 BY MEDICINE_ID
 IN(RDAREA01,RDAREA02)                                4.

Explanation:

  1. Specifies the data type provided by the plug-in module.
  2. The SGMLTEXT LOB column in MEDICINE_MANAGEMENT_TABLE is divided among and stored in user LOB RDAREAs LOBAREA01 and LOBAREA02.
  3. Specifies the plug-in portion. For details about specification, see the applicable plug-in documentation.
  4. MEDICINE_MANAGEMENT_TABLE's LOB column structure base table is divided and stored in user RDAREAs RDAREA01 and RDAREA02.
* This specification is applicable to FIX hash partitioning.

(2) Defining a plug-in index

If you use the index type for data retrieval offered by a plug-in, you can retrieve data easily and at high speed. The index type offered by a plug-in is called plug-in index. This section explains how to define a plug-in index using the index type (NGRAM) provided by the HiRDB Text Search Plug-in.

The following example defines a plug-in index for MEDICINE_MANAGEMENT_TABLE using CREATE INDEX:

CREATE INDEX PLGINDX1
       USING TYPE NGRAM
       ON MEDICINE_MANAGEMENT_TABLE(OPERATION_MANUAL)
       IN ((LOBAREA03),(LOBAREA04));

Explanation:
For the row-partitioned MEDICINE_MANAGEMENT_TABLE, plug-in index PLGINDX1 is divided and stored in user LOB RDAREAs LOBAREA03 and LOBAREA04. OPERATION_MANUAL is specified for the column that constitutes the PLGINDX1 plug-in index.

(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-RDAREA02 and LOBAREA01-LOBAREA04).
  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 the constructor function and the data type information passed to the constructor function, specify a column structure information file. 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) Loading data in units of RDAREAs into a table for which partitioning conditions are specified with a hash function

You can create a UAP using a hash function for table partitioning to create an input data file for each RDAREA. Because this makes it possible to check the amount of data to be stored in each RDAREA, you can select a hash function for uniform partitioning. For details about how to create a UAP for using a hash function for table partitioning, see the manual HiRDB Version 8 UAP Development Guide.