5.14.1 HiRDB single 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 using an input data file on a utility special unit
(5) Loading data to LOB column structure base tables and LOB columns
(6) Loading data to a LOB column structure base table only
(7) Loading data to LOB columns only
(8) Loading data into an audit trail table
(9) Loading data using the automatic numbering facility

(1) Loading data to a table

Example 1
This example loads data to table TABLE1. The explanation is based on the input data file in the DAT format and in the fixed-size data format.
The example assumes that the following table and index have been defined:
  • Table definition:

CREATE TABLE TABLE1(C1 INT,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]
(a) Input data file in the DAT format
Contents of the input file

1,ABCDEFG,99999,
2,a,-1,
3,XYZ,100,
    :

Explanation of the command
The example loads data to the table (TABLE1).
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                  1
*skipdata*          2

Explanation
  1. Stores input data in TABLE1 in the order of the columns (C1, C2, and C3).
  2. Specifies a skipdata statement to ignore the separator character at the end of a line.
Contents of the control information file (control_file)

source /usr/load_file1 error=/usr/err1          1
index INDEX1 /usr/index_inf1                    2
sort /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:
    /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:
    /usr/sortwork: Name of the directory in which the sort work file is created
(b) Input data file in the fixed-size data format
Contents of the input file

1  ABCDEFG  99999
2  a        -1
3  XYZ      100
      :

Note: there is a linefeed code at the end of the line.

Explanation of the command
The example loads data to the table (TABLE1).
-a: Specifies that the input data file is in the fixed-size data format.
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,type=char(3)              1
C2,type=char(8)              1
C3,type=char(6)              1
*skipdata*,length=1          2

Explanation
  1. Specifies the data type of the columns in the input data.
  2. Specifies a skipdata statement to ignore the linefeed code.
Contents of the control information file (control_file)

source /usr/load_file1 error=/usr/err1          1
index INDEX1 /usr/index_inf1                    2
sort /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:
    /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:
    /usr/sortwork: Name of the directory in which the sort work file is created
(c) Input data file in a data format specific to the fixed-size data format
This example loads data into table TABLE2. The explanation is based on the input data file in a data format unique to the fixed-size data format.
  • Table definition

 CREATE TABLE TABLE2(C1 DECIMAL(7,2),
                     C2 CHAR(10),
                     C3 DECIMAL(10,4));

Contents of the input file (/usr/input_file)
The contents of the input data file referenced by a binary editor are shown below. The contents are in hexadecimal. The first 7 bytes of data are stored in column C1, bytes 8 through 17 are stored in column C2, and bytes 18 through 22 are stored in column C3.

303030303130304142434445464748494​a00000001​
202020202020206162636465666768696​a00000002​
303030303330302020202020202020202000000003​

Command line to be executed

pdload -a -c column_inf TABLE2 control_file

Explanation of the command
The example loads data into the table (TABLE2).
-a: Specifies that the input data file is in fixed-size data format.
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,type=adec(7,2)            1
C2,type=char(10)             2
C3,type=bdec(4)              3

Explanation
  1. Data is specified as numeric values in character string format and is stored in DECIMAL(7,2) with a length of 7 bytes, no decimal point, and decimal places in the trailing 2 bytes.
  2. Data with a length of 10 bytes is stored in CHAR(10).
  3. Binary data stored in a 4-byte area is stored in DECIMAL(10,4).
Contents of the control information file (control_file)

option allspace=zero                            1
source /usr/input_file error=/usr/err           2

Explanation
  1. Stores 0 if the input data to be stored in a DECIMAL column is the space.
  2. Specifies the input data file and error information file to which error information is to be output.

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

Example 2
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),(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
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 /usr/load_file1 error=/usr/err1 errdata=/usr/err_data,1000   1
idxwork /usr/idx_file                                               2
sort /usr/sortwork                                              3

Explanation
  1. Specifies the input data file and the error information file to which error information (up to 1,000 entries) is to be output in the event of an error in the input data:
    /usr/load_file1: Name of the input data file
    /usr/err1: Name of the error information file
    /usr/err_data: Name of the error data file
  2. Specifies the directory 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:
    /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 3
This example loads data to a row-partitioned table (TABLE1) in units of RDAREAs. It executes data loading twice on PDBUSER01 and PDBUSER02.
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),(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) Loading data to PDBUSER01
Explanation of the command
The example loads data to a row-partitioned table (TABLE1) in units of RDAREAs (PDBUSER01).
control_file: Name of the control information file
Contents of the control information file (control_file)

source PDBUSER01 /usr/load_file1 error=/usr/err1          1
idxwork /usr/idx_file                                     2
sort /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
    /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:
    /usr/idx_file: Name of the directory where the index information files are to be created
  3. Specifies the work directory for sorting:
    /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_file: Name of the control information file
Contents of the control information file (control_file)

source PDBUSER02 /usr/load_file2 error=/usr/err2          1
idxwork /usr/idx_file                                     2
sort /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
    /usr/load_file2: Name of the input data file
    /usr/err2: Name of the error information file
  2. Specifies the directory 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:
    /usr/sortwork: Name of the directory in which the sort work file is created
(c) Creating INDEX2

You cannot use INDEX2 as is.

Execute batch index creation with pdrorg (specifying -k ixmk) using index information files 3 and 4 to which index information has been output.

(4) Loading data using an input data file on a utility special unit

