Examples

This section presents examples of using the database reorganization utility (creating indexes in batch mode), listed as follows:

Example	Description	Classification
1	Creating an index in batch mode Creating an index in batch mode on the basis of the index information file output in table reorganization example 2	S
2	Creating a plug-in index in delayed batch mode Creating a plug-in index defined for a table with a abstract data type (SGMLTEXT) in delayed batch mode	S
3	Creating indexes in batch mode Using the index information files output during the execution of the database load utility (index information output mode)	P

S: HiRDB single server configuration

P: HiRDB parallel server configuration

Organization of this subsection: (1) Creating an index in batch mode; (2) Creating a plug-in index in delayed batch mode; (3) Creating indexes in batch mode

(1) Creating an index in batch mode

Example 1

This example creates an index (INDEX2) in batch mode on the basis of the index information that was output in table reorganization example 2. The name of the index information file is /usr/index_inf2. Index information in PDBUSER02 has already been obtained in /usr/index_inf4.

The example assumes that the following table (TABLE1) and indexes have been defined:

Table definition:

CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT)
         IN ((PDBUSER01) C1 > 10,(PDBUSER02))

Index definition (partitioning key index):

CREATE INDEX INDEX1 ON TABLE1(C1)
         IN ((PDBUSER03),(PDBUSER04))

Index definition (non-partitioning key index):

CREATE INDEX INDEX2 ON TABLE1(C2,C1) IN (PDBUSER05)

Overview

Relationship between input/output files and RDAREAs

Explanation of the command: This example creates an index (INDEX2) in batch mode on the basis of the index information.; -k ixmk: Specification for batch index creation; -t TABLE1: Name of the table subject to batch index creation; -o: Specification for deleting unneeded index information files after index creation is completed; control_file: Name of the control information file

Contents of the control information file (control_file)

 
 index INDEX2 PDBUSER05 /usr/index_inf2                  1
 index INDEX2 PDBUSER05 /usr/index_inf4                  1
 sort /usr/sortwork                                      2

Explanation:

Specifies the index information files containing the index information:
INDEX2: Index identifier
PDBUSER05: Name of the index storage RDAREA
/usr/index_inf2, /usr/index_inf4: Names of the index information files
Specifies the work directory for sorting:
/usr/sortwork: Name of the directory in which the sort work file is created

(2) Creating a plug-in index in delayed batch mode

Example 2

This example creates a plug-in index (INDEX1) in delayed batch mode that is defined for a table (TABLE1) with columns of abstract data type (SGMLTEXT). The abstract data type is provided by the HiRDB Text Search Plug-in.

The example assumes that the following table and index have been defined:

Table definition:

CREATE TABLE TABLE1(C1 INT,C2 SGMLTEXT
          ALLOCATE (SGMLTEXT IN LOBUSER01)
          PLUGIN'<DTD>sgml.dtd</DTD>' ) IN PDBUSER01

Plug-in index definition:

CREATE INDEX INDEX1 USING TYPE NGRAM ON TABLE1(C2)
          IN LOBUSER02

The index information file was created by executing an updating UAP (INSERT or UPDATE statement) with the following definition information:

Server definition:

set pd_plugin_ixmk_dir="/hd001/ixdir"

Client environment variable:

PDPLGIXMK YES

Overview

Relationship between input/output files and RDAREAs

Explanation of the command: This example creates a plug-in index (INDEX1) defined for the table (TABLE1) in delayed batch mode.; -k ixmk: Specification for batch index creation; -t TABLE1: Name of the table subject to batch index creation; control_file: Name of the control information file

Contents of the control information file (control_file)

 
 index INDEX1 LOBUSER02 /hd001/ixdir/INDEX1.LOBUSER02               1

Explanation:

Specifies the index information file containing the information subject to delayed batch index creation
INDEX1: Identifier of the plug-in index subject to delayed batch index creation
LOBUSER02: Name of the index storage RDAREA for the plug-in index subject to delayed batch index creation
/hd001/ixdir/INDEX1.LOBUSER02: Name of the index information file

(3) Creating indexes in batch mode

Example 3

This example creates indexes in batch mode on the basis of the index information files. It uses four index information files that were output during the execution of the database load utility (to load data in index information output mode).

The example assumes that the following table and indexes have been defined:

Table definition:

CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT)
               IN ((PDBUSER01) C1 > 10,(PDBUSER02))

Index definition (partitioning key index):

CREATE INDEX INDEX1 ON TABLE1(C1)
               IN ((PDBUSER03),(PDBUSER05))

Index definition (non-partitioning key index):

CREATE INDEX INDEX2 ON TABLE1(C2,C1)
               IN ((PDBUSER04),(PDBUSER06))

Overview

Relationship between input/output files and RDAREAs

Explanation of the command: This example creates indexes in batch mode on the basis of the index information output during data load operation.; -k ixmk: Specification for batch index creation; -t TABLE1: Name of the table subject to batch index creation; control_file: Name of the control information file

Contents of the control information file (control_file)

 
 index INDEX1 PDBUSER03 /usr/index_inf1          1
 index INDEX2 PDBUSER04 /usr/index_inf2          1
 index INDEX1 PDBUSER05 /usr/index_inf3          1
 index INDEX2 PDBUSER06 /usr/index_inf4          1
 sort bes1 /usr/sortwork                         2
 sort bes2 /usr/sortwork                         2

Explanation:

Specifies the index information files containing the index information:
INDEX1, INDEX2: Index identifiers
PDBUSER03, PDBUSER04, PDBUSER05, PDBUSER06: Names of the index storage RDAREAs
/usr/index_inf1, /usr/index_inf2, /usr/index_inf3, /usr/index_inf4: Names of the index information files
Specifies the work directory for sorting:
bes1, bes2: Names of the servers used to create the sort work file
/usr/sortwork: Name of the directory in which the sort work file is created

8.6.1 Examples

(1) Creating an index in batch mode

(2) Creating a plug-in index in delayed batch mode

(3) Creating indexes in batch mode