5.14.2 HiRDB parallel server configuration

Organization of this subsection
(1) Loading data to a table
(2) Loading data to a row-partitioned table in units of tables
(3) Loading data to a row-partitioned table in units of RDAREAs
(4) Loading data to a table with LOB columns (loading data to LOB RDAREA and LOB columns at the same time)
(5) Loading data to a table with LOB columns (using a LOB column input file)
(6) Loading data to a table with repetition columns
(7) Loading data to a table using a plug-in
(8) Loading data into an audit trail table

(1) Loading data to a table

Example 10
This example loads data to table TABLE1.
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:
    CREATE INDEX INDEX1 ON TABLE1(C1) IN PDBUSER03
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example loads data to the table (TABLE1).
control_file: Name of the control information file
Contents of the control information file (control_file)

source fes1:/usr/load_file1 error=/usr/err1          1
index INDEX1 /usr/index_inf1                         2
sort bes1 /usr/sortwork                              3

Explanation
  1. Specifies the input data file and the error information file to which error information is to be output in the event of an error in the input data:
    fes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
    /usr/err1: Name of the error information file
  2. Specifies the index information file to which index information is to be output:
    INDEX1: Index identifier
    /usr/index_inf1: Name of the index information file
  3. 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

(2) Loading data to a row-partitioned table in units of tables

Example 11
This example loads data to table TABLE1 in units of tables.
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
The example loads data to a row-partitioned table (TABLE1) in units of tables.
control_file: Name of the control information file
Contents of the control information file (control_file)

source fes1:/usr/load_file1 error=/usr/err1          1
idxwork bes1 /usr/idx_file                           2
idxwork bes2 /usr/idx_file                           2
sort bes1 /usr/sortwork                              3
sort bes2 /usr/sortwork                              3

Explanation
  1. Specifies the input data file and the error information file to which error information is to be output in the event of an error in the input data:
    fes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
    /usr/err1: Name of the error information file
  2. Specifies the directory where index information files are to be created:
    bes1, bes2: Names of the servers where index information files are to be created
    /usr/idx_file: Name of the directory where the index information files are to be created
  3. Specifies the work directory for sorting:
    bes1, bes2: Names of the servers where the sort work file is to be created
    /usr/sortwork: Name of the directory in which the sort work file is created

(3) Loading data to a row-partitioned table in units of RDAREAs

Example 12
This example loads data to a row-partitioned table (TABLE1) in units of RDAREAs.
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]
(a) Loading data to PDBUSER01
Explanation of the command
The example loads data to a row-partitioned table (TABLE1) in units of RDAREAs (PDBUSER01).
control_file1: Name of the control information file
Contents of the control information file (control_file1)

source PDBUSER01 bes1:/usr/load_file1 error=/usr/err1          1
index INDEX1 PDBUSER03 /usr/index_inf1                         2
index INDEX2 PDBUSER04 /usr/index_inf2                         2
sort bes1 /usr/sortwork                                        3

Explanation
  1. Specifies the input data file and the error information file to which error information is to be output in the event of an error in the input data:
    PDBUSER01: Name of the RDAREA that contains the table subject to data loading
    bes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
    /usr/err1: Name of the error information file
  2. Specifies the index information files to which index information is to be output:
    INDEX1, INDEX2: Index identifiers
    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: 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
(b) Loading data to PDBUSER02
Explanation of the command
The example loads data to a row-partitioned table (TABLE1) in units of RDAREAs (PDBUSER02).
control_file2: Name of the control information file
Contents of the control information file (control_file2)

source PDBUSER02 bes2:/usr/load_file2 error=/usr/err2          1
index INDEX1 PDBUSER05 /usr/index_inf3                         2
index INDEX2 PDBUSER06 /usr/index_inf4                         2
sort bes2 /usr/sortwork                                        3

Explanation
  1. Specifies the input data file and the error information file to which error information is to be output in the event of an error in the input data:
    PDBUSER02: Name of the RDAREA that contains the table subject to data loading
    bes2: Name of the server containing the input data file
    /usr/load_file2: Name of the input data file
    /usr/err2: Name of the error information file
  2. Specifies the index information files to which index information is to be output:
    INDEX1, INDEX2: Index identifiers
    PDBUSER05, PDBUSER06: Names of the index storage RDAREAs
    /usr/index_inf3, /usr/index_inf4: Names of the 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

(4) Loading data to a table with LOB columns (loading data to LOB RDAREA and LOB columns at the same time)

