Hi All,

I'm trying to create a simple SQL procedure, But DB2 is giving an
error..can anyone pls help in this..My proc is something like this and
stored proc1.db2 file.

CREATE PROCEDURE DB2INST1.Proc1()
LANGUAGE SQL
P1: BEGIN
declare a integer;
declare b integer;
declare c integer;
set c=a+b;
END P1

when i try to create this using db2 -vf proc1.db2..it says..
CREATE PROCEDURE DB2INST1.Proc1()

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1760N The CREATE statement for stored procedure "PROC1" must have
a valid
LANGUAGE clause, EXTERNAL clause and PARAMETER STYLE clause. LINE
NUMBER=1.

LANGUAGE SQL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"LANGUAGE
SQL". Expected tokens may include: "JOIN <joined_table>".
SQLSTATE=42601

P1: BEGIN
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "P1" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "<call>". SQLSTATE=42601

declare a integer;
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "declare" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin_atomic>".
SQLSTATE=42601

declare b integer;
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "declare" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin_atomic>".
SQLSTATE=42601

declare c integer;
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "integer;" was found following "declare
c ".
Expected tokens may include: "<space>". SQLSTATE=42601

set c=a+b;
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "set c=" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

END P1
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END P1".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

Praveen <spraveen2001@yahoo.com> wrote:

> Hi All,
>
> I'm trying to create a simple SQL procedure, But DB2 is giving an
> error..can anyone pls help in this..My proc is something like this and
> stored proc1.db2 file.
>
> CREATE PROCEDURE DB2INST1.Proc1()
> LANGUAGE SQL
> P1: BEGIN
> declare a integer;
> declare b integer;
> declare c integer;
> set c=a+b;
> END P1
>
> when i try to create this using db2 -vf proc1.db2..it says..
> CREATE PROCEDURE DB2INST1.Proc1()
[...]

What do you use as statement terminator? The default is the end-of-line,
which would mean that you have 8 different statements above. You might
want to use some other symbol like an '@':

db2 -td@

db2 =>
db2 => CREATE PROCEDURE DB2INST1.Proc1()
db2 => LANGUAGE SQL
db2 => P1: BEGIN
db2 => declare a integer;
db2 => declare b integer;
db2 => declare c integer;
db2 => set c=a+b;
db2 => END P1
db2 => @


--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Knut Stolze wrote:

> Praveen <spraveen2001@yahoo.com> wrote:
>
>
>>Hi All,
>>
>>I'm trying to create a simple SQL procedure, But DB2 is giving an
>>error..can anyone pls help in this..My proc is something like this and
>>stored proc1.db2 file.
>>
>>CREATE PROCEDURE DB2INST1.Proc1()
>>LANGUAGE SQL
>>P1: BEGIN
>>declare a integer;
>>declare b integer;
>>declare c integer;
>>set c=a+b;
>>END P1
>>
>>when i try to create this using db2 -vf proc1.db2..it says..
>>CREATE PROCEDURE DB2INST1.Proc1()
>
> [...]
>
> What do you use as statement terminator? The default is the end-of-line,
> which would mean that you have 8 different statements above. You might
> want to use some other symbol like an '@':
>
> db2 -td@
>
> db2 =>
> db2 => CREATE PROCEDURE DB2INST1.Proc1()
> db2 => LANGUAGE SQL
> db2 => P1: BEGIN
> db2 => declare a integer;
> db2 => declare b integer;
> db2 => declare c integer;
> db2 => set c=a+b;
> db2 => END P1
> db2 => @
>
>

.... and Praveen, how do you propose to return the result to caller?

Jan M. Nelken