database - db2 "export to filename.csv"给出 IO 错误

Question

我正在尝试使用 JDBC 连接导出数据(我使用的是 DB2 数据库)，但失败并出现以下错误:

Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -3001, SQLSTATE: , SQLERRMC: sqlofopn -2029060079

我使用的查询:

call admin_cmd('EXPORT TO /home/user/test_1/db_extract.csv OF DEL MODIFIED BY NOCHARDEL SELECT * from mytable fetch first 5 rows only');

我还授予了 755 访问 test_1 文件夹的权限。我也尝试删除 admin_cmd 但出现 BEGIN OF STATEMENT 错误

并且还使用腻子尝试了相同的查询，但不幸的是我收到了这个错误:

SQL3001C An I/O error (reason = "sqlofopn -2029060079") occurred while opening the output file.

Answer 1

您需要向 db2 受防护用户和/或 db2 受防护用户所属的组授予权限。例如:

-- file /tmp/stack.sql
connect to pocdb user proksch using in4mix;

call admin_cmd('export to /stack/my.unl of del select * from proksch.foo');

connect reset;

terminate;

以下 stack.sh 脚本以 root 身份运行(或其他可以动态设置 acls 的用户)

#!/bin/bash
# ran as root to set acts
DB2=/home/db2inst1/sqllib/bin/db2

function rmperms {
        rm -f /stack/my.unl > /dev/null 2> /dev/null
        setfacl -x user:db2inst1 /stack
        setfacl -x user:db2fence /stack
        setfacl -x group:db2 /stack
}

function setperms {
        setfacl -m $1 /stack
}


function getperms {
        echo "   "
        echo "Perms on /stack"
        ls -l  / | grep stack
        getfacl /stack --tabular --absolute-names --recursive
        echo "   "
}
rmperms

getperms

su --command="${DB2} -tvf /tmp/stack.sql" db2inst1

rmperms
setperms user:db2inst1:rwx
getperms

su --command="${DB2} -tvf /tmp/stack.sql" db2inst1

rmperms
setperms user:db2fence:rwx
getperms
su --command="${DB2} -tvf /tmp/stack.sql" db2inst1

rmperms
setperms group:db2:rwx
getperms

su --command="${DB2} -tvf /tmp/stack.sql" db2inst1

产生以下结果:

Perms on /stack
drwxr-xr-x+   2 unload ops   4096 Mar 22 16:17 stack
# file: /stack
USER   unload    rwx
GROUP  ops       r-x
mask             r-x
other            r-x


connect to pocdb user proksch using

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = PROKSCH
 Local database alias   = POCDB


call admin_cmd('export to /stack/my.unl of del select * from proksch.foo')
SQL3001C  An I/O error (reason = "sqlofopn -2079391743") occurred while
opening the output file.

connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.


Perms on /stack
drwxrwxr-x+   2 unload ops   4096 Mar 22 16:17 stack
# file: /stack
USER   unload    rwx
user   db2inst1  rwx
GROUP  ops       r-x
mask             rwx
other            r-x


connect to pocdb user proksch using

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = PROKSCH
 Local database alias   = POCDB


call admin_cmd('export to /stack/my.unl of del select * from proksch.foo')
SQL3001C  An I/O error (reason = "sqlofopn -2079391743") occurred while
opening the output file.

connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.


Perms on /stack
drwxrwxr-x+   2 unload ops   4096 Mar 22 16:17 stack
# file: /stack
USER   unload    rwx
user   db2fence  rwx
GROUP  ops       r-x
mask             rwx
other            r-x


connect to pocdb user proksch using

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = PROKSCH
 Local database alias   = POCDB


call admin_cmd('export to /stack/my.unl of del select * from proksch.foo')

  Result set 1
  --------------

  ROWS_EXPORTED        MSG_RETRIEVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    MSG_REMOVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
  -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     5 -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

  1 record(s) selected.

  Return Status = 0

connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.


Perms on /stack
drwxrwxr-x+   2 unload ops   4096 Mar 22 16:17 stack
# file: /stack
USER   unload    rwx
GROUP  ops       r-x
group  db2       rwx
mask             rwx
other            r-x


connect to pocdb user proksch using

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.3
 SQL authorization ID   = PROKSCH
 Local database alias   = POCDB


call admin_cmd('export to /stack/my.unl of del select * from proksch.foo')


  Result set 1
  --------------

  ROWS_EXPORTED        MSG_RETRIEVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    MSG_REMOVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
  -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                     5 -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

  1 record(s) selected.

  Return Status = 0

connect reset
DB20000I  The SQL command completed successfully.

terminate
DB20000I  The TERMINATE command completed successfully.