Example 13
For a table with LOB columns (TABLE2), this example loads data to both the LOB column structure base table and LOB columns at the same time.
The example assumes that the following table and index have been defined:
  • Table definition:

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

  • Index definition (partitioning key index):

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

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example loads data to a table with LOB columns (TABLE2) in units of RDAREAs (PDBUSER01, LOBUSER01).
control_file: Name of the control information file
Contents of the control information file (control_file)

source PDBUSER01 bes1:/usr/load_file1 error=/usr/err1          1
index INDEX2 PDBUSER03 /usr/index_inf1                         2
sort bes1 /usr/sortwork                                        3
lobdata /usr/lob                                               4
lobmid /usr/lobmid_file1                                       5

Explanation
  1. Specifies the input data file and the error information file to which error information is to be output in the event of an error in the input data:
    PDBUSER01: Name of the RDAREA that contains the table subject to data loading
    bes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
    Contents of the input data file (DAT format):
    1,lobfile1
    2,lobfile2
    3,lobfile3
    /usr/err1: Name of the error information file
  2. Specifies the index information file to which index information is to be output.
    INDEX2: Index identifier
    PDBUSER03: Name of the index storage RDAREA
    /usr/index_inf1: Name of the index information file
  3. 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
  4. Specifies the directory containing the LOB input files:
    /usr/lob: pathname of the LOB Input files
  5. Specifies the file to which information required for loading LOB column data is to be output when loading data to the LOB column structure base table (this file is used as is when loading data to the LOB columns):
    /usr/lobmid_file1: Name of the LOB middle file

(5) Loading data to a table with LOB columns (using a LOB column input file)

Example 14
For a table with LOB columns (TABLE2), this example loads data to both the LOB column structure base table and LOB columns at the same time using a LOB column input file to store data to the LOB columns.
The example assumes that the following table and index have been defined:
  • Table definition:

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

  • Index definition (partitioning key index):

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

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example loads data to a table with LOB columns (TABLE2).
-k c: Specification for using a LOB column input file
control_file: Name of the control information file
Contents of the control information file (control_file)

source fes1:/usr/load_file1                          1
index INDEX2 PDUSER03 /usr/index_inf1                2
index INDEX2 PDUSER04 /usr/index_inf2                2
sort bes1 /usr/sortwork                              3
sort bes2 /usr/sortwork                              3
lobdata /usr/collob                                  4
lobcolumn collob_file                                5
lobmid /usr/lobmid_file                              6

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
  2. Specifies the index information files to which index information is to be 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 directory containing the LOB column input file:
    /usr/collob: Name of the directory containing the LOB column input file
  5. Specifies the LOB column input file:
    collob_file: Name of the LOB column input file
  6. Specifies the LOB middle file:
    /usr/lobmid_file: Name of the LOB middle file

(6) Loading data to a table with repetition columns

Example 15
This example loads data to a table with repetition columns (SCORE_TABLE) without checking the order of cluster key values.
The example assumes that the following table has been defined:
  • Table definition:

CREATE TABLE SCORE_TABLE
   (NUMBER CHAR(6) UNIQUE CLUSTER KEY IN
    ((PDUSER03),(PDUSER04))
    NAME NCHAR(10),
    SUBJECT_NAME NCHAR(8) ARRAY[5],
    SCORE INT ARRAY[5])
 IN ((PDUSER01) < =  1Z9999,(PDUSER02))

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Contents of the input data file (DAT format)
[Figure]
Explanation of the command
The example loads data to a table with repetition columns (SCORE_TABLE) in units of tables.
-x: Specification for not checking the order of cluster key values
control_file: Name of the control information file
Contents of the control information file (control_file)

source fes1:/usr/arrload_file1                1
idxwork bes1 /usr/idx_file                    2
idxwork bes2 /usr/idx_file                    2
sort bes1 /usr/sortwork                       3
sort bes2 /usr/sortwork                       3
array elmtype=ff nullset=c                    4

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/arrload_file1: Name of the input data file
  2. Specifies the directory where index information files are to be created:
    bes1, bes2: Names of the servers used to create index information files
    /usr/idx_file: Name of the directory 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 files
    /usr/sortwork: Name of the directory in which the sort work file is created
  4. Specifies that data is to be loaded to a table with repetition columns:
    ff: Arrayed data format
    c: Specification for not storing any data for the corresponding element

