I'm using Data Studio to connect to a DB2 server. When I'm trying to use the 'import utility' in the Data Studio, it succeeds with a warning and the result show that no record has been inserted into the database. The Import wizard is generating the following SQL command
CALL SYSPROC.ADMIN_CMD( 'IMPORT FROM "/home/xyz/backup/TRANSACTION" OF DEL MODIFIED BY coldel| delprioritychar INSERT INTO S.TRANSACTION' );
If I copy this command and paste it in a sql script in DB2 and then run it it give another error
An I/O error (reason = "sqlofopn -2029060079") occurred while opening the input file.. SQLCODE=-3030, SQLSTATE=
If I use the db2 shell to execute the IMPORT part of the command (without CALL SYSPROC.ADMIN_CMD) it succeed without any issue. What is wrong here?
When you (or DataStudio) runs SYSPROC.ADMIN_CMD (which is the default method used by DataStudio for import), the action happens on the Db2-server using the account of the Db2-instance-owner (for Db2-LUW).
That account (for example db2inst1) requires read access to the specified filename. In your case, the Db2-instance owner did not have access to the file (and/or the path containing the file), so the exception got thrown.
You may see additional detail in the Db2-server diagnostic file (db2diag.log) for the failed action, depending on the diagnostics level that is active on the Db2-server.