Home » Infrastructure » Other Operating Systems » How to run a set of SQL statements in AS400 (IBM iseries in WIN XP)
How to run a set of SQL statements in AS400 [message #289353] Thu, 20 December 2007 21:48 Go to next message
sivakumar.oracle
Messages: 9
Registered: August 2007
Location: Bangalore
Junior Member
HI Folks,

i was stuck at my work with this issue :

Created one member i.e, INS_INTF in CDC/SRC_CDC,where this member contains a set of SQL insert statements nearly about 116.I wanted to execute/run this in AS400.

can any body try to find out a solution for me.

My CL program :

CREATE PROCEDURE CDC/INS_TEST11()
LANGUAGE SQL
P1: BEGIN
INSERT INTO cdc/abc (select * from cdc/xyz);
INSERT INTO cdc/cde (select * from cdc/qrs);
"
"
:
116 inserts
END P1


Regards,
siva
Re: How to run a set of SQL statements in AS400 [message #289368 is a reply to message #289353] Fri, 21 December 2007 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 18821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I can not help you about AS400, but here's the principle which *must* work anywhere: create a simple SQL script (it is an ordinary text file; let's call it insert.sql) which should contain all those INSERT INTO statements (and nothing else - just 116 inserts UNLESS you want to exit to operating system prompt after it finishes. In that case, add the EXIT; statement at the end).

It is to be run like this:
sqlplus -s username/password@database @insert.sql
Now, put this line into a file that can be executed on your operating system. On MS Windows, it would be a .BAT file. On OpenVMS, it would be a .COM file. I believe you know how it is to be named on AS400.

That should be it ... there's no need to create a procedure (at least, that's what I concluded viewing your question).
Re: How to run a set of SQL statements in AS400 [message #307384 is a reply to message #289353] Tue, 18 March 2008 13:15 Go to previous message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
You can try this:

Change your INS_INTF to something simpler (only the insert statements):

INSERT INTO cdc/abc (select * from cdc/xyz);
INSERT INTO cdc/cde (select * from cdc/qrs);
...

And finally run it with this:

RUNSQLSTM SRCFILE(CDC/SRC_CDC) SRCMBR(INS_INTF) COMMIT(*NONE)

(The COMMIT(*NONE) may or may not be required)

I hope this helps.


Edit: My bad, I'm assuming you are using the integrated DB2. If that's not the case, this shouldn't work.

Re-Edit: Wow, didn't notice how long this question has been around. Sorry!

[Updated on: Tue, 18 March 2008 13:22]

Report message to a moderator

Previous Topic: Handling a result set from a db2 z/os Stored procedure
Next Topic: Installing Oracle client ver 10g on Z/OS mainframe
Goto Forum:
  


Current Time: Wed Apr 16 13:08:37 CDT 2014

Total time taken to generate the page: 0.06039 seconds