8.7.1 Examples

This section presents examples of using the database reorganization utility (re-creating indexes), listed as follows:

ExampleDescriptionClassification
1Re-creating a plug-in index
  • Re-creating a plug-in index defined for a table with an abstract data type (SGMLTEXT)
S
2Re-creating indexes in units of index storage RDAREAsP
3Re-creating indexes in units of indexes

S: HiRDB single server configuration

P: HiRDB parallel server configuration

Organization of this subsection
(1) Re-creating a plug-in index
(2) Re-creating an index in units of index storage RDAREAs
(3) Re-creating indexes in units of indexes

(1) Re-creating a plug-in index

Example 1
This example re-creates a plug-in index (INDEX1) defined for a table (TABLE1) with columns of abstract data type (SGMLTEXT). The abstract data type (SGMLTEXT) 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

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example creates a plug-in index (INDEX1) defined for a table (TABLE1).
-k ixrc: Specification for index re-creation
-t TABLE1: Name of the table subject to index re-creation
control_file: Name of the control information file
Contents of the control information file (control_file)

index INDEX1 LOBUSER02 /usr/index_file                   1

Explanation:
  1. Specifies the index information file to which plug-in index information is to be output:
    INDEX1: Identifier of the plug-in index to be created
    LOBUSER02: Name of the RDAREA storing the plug-in index to be created
    /usr/index_file: Name of the index information file to which index information is output

(2) Re-creating an index in units of index storage RDAREAs

Example 2
This example re-creates an index (INDEX1) in units of RDAREAs (PDBUSER03).
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
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example re-creates indexes (INDEX1 and INDEX2).
-k ixrc: Specification for index re-creation
-t TABLE1: Name of the table subject to index re-creation
control_file: Name of the control information file
Contents of the control information file (control_file)

index INDEX1 PDBUSER03 /usr/index_inf1                1
sort bes1 /usr/sortwork                               2

Explanation:
  1. Specifies the index information file:
    INDEX1: Index identifier
    PDBUSER03: Name of the index storage RDAREA
    /usr/index_inf1: Name of the index information file
  2. Specifies the work directory for sorting:
    bes1: Name of the server used to create the sort work file
    /usr/sortwork: Name of the directory in which the sort work file is created

(3) Re-creating indexes in units of indexes

Example 3
This example re-creates indexes (INDEX1 and INDEX2) defined for a row-partitioned table (TABLE1).
The example assumes that the following table and indexes have been defined:
  • Table definition:

CREATE TABLE TABLE1(C1 INT,C2 INT NOT NULL)
    IN ((PDBUSER01) C2<200,(PDBUSER02) C2<400,(PDBUSER03))

  • Index definition:

CREATE INDEX INDEX1 ON TABLE1(C2) IN ((IDX01),(IDX02),(IDX03))
CREATE INDEX INDEX2 ON TABLE1(C1) IN ((IDX04),(IDX05))

Overview
[Figure]
(a) Re-creating all indexes defined for the table
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example re-creates indexes (INDEX1 and INDEX2).
-k ixrc: Specification for index re-creation
-t TABLE1: Name of the table subject to index re-creation
control_file: Name of the control information file
Contents of the control information file (control_file)

idxname name=*                                  1
idxwork bes1 /index/workdir01                   2
idxwork bes2 /index/workdir02                   2
sort bes1 /usr/sortwork                         3
sort bes2 /usr/sortwork                         3

Explanation:
  1. Specifies index re-creation in units of indexes:
    *: Specification for re-creating all indexes defined for the table (TABLE1)
  2. Specifies the directory in which index information files are to be created:
    bes1, bes2: Names of the servers used to create index information
    /index/workdir01, /index/workdir02: Names of the directories in which index information files are created
  3. 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
(b) Re-creating one of the indexes defined for a table
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example re-creates an index (INDEX1).
-k ixrc: Specification for index re-creation
-t TABLE1: Name of the table subject to index re-creation
control_file: Name of the control information file
Contents of the control information file (control_file)

idxname name=INDEX1                           1
idxwork bes1 /index/workdir01                 2
idxwork bes2 /index/workdir02                 2
sort bes1 /usr/sortwork                       3
sort bes2 /usr/sortwork                       3

Explanation:
  1. Specifies index re-creation in units of indexes:
    INDEX1: Name of the index to be re-created
  2. Specifies the directory in which index information files are to be created:
    bes1, bes2: Names of the servers used to create index information
    /index/workdir01, /index/workdir02: Names of the directories in which index information files are created
  3. 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