Loading data from a remote client system

You can load data from a flat file (external table) or a named pipe on a remote client into a Db2® database table on a Db2 server.

Load data by using named external table

Insert data from the external table into the database table on the Db2 server by issuing the following command.

INSERT INTO EMPLOYEE SELECT * FROM EXT_EMPLOYEE

Load data by using transient external table

Insert data from a transient external table into the database table on the Db2 server by issuing the following command.

INSERT INTO EMPLOYEE SELECT * FROM external '/tmp/employee.dat' USING (delimiter ',' MAXERRORS 10 SOCKETBUFSIZE 30000 REMOTESOURCE 'JDBC' LOGDIR '/logs' )

Load operation errors are logged in detail in a log file, and bad rows details are logged in a bad file. If LOGDIR was not specified in create external table SQL statement, the IBM® Data Server Driver for JDBC and SQLJ writes log files or bad files in to the external data file directory. If external data file directory is not accessible, it writes the file into driver's output directory.

The default output directory is the system temp location (for example, on windows it is usually c:\user\home\AppData\Local\Temp and on Linux or AIX, it is /tmp). The default output directory can be changed by configuring the db2.jcc.outputDirectory global property.

If a log or bad file with the same name exists, the new log or bad files contents get appended to it. The IBM Data Server Driver for JDBC and SQLJ does not attempt to generate a unique file name on its own.

The IBM Data Server Driver for JDBC and SQLJ does not provide any methods to fetch the log or bad files that are generated for an SQL execution. Any log or bad files that are generated are written to the current output location.

Fetching Log/Bad file names

As a part of JCC and the DB2Statement Interface, the class ExternalTableResult exposes the log and bad file names, and the error information that uses the APIs described in table 1:

Table 1. ExternalTableResult Methods
ExternalTableResult Methods	Purpose
Throwable getError ()	Returns the first error that occurred while writing the log file, or bad file.
String getLogFileName ()	Returns the name of the log file.
String getBadFileName()	Returns the name of the bad file.

Applications can invoke these public methods on the DB2Statement Interface as shown in the following example:

 Statement stmt = con.createStatement();
stmt.execute(str);
ExternalTableResult lr = ((DB2Statement) stmt).getExternalTableResult ();
 
String logFileName = lr.getLogFileName ();
String badFileName = lr.getBadFileName ();
Throwable error = lr.getError();