8.2.1 Examples

The section presents examples of using the database reorganization utility (reorganizing a table), listed as follows:

ExampleDescriptionClassification
1Reorganizing a row-partitioned table in units of tablesS
2Reorganizing a row-partitioned table in units of RDAREAsS
3Reorganizing a table with LOB columns
  • Reorganizing LOB column structure base tables and LOB columns at the same time
S
4Reorganizing a table with an abstract data type (SGMLTEXT type)
  • Reorganizing only the LOB column structure base table
S
5Reorganizing a row-partitioned table in units of tablesP
6Reorganizing a table with LOB columns
  • Reorganizing LOB column structure base tables and LOB columns at the same time
P
7Reorganizing tables in units of schemasP

S: HiRDB single server configuration

P: HiRDB parallel server configuration

Organization of this subsection
(1) Reorganizing a row-partitioned table in units of tables
(2) Reorganizing a row-partitioned table in units of RDAREAs
(3) Reorganizing a table with LOB columns
(4) Reorganizing a table with an abstract data type
(5) Reorganizing a row-partitioned table in units of tables
(6) Reorganizing a table with LOB columns
(7) Reorganizing tables in units of schemas

(1) Reorganizing a row-partitioned table in units of tables

Example 1
This example reorganizes a row-partitioned table (TABLE1) in units of tables.
The example assumes that the table (TABLE1) and indexes are defined as follows:
  • 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
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example reorganizes the row-partitioned table (TABLE1) in units of tables.
-k rorg: Specification for reorganization
-t TABLE1: Name of the table being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload /usr/unload_file1                     1
idxwork /usr/idx_file                        2
sort /usr/sortwork                           3

Explanation
  1. Specifies the unload data file:
    /usr/unload_file1: Name of the unload data file
  2. Specifies the directory in which index information files are to be created:
    /usr/idx_file: Name of the directory in which index information files are created
  3. Specifies the work directory for sorting:
    /usr/sortwork: Name of the directory in which the sort work file is created

(2) Reorganizing a row-partitioned table in units of RDAREAs

Example 2
This example reorganizes a row-partitioned table (TABLE1) in units of RDAREAs.
The example assumes that the table (TABLE1) and indexes are defined as follows:
  • 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
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
(a) Reorganizing TABLE1 (RDAREA PDBUSER01)
Explanation of the command
This example reorganizes the row-partitioned table (TABLE1) in units of RDAREAs.
-k rorg: Specification for reorganization
-t TABLE1: Name of the table being reorganized
-r PDBUSER01: Name of the RDAREA being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload /usr/unload_file1                     1
idxwork /usr/idx_file                        2
sort /usr/sortwork                           3

Explanation
  1. Specifies the unload data file:
    /usr/unload_file1: Name of the unload data file
  2. Specifies the directory in which index information files are to be created:
    /usr/idx_file: Name of the directory in which index information files are created
  3. Specifies the work directory for sorting:
    /usr/sortwork: Name of the directory in which the sort work file is created
(b) Creating INDEX2

You cannot use INDEX2 as is. To use INDEX2, you need to reorganize RDAREA PDBUSER02 and output index information for PDBUSER02. Create indexes from this index information file and the existing index information file 2 in batch mode by executing pdrorg (specifying -k ixmk).

For details, see 8.6 Creating indexes in batch mode.

(3) Reorganizing a table with LOB columns

Example 3
For a table with LOB columns (TABLE2), this example reorganizes its LOB column structure base table and LOB columns at the same time.
The example assumes that the table and index are defined as follows:
  • Table definition:

CREATE TABLE TABLE2(C1 INT NOT NULL,C2 BLOB IN
            ((LOBUSER01), (LOBUSER02))) IN
            ((PDBUSER01) C1 > 10,(PDBUSER02))

  • Index definition:

CREATE INDEX INDEX2 ON TABLE2(C1) IN
            ((PDBUSER03),(PDBUSER04))

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example reorganizes the table with LOB columns (TABLE2) in units of tables (both LOB column structure base table and LOB columns at the same time).
-k rorg: Specification for reorganization
-t TABLE2: Name of the table being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload /usr/unload_file1                                   1
index INDEX2 PDBUSER03 /usr/index_inf1                     2
index INDEX2 PDBUSER04 /usr/index_inf2                     2
sort /usr/sortwork                                         3
lobunld /usr/lobunld_file1                                 4

Explanation:
  1. Specifies the unload data file:
    /usr/unload_file1: Name of the unload data file
  2. Specifies the index information files to which index information is output:
    INDEX2: Index identifier
    PDBUSER03, PDBUSER04: Names of the index storage RDAREAs
    /usr/index_inf1, /usr/index_inf2: Names of the index information files
  3. Specifies the work directory for sorting:
    /usr/sortwork: Name of the directory in which the sort work file is created
  4. Specifies the LOB data unload file:
    /usr/lobunld_file1: Name of the LOB data unload file

(4) Reorganizing a table with an abstract data type

