IMPORT script on IBM DB2 Cloud using RUN SQL Interface

Question

I am trying to import a JOBFILE.CSV from my hard drive into the table JOB using the RUN SQL script in IBM DB2 cloud.

CALL SYSPROC.ADMIN_CMD('IMPORT FROM "C:/DATAFILE/JOBFILE.CSV" 
OF DEL INSERT INTO JOB');

I am getting this error:

An I/O error (reason = "sqlofopn -2029060079") occurred while opening the input file.. SQLCODE=-3030, SQLSTATE= , DRIVER=4.25.1301

It seems the path that I have set is not working. As I have researched JOBFILE.CSV must be loaded first into the DB2 server before the IMPORT script could run.

By calling a stored-procedure (admin_cmd), the file must already reside on the Db2-server (and not on your client workstation). In addition to the options in the answer below, you can arrange to use the Db2 command-line interface on your workstation to access Db2 on cloud as if it was on-premises, and then you can use IMPORT, LOAD, INGEST commands from your client workstation directly. — mao

kkuduk kkuduk · Accepted Answer · 2019-08-28T05:39:17

With a file located on a local client there are two options "basic" options (excluding e.g. Db2 Cloud REST API to import the data)

LOAD with a CLIENT keyword (work also for all Db2 LUW on-premise releases)
Insert from an EXTERNAL TABLE (available in Db2 Cloud, Warehouse and 11.5 release)

The latter is typically the fastest. See an example with an input like this:

db2 "create table import_test(c1 int, c2 varchar(10))"
echo "1,'test1'" > data.del
echo "2,'test2'" >> data.del

To insert the data from the client we need we can run:

db2 "INSERT INTO import_test SELECT * FROM EXTERNAL '/home/db2v111/data.del' USING (DELIMITER ',' REMOTESOURCE YES)"
DB20000I  The SQL command completed successfully.

db2 "select * from import_test"

C1          C2        
----------- ----------
          2 'test2'   
          1 'test1'   

  2 record(s) selected.

More examples including importing data from S3 can be found in Loading data to IBM Cloud chapter of the documentation.

IMPORT script on IBM DB2 Cloud using RUN SQL Interface

1 Answers