Loading data using CLPPlus

You can use the SQL CALL statement with the ADMIN_CMD procedure in the command line processor plus (CLPPlus) interface to run the LOAD command. CLPPlus is included in the Db2® Warehouse container.

Before you begin

Ensure that you have root authority.

The easiest approach is to put the data in /mnt/clusterfs/scratch/datafile, where datafile specifies the file that contains your data. The instructions in this topic described how to load data from this location. However, if you have large data sets on an NFS share that you want to load, it's preferable to mount that share on the host instead of copying it to the host. For instructions, see the information about using CLP for remote data loading in Running the LOAD command.

About this task

This task describes how to load data into an existing table.

Procedure

Initiate an interactive CLPPlus session by issuing the following Docker or Podman command on the host command line:

docker exec -it Db2wh clpplus userID/password@localhost:50000/bludb

podman exec -it Db2wh clpplus userID/password@localhost:50000/bludb

Issue the LOAD command by using the ADMIN_CMD procedure, specifying the container directory (/mnt/clusterfs/scratch) where the host directory is mounted. If you want to be able to view messages from the load operation, specify the MESSAGES ON SERVER parameter.
```
CALL SYSPROC.ADMIN_CMD('LOAD FROM source OF filetype [MODIFIED BY filetype_mod] 
[WARNINGCOUNT n] [MESSAGES ON SERVER] operation INTO tablename NONRECOVERABLE');
```
where:
- source is a file name, pipe name, or device.
- operation is INSERT or TERMINATE.
You must enclose case-sensitive names and double-byte character set (DBCS) names inside a backslash (\) and double quotation delimiter, for example, \" MyTabLe \".
If you specified the MESSAGES ON SERVER parameter, execute the SELECT statement in the command output to obtain the messages.
An example follows:
```
SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('15060085_336852192_BLUADMIN')) AS MSG 
```
If you specified the MESSAGES ON SERVER parameter and no longer need the messages, use the CALL statement in the command output to delete them.
An example follows:
```
CALL SYSPROC.ADMIN_REMOVE_MSGS('15060085_336852192_BLUADMIN')
```

Example

The following sample output was generated by executing the SELECT statement in the output of a LOAD command:


SQLCODE   MSG
--------- --------------------------------------------------
SQL27966W The DB2_LOAD_COPY_NO_OVERRIDE registry variable value "COPY YES TO /mnt/blumeta0/db2/copy" overrides the COPY NO parameter specified in the Load.
SQL3109N  The utility is beginning to load data from file"/mnt/bludata0/data.del".
SQL3500W  The utility is beginning the "LOAD" phase at time "10/12/2016 17:12:28.424467".
SQL3519W  Begin Load Consistency Point. Input record count = "0".
SQL3520W  Load Consistency Point was successful.
SQL3119W  The value in row "F0-1" and column "1" could not be converted to an integer value, so a null value was loaded instead.
SQL3119W  The value in row "F0-4" and column "1" could not be converted to an integer value, so a null value was loaded instead.
SQL3110N  The utility has completed processing. "15" rows were read from the input file.
SQL3519W  Begin Load Consistency Point. Input record count = "15".
SQL3520W  Load Consistency Point was successful.
SQL3515W  The utility has finished the "LOAD" phase at time "10/12/2016 17:12:28.478799".
SQL3500W  The utility is beginning the "BUILD" phase at time "10/12/2016 17:12:28.480188".
SQL3213I  The indexing mode is "INCREMENTAL".
SQL3515W  The utility has finished the "BUILD" phase at time "10/12/2016 17:12:28.497478".
SQL3107W  At least one warning message was encountered during LOAD processing.