使用RUN SQL接口在IBM DB2 Cloud上的IMPORT脚本 [英] IMPORT script on IBM DB2 Cloud using RUN SQL Interface

问题描述

我正在尝试使用IBM DB2云中的RUN SQL脚本将硬盘驱动器中的JOBFILE.CSV导入表JOB中.

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/O错误(原因="sqlofopn -2029060079")输入文件.SQLCODE = -3030，SQLSTATE =，DRIVER = 4.25.1301

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

似乎我设置的路径不起作用.根据我的研究，必须先将JOBFILE.CSV加载到DB2服务器中，然后才能运行IMPORT脚本.

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.

推荐答案

在本地客户端上有文件时，有两个基本"选项(例如，用于导入数据的Db2 Cloud REST API除外)

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)

1. 使用 CLIENT 关键字的 LOAD (也适用于所有Db2 LUW内部部署版本)
2. 从 外部表 (在Db2 Cloud，Warehouse和11.5版本中可用)

1. LOAD with a CLIENT keyword (work also for all Db2 LUW on-premise releases)
2. 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.

更多示例，包括从S3导入数据，可以在

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

这篇关于使用RUN SQL接口在IBM DB2 Cloud上的IMPORT脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！