• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2241
  • Last Modified:

Can not create Stroed Procedure on Iseries DB@

I am trying to create a stored procedure in Toad for data analysis on a ibm iseries DB2 platform.  I am getting the following error
ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword END not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

It is a very simple stored procedure

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


I have also tried adding a @ after the end and still no luck.

Thanks,
Montrof
0
montrof
Asked:
montrof
  • 12
  • 10
  • 5
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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

Open in new window

0
 
montrofAuthor Commented:
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
0
 
montrofAuthor Commented:
Also sorry I forgot the Begin but I had that in there when I originally tried it.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Kent OlsenData Warehouse Architect / DBACommented:

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

Open in new window

0
 
montrofAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Odd...

It works for me.

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

0
 
montrofAuthor Commented:
I am using Toad for data analysis.  

montrof
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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


0
 
montrofAuthor Commented:
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
montrofAuthor Commented:
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?
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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;

Open in new window

0
 
montrofAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Montroff,

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


Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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

Can you post your current code?

-- DaveSlash
0
 
montrofAuthor Commented:
I am actually using Toad for Data Analysis which is slightly different then Toad for DB2. Thanks for your help thus far.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Ahhh......

That didn't click when you mentioned it earlier.

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

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Can you post your current code?

-- DaveSlash
0
 
montrofAuthor Commented:
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@

Open in new window

0
 
montrofAuthor Commented:
It is the only tool that we have found other than winsql that allows us to Query, Create tables, Create views etc.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
montrofAuthor Commented:
Toad for DB2 does not support the iseries.  So that is why we went with Toad for data analysis
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
 
montrofAuthor Commented:
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;

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Montrof,

Aqua Data Studios doesn't have a Procedure Editor, either.  But perhaps you can fool it into passing the entire create statement.


Open:   File / Options / General

You'll see a line similar to this:

   ';' Statement separator      true

Set it to false and try to run the CREATE PROCEDURE statement.


Kent
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now