8.3.1 Examples

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

ExampleDescriptionClassification
1Unloading a row-partitioned table in units of tables
  • Using the unload data file as an input data file for pdload
  • Integrating all data in a single unload data file
P
2Unloading and reloading a table
  • After unloading the table, modifying the table partitioning conditions before reloading
3Unloading and reloading a table
  • Using unloaded table data to reload another table
4Unloading and reloading a table with an abstract data type (SGMLTEXT type)
  • After unloading the table, modifying the table partitioning conditions before reloading
5Unloading and reloading a table with an abstract data type (SGMLTEXT type)
  • Using unloaded table data to reload another table

P: HiRDB/Parallel Server

Organization of this subsection
(1) Unloading a row-partitioned table in units of tables
(2) Unloading and reloading a table while modifying the table partitioning conditions
(3) Unloading and reloading to migrate table data into another table
(4) Unloading and reloading while modifying the partitioning conditions of a table with an abstract data type
(5) Unloading and reloading to migrate table data from a table with an abstract data type to another table
(6) Creating an input data file for pdload in fixed-size data format

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

Example 1
This example unloads a row-partitioned table (TABLE1) in units of tables. It uses the unload data as an input data file (binary format) for pdload. All data is integrated in a single data file.
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 unloads the row-partitioned table (TABLE1) in units of tables.
-k unld: Specification for unloading
-W bin: Specification for using the unload data file as an input data file (binary format) for pdload
-g: Specification for integrating all data in a single unload data file
-t TABLE1: Name of the table being unloaded
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/usr/unload_file1                     1

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /usr/unload_file1: Name of the unload data file

(2) Unloading and reloading a table while modifying the table partitioning conditions

Example 2
To modify a table's (TABLE1's) partitioning conditions, this example first unloads the table, modifies the table partitioning conditions, then reloads the table.
The example assumes that the following table and index have been defined:
  • Table definition:

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

  • Index definition (partitioning key index):

CREATE INDEX INDEX1 ON TABLE1(C1) IN PDBUSER03

Overview
[Figure]
(a) Unloading the table before modifying the table partitioning conditions
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example unloads the table (TABLE1).
-k unld: Specification for unloading
-t TABLE1: Name of the table being unloaded
-g: Specification for modifying the table partitioning conditions
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                             1

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
(b) Modifying the table's (TABLE1's) partitioning conditions

First, drop TABLE1 with DROP TABLE. Then, redefine the table's (TABLE1's) partitioning conditions and modify the index definition, shown as follows:

CREATE TABLE TABLE1(C1 INT NOT NULL,
                    C2 CHAR(8),
                    C3 INT)
             IN ((PDBUSER01) C1 > 10,(PDBUSER02))
CREATE INDEX INDEX1 ON TABLE1(C1)
             IN ((PDBUSER03),(PDBUSER04))

(c) Reloading to the table after modifying the partitioning conditions
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example reloads to the table (TABLE1).
-k reld: Specification for reloading
-t TABLE1: Name of the table being reloaded
-g: Specification for modifying the table partitioning conditions
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                    1
idxwork bes1 /usr/idxwork                         2
idxwork bes2 /usr/idxwork                         2
sort bes1 /usr/sortwork,8192                      3
sort bes2 /usr/sortwork,8192                      3

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  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
    8192: Size of buffer for sorting (in KB)

(3) Unloading and reloading to migrate table data into another table

Example 3
This example migrates data from a table (TABLE1) to another table.
The example assumes that the following tables and indexes have been defined:
  • Source table and index definition:

CREATE TABLE TABLE1(C1 INT NOT NULL,C2 CHAR(8),C3 INT) IN PDBUSER01
CREATE INDEX INDEX1 ON TABLE1(C1) IN PDBUSER03

  • Target table and index definition:

CREATE TABLE TABLE11(C1 INT NOT NULL,C2 CHAR(8),C3 INT) IN PDBUSER11
CREATE INDEX INDEX11 ON TABLE11(C1) IN PDBUSER13

Overview
[Figure]
(a) Unloading the source table
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example unloads the table (TABLE1).
-k unld: Specification for unloading
-t TABLE1: Name of the table being unloaded
-g: Specification for migrating data into another table
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                             1

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
(b) Reloading to the destination table (reloading to another table)
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example reloads to the table (TABLE11).
-k reld: Specification for reloading
-t TABLE11: Name of the table being reloaded
-g: Specification for migrating data into another table
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file              1
idxwork bes2 /usr/idxwork                   2
sort bes2 /usr/sortwork,8192                3
tblname TABLE1                              4

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies the directory for index information files to which index information is to be output:
    bes2: Name of the server used to create index information files
    /usr/idxwork: Name of the directory for index information files
  3. Specifies the work directory for sorting:
    bes2: 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
    8192: Size of buffer for sorting (in KB)
  4. Specifies reloading the table data into another table:
    TABLE1: Name of the source table

(4) Unloading and reloading while modifying the partitioning conditions of a table with an abstract data type

Example 4
To modify the partitioning conditions of a table (TABLE1) with an abstract data type (SGMLTEXT), this example first unloads the table, modifies the table partitioning conditions, then reloads the table. The abstract data type (SGMLTEXT) is provided by the HiRDB Text Search Plug-in.
  • Table definition:

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

Overview
[Figure]
(a) Unloading the table before modifying the table partitioning conditions
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example unloads the table (TABLE1).
-k unld: Specification for unloading
-t TABLE1: Name of the table being unloaded
-j: Specification for unloading a table with an abstract data type with LOB attribute
-g: Specification for modifying the table partitioning conditions
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                             1
unld_func type=SGMLTEXT,func=unsgmltext(sgmltext)          2

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies the constructor parameter reverse creation function:
    SGMLTEXT: Name of the abstract data type (column C2)
    unsgmltext: Name of the constructor parameter reverse creation function (for the actual name, see the applicable plug-in manual)
    sgmltext: Data type of the argument of the constructor parameter reverse creation function
