Example 9

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

Relationship between input/output files and RDAREAs

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

Example 10

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

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 fes1:/usr/load_file1 error=/usr/err1 1 idxwork bes1 /usr/idx_file 2 idxwork bes2 /usr/idx_file 2 sort bes1 /usr/sortwork,8192 3 sort bes2 /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:
   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
   8192: Size of buffer for sorting (in KB)
   

Example 11

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

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

Example 12

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

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 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,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:
   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
   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 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 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

Relationship between input/output files and RDAREAs

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,8192 3 sort bes2 /usr/sortwork,8192 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
   8192: Size of buffer for sorting (in KB)
   
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
   

Example 14

This example uses EasyMT to load data to a table (TABLE1). Because the input data file consists of two magnetic tape volumes, the example uses MTguide to change the magnetic tape volumes at the right time.

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

Relationship between input/output files and RDAREAs

Explanation of the command

The example loads data to a table (TABLE1) using EasyMT.

-f easymt: Specification for using EasyMT

control_file: Name of the control information file

Contents of the control information file (control_file)

mtguide use 1 source fes1:MT01 error=/usr/err_file file=load_data,vol=(vol001,vol002) 2 idxwork bes1 /usr/idx_file 3 idxwork bes2 /usr/idx_file 3 sort bes1 /usr/sortwork,8192 4 sort bes2 /usr/sortwork,8192 4

Explanation:

1. Specifies that EasyMT is to be used:
   use: Specification for using MTguide (because magnetic tape volumes need to be changed)
   
2. Specifies the input data file:
   fes1: Name of the server containing the input data file
   MT01: Device symbolic name managed by MTguide
   /usr/err_file: Name of the error information file
   load_data: Name of magnetic tape file
   (vol001, vol002): Specification for the load_data file on two magnetic tape volumes
   
3. 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/idx_file: Name of the directory in which index information files are created
   
4. Specifies the work directory for sorting:
   bes1, bes2: Names of the servers used to create sort work file
   /usr/sortwork: Name of the directory in which sort work file is created
   8192: Size of buffer for sorting (in KB)
   

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

Relationship between input/output files and RDAREAs

Contents of the input data file (DAT format)

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,8192 3 sort bes2 /usr/sortwork,8192 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
   8192: Size of buffer for sorting (in KB)
   
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
   

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

Following is the information about the constructor function of SGMLTEXT type:

• Name of the function: SGMLTEXT, input: BLOB, output: the SGMLTEXT type

Overview

Relationship between input/output files and RDAREAs

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

Following is the information about the constructor function of the GEOMETRY type:

• Name of the function: GEOMETRY, input: VARCHAR, output: GEOMETRY type

Overview

Relationship between input/output files and RDAREAs

Contents of the input data file (fixed-size data format)

Explanation of the command

The example loads data to a table with an abstract data type (TBL02).

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 that was generated by passing data varchar (10) to the GEOMETRY constructor function, the input parameter of which is varchar in the abstract data type column C3.
   GEOMETRY: Name of the constructor function
   varchar: Type of 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

Relationship between input/output files and RDAREAs

Contents of the input data file (binary format)

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

Relationship between input/output files and RDAREAs

(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