OraFAQ Forum: Other Operating Systems » How to run a set of SQL statements in AS400

Home » Infrastructure » Other Operating Systems » How to run a set of SQL statements in AS400 (IBM iseries in WIN XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to run a set of SQL statements in AS400 [message #289353]

Thu, 20 December 2007 21:48

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

Report message to a moderator

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

Littlefoot
Messages: 21823
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).

Report message to a moderator

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 06:24:10 CST 2025