Can not create Stroed Procedure on Iseries DB@

Hi montroff,

The syntax is off a bit because the procedure isn't complete.  There's no 'begin' to match the 'end'.

Try the following.  And note that the procedure doesn't actually do anything with the results.  I'm assuming that this is a starting point and you're going to 'build up' to the desired procedure.

Good Luck,
Kent

CREATE PROCEDURE APAGING()
LANGUAGE SQL
NOT DETERMINISTIC
BEGIN
 Select * from m3djdtst.CMNDIV
 Fetch first 5 rows only;
END

Select allOpen in new window

I just was using this simple one as an example becuase I can not get any stored procedures to work.  

I tried the code you posted and got this error

ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: ( IF GET SET CALL CASE DROP GOTO.

Thanks,
Montrof

Also sorry I forgot the Begin but I had that in there when I originally tried it.

Ok.  That's because the SELECT fetches the data into 'mid air'.


Here's a short procedure that does work.  It should get you started.  :)

Kent

create procedure ap () 
language sql 
not deterministic 
begin 
  declare i integer; 
  select count(*) into i from sysibm.columns; 
end

Select allOpen in new window

I still get the following error

ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: ( IF GET SET CALL CASE DROP GOTO.

HELP

Odd...

It works for me.

How are you trying to create the stored procedure?  Some clients don't support the functionality.

I am using Toad for data analysis.  

montrof

Which version?  Does it have a "procedure editor" in it?

I am using Toad for Data Analysis 1.1.0.328 and does not seem to have a "procedure editor" I am writing it in the command line.

This may be tough.  :(

The Toad editor is quite simplistic in that it treats the semi-colon as the end-of-statement separator.  It doesn't actually parse the SQL, so it doesn't know that the semi-colons in the procedure are actually part of the procedure.

For creating the procedure, you might change the separator to a less interferring character, like '@', and run the create procedure statement.  You may want to change it back to ';' afterwards if that's what you're used to using.

And of course, the entire create procedure statement can be submitted via the command line interface, too.



Kent

Ok, so how would I change the end of statement separator in Toad? Also, if i do not change it how do I write the procedure?

The following works beautifully for me on iSeries v5r3:

HTH,
DaveSlash

create procedure ResultSet                                 
RESULT SETS 1
language sql
Begin
       DECLARE x CURSOR for select firstName from employee;
       Open x;
       SET RESULT SETS CURSOR x;
End;

Select allOpen in new window

Still getting
ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: ( IF GET SET CALL CASE DROP GOTO.

Unfortuantely I have no idea what verision our isereries is but it should not be this hard.

montrof

Hi Montroff,

I don't have Toad for DB2 installed anymore so I can't peruse the menus to find the location.  Sorry.


Kent

I don't believe Toad for DB2 currently supports DB2 for iSeries (although that may have changed).

Can you post your current code?

-- DaveSlash

I am actually using Toad for Data Analysis which is slightly different then Toad for DB2. Thanks for your help thus far.

Ahhh......

That didn't click when you mentioned it earlier.

I don't believe that you've got the right tool installed.

Can you post your current code?

-- DaveSlash

Here is the current code

CREATE PROCEDURE APAGING()
LANGUAGE SQL
 NOT DETERMINISTIC
 INHERIT SPECIAL REGISTERS
 BEGIN
 SELECT "FPLEDG"."EPDUDT", "CIDMAS"."IDSUNM", "CIDMAS"."IDPHNO", "FPLEDG"."EPIVDT", "FPLEDG"."EPCUAM", "FPLEDG"."EPSUNO", "FPLEDG"."EPSINO", "FPLEDG"."EPTRCD", "FPLEDG"."EPACDT", "CMNDIV"."CCDIVI", "CMNDIV"."CCCONO"
 FROM   "DCLTDE"."M3DJDEDU"."CIDMAS" "CIDMAS" INNER JOIN ("DCLTDE"."M3DJDEDU"."CMNDIV" "CMNDIV" INNER JOIN "DCLTDE"."M3DJDEDU"."FPLEDG" "FPLEDG" 
 ON ("CMNDIV"."CCCONO"="FPLEDG"."EPCONO") AND ("CMNDIV"."CCDIVI"="FPLEDG"."EPDIVI")) 
 ON ("CIDMAS"."IDCONO"="FPLEDG"."EPCONO") AND ("CIDMAS"."IDSUNO"="FPLEDG"."EPSUNO")
 WHERE  ("FPLEDG"."EPTRCD"=40 OR "FPLEDG"."EPTRCD"=50) AND "CMNDIV"."CCDIVI"<>'' AND "CMNDIV"."CCCONO"=999 
 ORDER BY "FPLEDG"."EPSUNO", "FPLEDG"."EPSINO" 
END@

Select allOpen in new window

It is the only tool that we have found other than winsql that allows us to Query, Create tables, Create views etc.

Hi montrof,

There are lots of tools out there that do this.

Aqua Data Studios has what I consider the "best" one.  www.aquafold.com

Advanced Query Tool is another that's pretty good.  www.aqt.com

Pay for tools like Toad for DB2 are great.  


Do you have Control Center installed?  It's a bit cumbersome, but does everything that you want.


Good Luck,
Kent

Toad for DB2 does not support the iseries.  So that is why we went with Toad for data analysis

It would appear that you're going to have to find another way to create stored procedures.

As I said, the command line interface works fine, but it's not very user friendly.


Kent

To begin, with Kent already told you that your select is going "into nothing".  If you want to return a result set, define a cursor, open it, and SET RESULT SETS to it. (see my example)

Also, use a semi-colon t end your statements (on both the SELECT and the END).

Follow my example above, and you should get to work easily.

HTH,
DaveSlash

Dave,
I copied your exact statement and it did not work.  I still get the following error

>[Error] Script lines: 1-8 --------------------------
 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>. 

Also I used Aqua Data Studio to run it to make sure it was not Toad.

Thanks,
Montrof

create procedure ResultSet                                 
RESULT SETS 1
language sql
Begin
       DECLARE x CURSOR for select CCCONO from M3DJDEDU.CMNDIV;
       Open x;
       SET RESULT SETS CURSOR x;
End;

Select allOpen in new window

Ok, that's certainly odd. (It works wonderfully for me.)

I have two suggestions of things to try.

First, remove the semi-colon on the "End" and try it again in your current tool. Possibly, the tool may add one automatically behind-the-scenes.

If that doesn't work, put the semi-colon back and try it using the iSeries' native tools ( like iSeries Navigator or just plain green-screen STRSQL ).  Surely, any company with an iSeries box must have a copy of those tools and a few people who know how to use them.

HTH,
DaveSlash