Import data fails in DB2

Question

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?

mao · Accepted Answer · 2018-05-06 16:14:36Z

1

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.

answered May 6, 2018 at 16:14

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HHH Over a year ago

Where the diagnostic file is stored?

mao Over a year ago

As you are learning, try to use the Knowledge Center more extensively, because you may get answers more quickly, see ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/…

mustaccio · Accepted Answer · 2018-05-06 15:55:35Z

0

ADMIN_CMD expects the input file to be on the server, because it (as any other stored procedure) runs on the server; it has no access to your local file system.

Commands you run in the Db2 command line processor execute on the client and therefore can access the file locally.

answered May 6, 2018 at 15:55

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Collectives™ on Stack Overflow

Import data fails in DB2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related