db2 "export to filename.csv" giving IO error

1
votes

I am trying to export data using JDBC connection (I am using DB2 database) but it is failing and giving following error:

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

Query I used:

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');

I Gave 755 access to test_1 folder as well. I tried removing the admin_cmd as well but getting BEGIN OF STATEMENT error

And also tried the same query using putty, but no luck I am getting this error :

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

2
databasecsvdb2export
1. You realize this will write the file to a directory on the database server, right? - Ian Bjorhovde
2. The directory must be writable by the instance's fenced user. 755 means that only the owner of the directory will have write permission. - Ian Bjorhovde
I tried giving 777 also to the folder, still same issue. But when i remove admin_cmd, it works when i run the query directly from the instance but it is not working from JDBC connection. - varunchopra18
Can you run the command from the DB2 CLP, both the native EXPORT command and EXPORT via calling the admin_cmd procedure? Also - Is this a home grown application calling admin_cmd via JDBC or something like Data Studio? - proksch_ibm
@proksch_ibm I tried running with admin_cmd, it gives error but without admin_cmd it works. It is just an Simple Java Application. - varunchopra18

2 Answers

1
votes

You need to either grant permission to the db2 fenced user and/or the group that the db2 fenced user is a member of. For example:

-- 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;

The following stack.sh script was run as root (or another user than can set acls dynamically)

#!/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

Yields the following results:

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.
0
votes

In windows, the problem was in path access. Running db2 with admin privileges solved the problem (the problem can be also solved by change paths to files).