Rick Grant
Rick Grant

Reputation: 25

Can IBM data studio run DB2 Command File

I am upgrading from IBM DB2 9.5 to DB2 11.1. I use the DB2 9.5 Client software to run a series of commands to export data. See code snippet below. Is there a way to run a similar command using the IBM Data Studio 4.1.2 on a client workstation? I need to be able to automate the exports and not have to manually save select results.

Thanks in advance for any help.

CONNECT TO dbName USER "myuser" USING "myPW";
EXPORT TO "C:\out\outData.csv" OF DEL 
  MESSAGES  "C:\out\msg.log" 
  SELECT * FROM XX.tablename T
    WHERE T.flag IS NULL;
Commit;
CONNECT RESET;

Upvotes: 0

Views: 1848

Answers (1)

mao
mao

Reputation: 12287

The real question is why you want to use DataStudio for command automation, because that's not its strong point.

Is there some reason why your existing script(s) won't run unchanged on Db2 v11.1 ? You will most likely be upgrading your workstations/laptops) with a Db2 v11.1 client if you are wise. If you want complete automation including scheduling, then DataStudio is not the right choice in my opinion.

Apart from the above, the question "how do I run export in a script in data-studio " is answered below.

Data-Studio is written in java, so uses jdbc to submit SQL to the database.

When a script contains commands (such as export, import etc) then the script has to be changed because those are not SQL, so they have to be wrapped in a call to a stored procedure called ADMIN_CMD and all paths and files are relative to the server and not relative to the client as with your current script. You can read about sysproc.admin_cmd in the docs and see examples there.

In addition, you have to add code to manage the messages files if you choose the messages on server option. You may also need to add code to pull the resulting exported files back from the server to your workstation if you want to process those files locally on the Db2-client side.

So in short, if you want your exported files to continue to be on the workstation (or Db2 client side) then it is easier to maintain your current scripts and automate them.

Upvotes: 0

Related Questions