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,8192 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
   8192: Size of buffer for sorting (in KB)
   

(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,8192 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
   8192: Size of buffer for sorting (in KB)
   

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

(a) Loading data to TABLE1

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,8192 3

Explanation:

1. Specifies the input data file and the error information file to which error information (up to 1000 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
   8192: Size of buffer for sorting (in KB)
   

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,8192 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
   8192: Size of buffer for sorting (in KB)
   

(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,8192 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
   8192: Size of buffer for sorting (in KB)
   

(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,8192 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
   8192: Size of buffer for sorting (in KB)
   

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,8192 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
   8192: Size of buffer for sorting (in KB)
   
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,8192 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
   8192: Size of buffer for sorting (in KB)
   
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