Example 4
This example loads data to a row-partitioned table (TABLE1). The input data file is located on a utility special unit.
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) C1 > 10,(PDBUSER02))

  • Index definition:

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

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 host1:/usr/load_file1 error=/usr/err1          1
index INDEX1 PDBUSER03 /usr/index_inf1                2
index INDEX1 PDBUSER04 /usr/index_inf2                2
sort /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:
    host1: Name of the host 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
    PDBUSER03, PDBUSER04: Name of the index storage RDAREAs
    /usr/index_inf1, /usr/index_inf2: Name of the index information files
  3. Specifies the work directory for sorting:
    /usr/sortwork: Name of the directory in which sort work file is created

(5) Loading data to LOB column structure base tables and LOB columns

Example 5
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) IN PDBUSER01

  • Index definition:

CREATE INDEX INDEX2 ON TABLE2(C1) IN PDBUSER02

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 tables.
control_file: Name of the control information file
Contents of the control information file (control_file)

source /usr/load_file1 error=/usr/err1                         1
index INDEX2 /usr/index_inf1                                   2
sort /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:
    /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
    /usr/index_inf1: Names of the index information file
  3. Specifies the work directory for sorting:
    /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

(6) Loading data to a LOB column structure base table only

Example 6
For a table with LOB columns (TABLE2), this example loads data to the LOB column structure base table only.
The example assumes that the following table and indexes have been defined:
  • Table definition:

CREATE TABLE TABLE2(C1 INT,C2 BLOB IN LOBUSER01) IN PDBUSER01

  • Index definition:

CREATE INDEX INDEX2 ON TABLE2(C1) IN PDBUSER02

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 tables.
control_file: Name of the control information file
Contents of the control information file (control_file)

source /usr/load_file1 error=/usr/err1                         1
index INDEX2 /usr/index_inf1                                   2
sort /usr/sortwork                                             3
lobmid /usr/lobmid_file1                                       4

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:
    /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
    /usr/index_inf1: Name of the index information file
  3. Specifies the work directory for sorting:
    /usr/sortwork: Name of the directory in which the sort work file is created
  4. 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:
    /usr/lobmid_file1: Name of the LOB middle file

(7) Loading data to LOB columns only

Example 7
For a table with LOB columns (TABLE2), this example loads data to the LOB columns only.
The example assumes that the following table and index have been defined:
  • Table definition:

CREATE TABLE TABLE2(C1 INT,C2 BLOB IN LOBUSER01) IN PDBUSER01

  • Index definition:

CREATE INDEX INDEX2 ON TABLE2(C1) IN PDBUSER02

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 tables.
control_file: Name of the control information file
Contents of the control information file (control_file)

lobdata /usr/lob                                                 1
lobmid /usr/lobmid_file1 error=/usr/err1                         2

Explanation
  1. Specifies the directory containing the input data file.
    /usr/lob: Pathname of the LOB input files
    The example uses the following input data file while loading data to the LOB column structure base table:
    Contents of the input data file (DAT format):
    1,lobfile1
    2,lobfile2
    3,lobfile3
  2. Specifies the LOB middle file containing the information required for loading data to a LOB column that was created during data loading to the LOB column structure base table, and the error information file to which error information is to be output in the event of a LOB input error:
    /usr/lobmid_file1: Name of the LOB middle file
    /usr/err1: Name of the error information file

(8) Loading data into an audit trail table

Example 8

This example loads data from audit trail files waiting for data loading into the audit trail table SQL_AUDIT_TRAIL. The example assumes that no index has been defined for the audit trail table.

Overview
[Figure]
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
This example loads data from audit trail files waiting for data loading (/hirdb/audit/file) into the audit trail table SQL_AUDIT_TRAIL.
-b and -W: Mandatory options for loading data into audit trail table
control_file: Name of the control information file
Contents of the control information file (control_file)

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

Explanation
  1. Specifies that an audit trail data registration UOC is to be used.
  2. Specifies the parameters that are to be passed to the audit trail data registration UOC.
    dir=/hirdb/audit/file: HiRDB file name of the audit trail file
    file=all: Specifies that all audit trail files waiting for data loading are to be used

(9) Loading data using the automatic numbering facility

Example 9
This example loads data into table TABLE1 using the specification unit acquisition method, and adds sequence numbers as column data in column C2.
The example assumes that the following table and sequence generator have been defined:
  • Table definition

 CREATE TABLE USER01.TABLE1(C1 INT,C2 INT,C3 VARCHAR(10)) IN PDBUSER01

  • Sequence generator definition
CREATE SEQUENCE USER01.SEQ_A1
AS INTEGER START WITH 1 INCREMENT BY 1 MAXVALUE 9999 CYCLE IN PDBUSER01
Relationship between input/output files and RDAREAs
[Figure]
Explanation of the command
The example loads data into table TABLE1.
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
C3                               1
*adddata*                        2
C2,sequence=(USER01.SEQ_A1)      3

Explanation
  1. Stores input data in columns C1 and C3 of TABLE1.
  2. Indicates the beginning of the add data statement for adding input data.
  3. Stores sequence numbers generated by the sequence generator (USER01.SEQ_A1) in column C2.
Contents of the control information file (control_file)

source /usr/load_file1           1
option seq_range=1000            2

Explanation
  1. Specifies the input data file.
    /usr/load_file1: Name of the input data file
  2. Specifies the sequence number acquisition method (specification unit acquisition method).
    seq_range=1000: Specifies acquiring 1,000 sequence numbers at a time during data loading