(7) Loading data to a table using a plug-in

Example 16
This example loads data to a table (TBL01) with an abstract data type (SGMLTEXT) using the HiRDB Text Search Plug-in.
The example assumes that the following table and index have been defined:
  • Table definition:

CREATE TABLE TBL01(C1 INTEGER,C2 VARCHAR(10),C3 SGMLTEXT ALLOCATE
                     (SGMLTEXT IN LOBUSER01) PLUGIN ...)
                     IN PDBUSER01

  • Plug-in index definition:

CREATE INDEX INDEX1 USING TYPE NGRAM ON TBL01(C3) IN PDBUSER02

The following is the information about the constructor function of SGMLTEXT type:
  • Name of the function: SGMLTEXT, input: BLOB, output: the SGMLTEXT type
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Contents of the input data file (DAT format)

1,"HiRDB",/usr/lob/file01
-1,"",/usr/lob/file02
999,"HITACHI",/usr/lob/file03
       :

Explanation of the command
The example loads data to a table with an abstract data type (TBL01).
column_inf: Name of the column structure information file
control_file: Name of the control information file
Contents of the column structure information file (column_inf)

C1                                     1
C2                                     1
C3,func=(SGMLTEXT,param=blob)          1,2

Explanation
  1. Stores the input data in TBL01's columns (C1, C2, and C3).
  2. Calls the constructor function and stores data in the column of abstract data type (C3):
    SGMLTEXT: Name of the constructor function
    blob: Type of the argument that is passed to the constructor function
Contents of the control information file (control_file)

source fes1:/usr/load_file1               1
lobdata                                   2

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
  2. Specifies the LOB input files.
Example 17
This example loads data to a table (TBL02) with an abstract data type (GEOMETRY).
The example assumes that the following table has been defined:
  • Table definition:

 CREATE TABLE TBL02(C1 INTEGER,C2 DECIMAL(7,0),C3 GEOMETRY)
                       IN PDBUSER02

The following is the information about the constructor function of GEOMETRY type:
  • Name of the function: GEOMETRY, input: VARCHAR, output: GEOMETRY type
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Contents of the input data file (fixed-size data format)
[Figure]
Explanation of the command
The example loads data to a table (TBL02) with an abstract data type.
column_inf2: Name of the column structure information file
control_file2: Name of the control information file
Contents of the column structure information file (column_inf2)

C1,type=integer                                            1
C2,type=dec(7,0)                                           2
C3,func=(GEOMETRY,param=varchar,type=varchar(10))          3

Explanation
  1. Stores INTEGER data in column C1.
  2. Stores DECIMAL(7,0) data in column C2.
  3. Stores the value generated by passing varchar(10) data to the GEOMETRY constructor function whose input parameter is varchar in the column of an abstract data type (C3).
    GEOMETRY: Name of the constructor function
    varchar: Type of the argument that is passed to the constructor function
Contents of the control information file (control_file2)

source fes1:/usr/load_file2                     1

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/load_file2: Name of the input data file
Example 18
This example loads data to a table (TBL03) with an abstract data type (SGMLTEXT) and LOB columns.
The example assumes that the following table and index have been defined:
  • Table definition:

CREATE TABLE TBL03(C1 INTEGER,C2 BLOB(4096) IN LOBUSER01),
                   C3 SGMLTEXT ALLOCATE
                     (SGMLTEXT IN LOBUSER02) PLUGIN ...)
                     IN PDBUSER03

  • Plug-in index definition:

CREATE INDEX INDEX1 USING TYPE NGRAM ON TBL03(C3) IN PDBUSER04

Following is the information about the construction function of SGMLTEXT type:
  • Name of the function: SGMLTEXT, input: BLOB, output: SGMLTEXT type
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Contents of the input data file (binary format)
[Figure]
Explanation of the command
The example loads data to a table with an abstract data type (TBL03).
-k v: Specifies that the input data file is to contain the LOB parameters and the names of the LOB input files for the data to be stored in the LOB columns.
control_file3: Name of the control information file
Contents of the control information file (control_file3)

source fes1:/usr/load_file3                  1
lobmid /usr/mid_file                         2
lobdata /usr                                 3

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/load_file3: Name of the input data file
  2. Specifies the LOB middle file:
    /usr/mid_file: Name of the LOB middle file
  3. Specifies the LOB input files:
    /usr: Name of the directory that contains LOB input files
