Help - Creating stored procedures using DB2 under AS400

[Ian Jones]

Hi

I'm in a bit of a pickle here and hope some kind sole will be able to help
me out. My boss has just asked me to create a stored procedure on our DB2
database which runs on our AS400 machine.

Nothing I try seems to work even when trying to create the simplest
procedure. Can anyone help? I could really use an example here. Perhaphs
someone has knows of some good reference material.

Can anyone point me in the right direction?

Ian

[Tim]

What type of stored procedure are you attempting to create? There are two
types:
1. An external stored procedure. This type simply declares the parameters
returned by a program created by a traditional compiler (RPG, COBOL, C,
PL/I, etc...)
2. An SQL language stored procedure. This type requires that you have SQL
Development Tools and the ILE C compiler on the system.

Ian Jones <Jo...@tesco.net> wrote in message
news:7sq81v$pmm$1...@epos.tesco.net...

[Karl Hanson]

Ian Jones wrote:
>
> I'm in a bit of a pickle here and hope some kind sole will be able to help
> me out. My boss has just asked me to create a stored procedure on our DB2
> database which runs on our AS400 machine.
>
> Nothing I try seems to work even when trying to create the simplest
> procedure. Can anyone help? I could really use an example here. Perhaphs
> someone has knows of some good reference material.
>
> Can anyone point me in the right direction?

Chapter 7. Stored Procedures in the SQL Programming manual may help:

http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafymst02.htm

--

Karl Hanson

[George Hughen]

Look in Chapter 7: Stored Procedures.

http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafymst0
2.htm

Ian Jones wrote in message <7sq81v$pmm$1...@epos.tesco.net>...
>Hi

>
>I'm in a bit of a pickle here and hope some kind sole will be able to help
>me out. My boss has just asked me to create a stored procedure on our DB2
>database which runs on our AS400 machine.
>
>Nothing I try seems to work even when trying to create the simplest
>procedure. Can anyone help? I could really use an example here. Perhaphs
>someone has knows of some good reference material.
>
>Can anyone point me in the right direction?
>

>Ian
>
>

[Stan Poliskey]

IBM Manuals suck, here is a sample of a simple stored procedure. if you
are on V4R4 your really in a pickle because IBM screwed up stored procedures
royally, but if your below that release, this should work.

1) create a source file on the as400, my example file the souce would be
EBUSINESS/QSQLSRC(CHGPWD)

then put this in it.

0002.00 CREATE PROCEDURE EBUSINESS/CHANGEPWD
0003.00 (IN S_USERID CHARACTER(20),
0004.00 IN I_CUSTNUM DECIMAL(6),
0005.00 IN S_PWD CHARACTER(20),
0006.00 OUT STRESULT INTEGER)
0007.00 LANGUAGE SQL
0008.00 BEGIN
0009.00
0010.00 DECLARE EXIT HANDLER FOR SQLEXCEPTION
0011.00 SET STRESULT = 1; -- ST_FAIL
0012.00 DECLARE EXIT HANDLER FOR SQLWARNING
0013.00 SET STRESULT = 1; -- ST_FAIL
0014.00
0015.00 SET STRESULT = 5;
0016.00
0017.00 UPDATE TPSDS001O/SECURITY
0018.00 SET PASSWORD = S_PWD
0019.00 WHERE USERID = S_USERID AND CNUMBR = I_CUSTNUM;
0020.00
0021.00 END;

2) compile into a stored procedure with this AS400 command.
RUNSQLSTM SRCFILE(EBUSINESS/QSQLSRC) SRCMBR(TST2) COMMIT(*NONE)

That command will take the source file and then using ILE C create a stored
procedure. now if you don't have ILE C installed on your as400, this won't
work.

good luck!