LOAD CLPPlus command by using external tables

Invocation

You must run the LOAD command from the CLPPlus interface.

Authorization

Required connection

Command syntax

Command parameters

EXTERNAL

Specifies that the LOAD command uses external table operations.

FROM filename or pipename

Specifies the file or pipe that contains the data that is being loaded.

NOT LOGGED INITIALLY

Data that is loaded into the table by a LOAD operation that is in the same unit of work is not logged.

OPTIONS(options-string)

Specifies the external table options that control the processing of the LOAD operation. Refer to the target server's external table options for details.

INTO table-name

Specifies the database table into which the data is to be loaded.

insert-column-names

Specifies the comma-separated table columns into which the data is to be loaded.

Usage notes

LOG and BAD files contain information about records that are successfully loaded, rejected, or skipped. The location of these files depends on the location of the server:

Remote server (REMOTESOURCE 'LOCAL' is not specified)

LOGDIR specifies the directory to which ~.log and ~.bad files are generated. If log and bad files are generated, the server returns the full path to store them by including the specified LOGDIR in the log and bad file path that is returned to the driver.

If the LOGDIR option is not defined, the JDBC driver’s output directory is used to store the log and bad files. This default output directory is the operating system default temp directory or the configured db2.jcc.outputDirectory directory.

Local server (REMOTESOURCE 'LOCAL' is specified)

LOGDIR specifies the directory to which ~.log and ~.bad files are generated. When used with SWIFT or S3, the files are located in the object store where the paths of the log and bad files are relative to the top of the bucket or container.

Attention: In Db2 Version 11.5 Mod Pack 1 or later versions, LOGDIR can be used with AZURE. The files are located in the object store where the paths of the log and bad files are relative to the top of the bucket or container.

ERROR_LOG can be used as a synonym for the LOGDIR option. If the LOGDIR option is not defined, then the default location is the location of the data files.

Output of LOAD CLPPlus command by using external tables

Examples

Example 1

In this example, the data file is on a client that is connected to the database. The data file is loaded remotely.

The target table is DB2TBL1, whose columns are ID [type INTEGER] and NAME [type varchar(50)].

The path and name of the data file is C:\data\et.txt and has the following content:

1, 'Name0'
2, 'Name1'
3, 'Name2'

1. Start CLPPlus.
2. Connect to the target database.
3. Run the LOAD command:

   LOAD EXTERNAL FROM C:\data\et.txt 
      OPTIONS (DELIMITER ',' SOCKETBUFSIZE 30000 LOGDIR 'C:\data\' MAXERRORS 20 REMOTESOURCE 'JDBC') 
      INTO DB2TBL1;

   Note: REMOTESOURCE 'JDBC' is a mandatory option for a remote load.
4. Data is successfully loaded in target table with following output message:

   Total number of rows loaded: 3
   
   DB250000I: The command completed successfully.

Example 2

In this example, the data file exists on the target server and is loaded locally.

The target table is DB2TBL1, whose columns are ID [type INTEGER] and NAME [type VARCHAR(50)].

The path and name of the data file is /home/regres1/et/et1.txt and has the following content:

1, 'Name0'
2, 'Name1'
"3", 'Name2'

1. Start CLPPlus.
2. Connect to the target database.
3. Run the LOAD command:

   LOAD EXTERNAL FROM /home/regres1/et/et1.txt 
      OPTIONS (DELIMITER ',' SOCKETBUFSIZE 30000 LOGDIR /home/regres1/et/' MAXERRORS 20) 
      INTO DB2TBL1;

   Note: The keyword REMOTESOURCE cannot be used for a local load.
4. Partial data is successfully loaded in target table with following output message:

   SQL5108W Loading of data to a Hadoop table or processing of data in an external table completed. 
   Number of rows processed: "2". Number of source records: "3".
   If the source was a file, number of skipped lines: "0". 
   Number of rejectedsource records: "1". 
   Job or file identifier:
   "SAMPLE.REGRES1.SYSTET46273.019412".
   
   DB250000I: The command completed successfully.

Example 3

In this example, the data file that is in the AWS S3 object store.

The target table is DB2TBL1, whose columns are ID [type INTEGER] and NAME [type VARCHAR(50)].

The path and name of the data file is C:\data\et.txt and has the following content:

1, 'Name0'
2, 'Name1'
3, 'Name2'

1. Start CLPPlus.
2. Connect to the target database.
3. Run the LOAD command:

   LOAD EXTERNAL FROM /home/regres1/et/et1.txt 
      OPTIONS (S3 (ENDPOINT, AUTHKEY1, AUTHKEY2, BUCKET), 
         DELIMITER ',' SOCKETBUFSIZE 30000 LOGDIR  /home/regres1/et/' MAXERRORS 20) 
      INTO DB2TBL1;

   Note: The keyword REMOTESOURCE cannot be used with AWS S3 object store.
4. Data is successfully loaded in target table with following output message:

   Total number of rows loaded: 3
   
   DB250000I: The command completed successfully.

Example 4

In this example, the data file is in the IBM® Cloud Object Storage.

The target table is DB2TBL1, whose columns are ID [type INTEGER] and NAME [type VARCHAR(50)].

The path and name of the data file is C:\data\et.txt and has the following content:

1, 'Name0'
2, 'Name1'
3, 'Name2'

1. Start CLPPlus.
2. Connect to the target database.
3. Run the LOAD command:

   LOAD EXTERNAL FROM /home/regres1/et/et1.txt 
      OPTIONS (SWIFT (ENDPOINT, AUTHKEY1, AUTHKEY2, CONTAINER), 
         DELIMITER ',' SOCKETBUFSIZE 30000 LOGDIR  /home/regres1/et/' MAXERRORS 20) 
      INTO DB2TBL1;

   Note: The keyword REMOTESOURCE cannot be used with IBM Cloud Object Storage.
4. Data is successfully loaded in target table with following output message:

   Total number of rows loaded: 3
   
   DB250000I: The command completed successfully.

Example 5

This example demonstrates the use of the NOT LOGGED INITIALLY option.

The target table is DB2TBL1, whose columns are ID [type INTEGER] and NAME [type VARCHAR(50)].

The path and name of the data file is C:\data\et.txt and has the following content:

1, 'Name0'
2, 'Name1'
3, 'Name2'

1. Start CLPPlus.
2. Connect to the target database.
3. Run the LOAD command:

   LOAD EXTERNAL FROM 'C:\data\et1.txt' NOT LOGGED INITIALLY 
      OPTIONS (DELIMITER ',' LOGDIR 'C:\data\' MAXERRORS 20 SOCKETBUFSIZE 30000 REMOTESOURCE 'JDBC') 
      INTO DB2TBL1(ID, NAME);

4. Data is successfully loaded in target table with following output message:

   Total number of rows loaded: 3
   
   DB250000I: The command completed successfully.