varunchopra18
Reputation: 21
db2 "export to filename.csv" giving IO error
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.
Upvotes: 2
Views: 12107
Answers (2)
Krystian
Reputation: 2290
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).
Upvotes: 0
proksch_ibm
Reputation: 278
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.
Upvotes: 1
Related Questions
- Do a "git export" (like "svn export")?
- DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=12;AT YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE MINUTE
- Failed to run the DB2 command using jdbc
- DB2 SQL Error: SQLCODE=-302 while executing prepared statement
- Persiste image file as byte[] on db2
- DB2 Mainframe: queryTimeout does not work on SqlTransactionRollbackException: SQLCODE=-911, SQLSTATE=40001
- db2 count(*) order by with hibernate criteria
- db2 cast problem