(b) Modifying the table's (TABLE1's) partitioning conditions

First, drop TABLE1 with DROP TABLE. Then, redefine the table's (TABLE1's) partitioning conditions as follows:

CREATE TABLE TABLE1(C1 INT NOT NULL,
                   C2 SGMLTEXT ALLOCATE
                      (SGMLTEXT IN ((LOBUSER01),(LOBUSER02)))
                       PLUGIN'<DTD>sgml.dtd</DTD>' )
   IN ((PDUSER01) C1<2000,(PDBUSER02))

(c) Reloading to the table after modifying the partitioning conditions
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example reloads to the table (TABLE1).
-k reld: Specification for reloading
-t TABLE1: Name of the table being reloaded
-j: Specification for reloading to a table with an abstract data type with LOB attribute
-g: Specification for modifying the table partitioning conditions
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                       1
reld_func type=SGMLTEXT,func=sgmltext(blob)          2

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies the constructor function:
    SGMLTEXT: Name of the abstract data type (column C2)
    sgmltext(blob): Name of the constructor function (parentheses enclose the data type of the constructor function's argument)

(5) Unloading and reloading to migrate table data from a table with an abstract data type to another table

Example 5
This example migrates data from a table (TABLE1) with abstract data type (SGMLTEXT) to another table. The abstract data type (SGMLTEXT) is provided by the HiRDB Text Search Plug-in.
  • Source table and index definition:

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

  • Target table and index definition:

CREATE TABLE TABLE2(C1 INT,
                   C2 SGMLTEXT ALLOCATE
                     (SGMLTEXT IN LOBUSER03)
                     PLUGIN'<DTD>sgml.dtd</DTD>'
                   C3 SGMLTEXT ALLOCATE
                     (SGMLTEXT IN LOBUSER04)
                     PLUGIN'<DTD>sgml.dtd</DTD>'
                   ) IN PDUSER02

Overview
[Figure]
(a) Unloading the source table
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example unloads the table (TABLE1).
-k unld: Specification for unloading
-t TABLE1: Name of the table being unloaded
-j: Specification for unloading a table with an abstract data type with LOB attribute
-g: Specification for migrating data into another table
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                             1
unld_func type=SGMLTEXT,func=unsgmltext(sgmltext)          2

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies the constructor parameter reverse creation function:
    SGMLTEXT: Name of the abstract data type (column C2)
    unsgmltext: Name of the constructor parameter reverse creation function (for the actual name, see the applicable plug-in manual)
    sgmltext: Data type of the argument of the constructor parameter reverse creation function
(b) Reloading to the target table (another table)
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example reloads to the table (TABLE2).
-k reld: Specification for reloading
-t TABLE2: Name of the table being reloaded
-j: Specification for reloading to a table with an abstract data type with LOB attribute
-g: Specification for migrating data to another table
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                       1
reld_func type=SGMLTEXT,func=sgmltext(blob)          2
tblname TABLE1                                       3

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies the constructor function:
    SGMLTEXT: Name of the abstract data type (column C2)
    sgmltext(blob): Name of the constructor function (parentheses enclose the data type of the constructor function's argument)
  3. Specifies reloading table data to another table:
    TABLE1: Name of the source table

(6) Creating an input data file for pdload in fixed-size data format

Example 6
This example unloads data from the table PRODUCT_TABLE as an input data file for pdload in fixed-size data format.
  • Table definition

 CREATE TABLE PRODUCT_TABLE
       (PRODUCT_NUMBER SMALLINT NOT NULL,PRODUCT_NAME NVARCHAR(12),QUANTITY INTEGER)
       IN ((RDAREA1)PRODUCT_NUMBER<=10000,(RDAREA2)PRODUCT_NUMBER<=20000,(RDAREA3));

pdrorg command

pdrorg -k unld -t PRODUCT_TABLE -W fixtext,@,cr control_file

Explanation
-k unld: Specification for unloading
-t PRODUCT_TABLE: Name of the table being unloaded
-W fixtext,@,cr: Specification for unloading the data as an input data file for pdload in fixed-size data format (@ is the padding character, and use of 1-byte linefeed codes is specified)
control_file: Name of the control information file
Contents of the control information file (control_file)

unload bes1:/hd001/unload_file                                          1
fixtext_option enclose=" format=integer,type1 format=smallint,type2     2

Explanation:
  1. Specifies the unload data file:
    bes1: Name of the server containing the unload data file
    /hd001/unload_file: Name of the unload data file
  2. Specifies how to edit the output data:
    enclose=": Encloses in the enclosing character (") the data for PRODUCT_NAME in a column whose data type is NVARCHAR.
    format=integer,type1: Outputs in output format type1 the data for QUANTITY in a column whose data type is INTEGER.
    format=smallint,type2: Outputs in output format type 2 the data for PRODUCT_NUMBER in a column whose data type is SMALLINT.
Output data

PRODUCT_NUMBER    PRODUCT_NAME    QUANTITY
(6 bytes)         (14 bytes)      (11 bytes)     (1 byte)

###100            "BOOTS"@@@@@@@  @@@@@@@@@@@    linefeed-character
@@@@@@            ""@@@@@@@@@@@@  #0000000100​    linefeed-character
-12345            @@@@@@@@@@@@@@  -0000000300​    linefeed-character

Note
# represents a space character, and 1 row consists of 32 bytes.