Example 19
This example loads data to a table (TBL04) with an abstract data type (SGMLTEXT) and LOB columns. First, it loads data to the LOB column structure base table and abstract data type, then loads data to the LOB columns concurrently at each back-end server.
The example assumes that the following table and index have been defined:
  • Table definition:

CREATE TABLE TBL04(C1 INTEGER,C2 BLOB(100) IN
                  (LOB11),(LOB12)),
                  C3 SGMLTEXT ALLOCATE
                     (SGMLTEXT IN ((LOB21),(LOB22))
                      PLUGIN...)
                      IN ((PDBUSER01) C1 >
                      1000,(PDBUSER02))

  • Plug-in index definition:

CREATE INDEX INDEX1 USING TYPE NGRAM ON TBL04(C3) IN ((PDBUSER03),(PDBUSER04))

Following is the information about the constructor function of the SGMLTEXT type:
  • Name of the function: SGMLTEXT, input: BLOB, output: SGMLTEXT type
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
(a) Loading data to the LOB column structure base table and abstract data type
Explanation of the command
The example loads data to the LOB column structure base table and abstract data type of a table with an abstract data type (TBL04).
-b: Specification for the input data file in the binary format
-k v: Specifies that the input data file is to contain the LOB parameters and the names of the LOB input files for the data to be stored in the LOB columns.
control_file1: Name of the control information file
Contents of the control information file (control_file1)

source fes1:/usr/load_file1 err=/usr/err          1
idxwork bes1 /usr/idx_file                        2
idxwork bes2 /usr/idx_file                        2
lobmid /usr/lobmid                                3

Explanation
  1. Specifies the input data file:
    fes1: Name of the server containing the input data file
    /usr/load_file1: Name of the input data file
    /usr/err: Name of the error information file
  2. Specifies the directory in which index information files are to be created:
    bes1, bes2: Names of the servers used to create index information files
    /usr/idxfile: Name of the directory in which index information files are created
  3. Specifies the LOB middle files:
    /usr/lobmid: Name of the LOB middle file
(b) Loading data to LOB columns
Explanation of the command
The example loads data to the LOB columns of the table with abstract data type (TBL04) concurrently at each back-end server.
-b: Specification for the input data file in the binary format
-k v: Specifies that LOB input files are to be provided for each LOB data item for the LOB columns and that an input data file is to be provided for the input parameters of the constructor function that generates values for the columns of the abstract data type.
control_file2, control_file3: Names of the control information files
Contents of the control information file (control_file2)

lobdata /usr/lob1                                      1
lobmid LOB11 fes1:/usr/lobmid error=/usr/err1          2

Explanation
  1. Specifies the input data file:
    /usr/lob1: Name of the directory containing the LOB input files
  2. Specifies the LOB middle file:
    LOB11: Name of the user LOB RDAREA containing the LOB columns
    fes1: Name of the server containing the LOB middle file
    /usr/lobmid: Name of the LOB middle file
    /usr/err1: Name of the error information file
Contents of the control information file (control_file3)

lobdata /usr/lob2                                      1
lobmid LOB12 fes1:/usr/lobmid error=/usr/err2          2

Explanation
  1. Specifies the LOB input files:
    /usr/lob2: Name of the directory containing the LOB input files
  2. Specifies the LOB middle file:
    LOB12: Name of the user LOB RDAREA containing the LOB columns
    fes1: Name of the server containing the LOB middle file
    /usr/lobmid: Name of the LOB middle file
    /usr/err2: Name of the error information file

(8) Loading data into an audit trail table

Example 20
This example loads data in data loading wait status from audit trail files located in unit 2 (un02) into the audit trail table (SQL_AUDIT_TRAIL) defined on the back-end server (bes1) at unit 1 (un01).
Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example loads data from audit trail files (/hirdb/audit/un02) waiting for data loading into the audit trail table (SQL_AUDIT_TRAIL).
-b and -W: Options required in order to load data into an audit trail table
control_file: Name of the control information file
Contents of the control information file (control_file)

source fes2:(uoc)                                  1
srcuoc param='dir=/hirdb/audit/un02,file=all'      2

Explanation
  1. Specifies the server name (fes2) for the unit that contains the audit trail files subject to data loading and the audit trail data registration UOC.
  2. Specifies parameters to be passed to the audit trail data registration UOC:
    dir=/hirdb/audit/un02: HiRDB file name of the audit trail files
    file=all: Specifies all audit trail files waiting for data loading