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

Relationship between input/output files and RDAREAs

(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

  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.

303030303130304142434445464748494a00000001 202020202020206162636465666768696a00000002 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.

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

Relationship between input/output files and RDAREAs

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
   

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

Relationship between input/output files and RDAREAs

(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.

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

Relationship between input/output files and RDAREAs

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
   

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

Relationship between input/output files and RDAREAs

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
   

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

Relationship between input/output files and RDAREAs

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
   

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

Relationship between input/output files and RDAREAs

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
   

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

Relationship between input/output files and RDAREAs

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
   

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

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