Example 4
This example reorganizes 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 table and index are defined as follows:
  • 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 reorganizes the table (TABLE1).
-k rorg: Specification for reorganization
-t TABLE1: Name of the table being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload /hd001/unload_file1                                    1
index INDEX1 LOBUSER02 /hd001/ixdir/INDEX1_LOBUSER02          2

Explanation:
  1. Specifies the unload data file:
    /hd001/unload_file1: Name of the unload data file
  2. Specifies the index information file:
    INDEX1: Index identifier of the plug-in index subject to batch index creation
    LOBUSER02: Name of the index storage RDAREA for the plug-in index subject to batch index creation
    /hd001/ixdir/INDEX1_LOBUSER02: Name of the index information file

(5) Reorganizing a row-partitioned table in units of tables

Example 5
This example reorganizes a row-partitioned table (TABLE1) in units of tables.
The example assumes that the table and indexes are defined as follows:
  • 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 reorganizes the row-partitioned table (TABLE1) in units of tables.
-k rorg: Specification for reorganization
-t TABLE1: Name of the table being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/usr/unload_file1                     1
idxwork bes1 /usr/idxwork                         2
sort bes1 /usr/sortwork                           3
unload bes2:/usr/unload_file2                     1
idxwork bes2 /usr/idxwork                         2
sort bes2 /usr/sortwork                           3

Explanation:
  1. Specifies the unload data file:
    bes1, bes2: Names of the servers containing the unload data files
    /usr/unload_file1, /usr/unload_file2: Names of the unload data files
  2. Specifies the directory for index information files to which index information is to be output:
    bes1, bes2: Names of the servers used to create index information files
    /usr/idxwork: Name of the directory for index information files
  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

(6) Reorganizing a table with LOB columns

Example 6
For a table with LOB columns (TABLE2), this example reorganizes both the LOB column structure base table and LOB columns at the same time.
The example assumes that the table and index are defined as follows:
  • Table definition:

CREATE TABLE TABLE2(C1 INT NOT NULL,C2 BLOB IN
          ((LOBUSER01), (LOBUSER02))) IN
          ((PDBUSER01) C1 > 10,(PDBUSER02))

  • Index definition:

CREATE INDEX INDEX2 ON TABLE2(C1) IN
           ((PDBUSER03),(PDBUSER04))

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example reorganizes the table with LOB columns (TABLE2) in units of tables (both the LOB column structure base table and LOB columns at the same time).
-k rorg: Specification for reorganization
-t TABLE2: Name of the table being reorganized
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/usr/unload_file1                              1
unload bes2:/usr/unload_file2                              1
index INDEX2 PDBUSER03 /usr/index_inf1                     2
index INDEX2 PDBUSER04 /usr/index_inf2                     2
sort bes1 /usr/sortwork                                    3
sort bes2 /usr/sortwork                                    3
lobunld bes1:/usr/lobunld_file1                            4
lobunld bes2:/usr/lobunld_file2                            4

Explanation:
  1. Specifies the unload data file:
    bes1, bes2: Names of the servers containing the unload data files
    /usr/unload_file1, /usr/unload_file2: Names of the unload data files
  2. Specifies the index information files to which index information is output:
    INDEX2: Index identifier
    PDBUSER03, PDBUSER04: Names of the index storage RDAREAs
    /usr/index_inf1, /usr/index_inf2: Names of the index information files
  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
  4. Specifies the LOB data unload files:
    bes1, bes2: Names of the servers containing the LOB data unload files
    /usr/lobunld_file1, /usr/lobunld_file2: Names of the LOB data unload files

(7) Reorganizing tables in units of schemas

Example 7
This example reorganizes all tables owned by schema USER01, for which the following tables have been defined:

CREATE TABLE TABLE1(C1 INT) IN (BES1R01)
CREATE TABLE TABLE2(C1 BLOB IN LOBUSER01) IN(BES2R01)
CREATE TABLE TABLE3(C1 ADT1) IN(BES3R01)

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example reorganizes all tables owned by schema USER01. When reorganizing tables in units of schemas with a HiRDB parallel server configuration, the system assumes the -g and -j options.
-k rorg: Specification for reorganization
-t USER01.all: Specification for all the tables owned by schema USER01
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file1                   1
idxwork bes1 /hd001/idxwork                       2
sort bes1 /hd001/sortwork                         3
idxwork bes2 /hd001/idxwork                       2
sort bes2 /hd001/sortwork                         3
idxwork bes3 /hd001/idxwork                       2
sort bes3 /hd001/sortwork                         3

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file1: Name of the unload data file
  2. Specifies the directory for index information files to which index information is to be output:
    bes1, bes2, bes3: Names of the servers used to create index information files
    /hd001/idxwork: Name of the directory for index information files
  3. Specifies the work directory for sorting:
    bes1, bes2, bes3: Names of the servers used to create the sort work file
    /hd001/sortwork: Name of the directory in which the sort work file is created