我需要将DB2表中的数据导出为文本格式,然后将文件从IBM COS(又名S3)文件存储导入云上的DB。
数据明细:
- UTF-8编码格式
- 数据类型:时间戳、字符串(新行内=〉多行)、数字、浮点数
- 无CLOB或BLOB
我应该使用什么“导出”和“导入”命令?
我试过了
导出
db2 EXPORT TO table1.del OF DEL modified by codepage=1208 SELECT * FROM table1
- table1.del* 的第一行:
"blah",6,"1",766,"Y","blah","2022-01-24-08.53.09.000000","blah","3,766",1
汇入
INSERT INTO table1 FROM EXTERNAL 'table1.del' USING
(CCSID 1208 s3('s3.xxxxxx.cloud',
's3-access-key-id',
's3-secret-access-key',
'bucket-name')
)
它找到了文件,但在文件处理过程中出错(似乎导入尝试获取整个第一行并插入到第一列中。指定 fillRecord 选项没有帮助):
bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]
-----------------------------------------------------------------------------
1: 1(182)[1, VARCHAR(256)] missing field - fillRecord not enabled, ""blah",6,"1",766,"Y","blah","2022-01-24-08.53.09.000000","blah","3,766",1"[1]
载入
CALL SYSPROC.ADMIN_CMD('IMPORT from "S3::s3.xxxxxx.cloud::s3-access-key-id::s3-secret-access-key::bucket-name::table1.del" OF DEL modified by codepage=1208
INSERT into TABLE1 ')
FAILED [Code: -2062, SQL State: ] An error occurred while accessing media "DB2REMOTE". Reason code: "0x870f01bb".. SQLCODE=-2062, SQLSTATE= , DRIVER=4.25.1301
其他问题:
1.我可以从S3存储器加载IXF文件吗?或者只支持TXT文件格式?
1.我还尝试按此处所述对DB2存储进行编目https://blog.4loeser.net/2017/08/combining-db2-and-cloud-object-storage.html,但LIST STORAGE ACCESS没有提供任何信息,
CALL SYSPROC.ADMIN_CMD('LOAD from db2remote://bucket-name-alias//table1.ixf OF IXF INSERT INTO table1');
退货:
FAILED [Code: -1652, SQL State: ] File I/O error occurred.. SQLCODE=-1652, SQLSTATE= , DRIVER=4.25.1301
1条答案
按热度按时间7uhlpewt1#
最后我总算
详细信息:
1.导入DEL文件只能有EOL - LF,不能有LF-CR;
1.列的分隔符-选项卡;
1.在IMPORT COMMAND中添加了一些选项:
)