db2 procedure in solaris

[Gladiator]

Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. can any onehelp me
on this.

Procedure 1 with a parameter:

CREATE PROCEDURE DMSODS.test_proc (IN n INT)
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Procedure 2 with no parameter:

CREATE PROCEDURE DMSODS.test_proc1 ()
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Call from Linux:
db2 "call test_proc(1)"
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601

db2 "call test_proc1()"

Return Status = 0
Thanks,
Kamalnath.V

[Serge Rielau]

Gladiator wrote:

Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. can any onehelp me
on this.

Procedure 1 with a parameter:

CREATE PROCEDURE DMSODS.test_proc (IN n INT)
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Procedure 2 with no parameter:

CREATE PROCEDURE DMSODS.test_proc1 ()
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Call from Linux:
db2 "call test_proc(1)"
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601

db2 "call test_proc1()"

Return Status = 0

Smells like a linux shell problem.
What happens if you type:
db2
call test_proc(1)
quit

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gladiator]

On Aug 12, 3:43*pm, Serge Rielau <[e-mail]:

Gladiator wrote:

Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. *can any onehelp me
on this.

Procedure 1 with a parameter:

CREATE PROCEDURE DMSODS.test_proc (IN n INT)
* * LANGUAGE SQL
P1: * BEGIN
* * * DECLARE SQLSTATE CHAR(5);
END P1

Procedure 2 with no parameter:

CREATE PROCEDURE DMSODS.test_proc1 ()
* * LANGUAGE SQL
P1: * BEGIN
* * * DECLARE SQLSTATE CHAR(5);
END P1

Call from Linux:

db2 "call test_proc(1)"
SQL0104N *An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: *"<space>". *SQLSTATE=42601

*db2 "call test_proc1()"

* Return Status = 0

Smells like a linux shell problem.
What happens if you type:
db2
call test_proc(1)
quit

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Does it make some difference if we execute a procedure from a 32-bit
instance client which is accesing the procedure/database on a 64-bit
instance ?

thanks,
kamal.

[Serge Rielau]

Gladiator wrote:

On Aug 12, 3:43 pm, Serge Rielau <[e-mail]:

>Gladiator wrote:

>>Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. can any onehelp me
on this.
Procedure 1 with a parameter:
CREATE PROCEDURE DMSODS.test_proc (IN n INT)
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1
Procedure 2 with no parameter:
CREATE PROCEDURE DMSODS.test_proc1 ()
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1
Call from Linux:
db2 "call test_proc(1)"
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601
db2 "call test_proc1()"
Return Status = 0

Smells like a linux shell problem.
What happens if you type:
db2
call test_proc(1)
quit

Does it make some difference if we execute a procedure from a 32-bit
instance client which is accesing the procedure/database on a 64-bit
instance ?

No, I'm 99.9% certain that this is not a DB2 problem.
It seems like the linux shell you are using is doing something to the
text in the double quotes.
Please try what i proposed it will rule out any issues along the 32/64
bit boundary, OS differences and server issues.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Gladiator]

On Aug 12, 5:41*pm, Serge Rielau <[e-mail]:

Gladiator wrote:

On Aug 12, 3:43 pm, Serge Rielau <[e-mail]:

Gladiator wrote:
Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. *can any onehelp me
on this.
Procedure 1 with a parameter:
CREATE PROCEDURE DMSODS.test_proc (IN n INT)
* * LANGUAGE SQL
P1: * BEGIN
* * * DECLARE SQLSTATE CHAR(5);
END P1
Procedure 2 with no parameter:
CREATE PROCEDURE DMSODS.test_proc1 ()
* * LANGUAGE SQL
P1: * BEGIN
* * * DECLARE SQLSTATE CHAR(5);
END P1
Call from Linux:
db2 "call test_proc(1)"
SQL0104N *An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: *"<space>". *SQLSTATE=42601
*db2 "call test_proc1()"
* Return Status = 0
Smells like a linux shell problem.
What happens if you type:
db2
call test_proc(1)
quit

Does it make some difference if we execute a procedure from a 32-bit
instance client which is accesing the procedure/database on a 64-bit
instance ?

No, I'm 99.9% certain that this is not a DB2 problem.
It seems like the linux shell you are using is doing something to the
text in the double quotes.
Please try what i proposed it will rule out any issues along the 32/64
bit boundary, OS differences and server issues.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Hi serge ,

it gives me the same result

db2 =call test_proc(1)
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601
Thanks ,
Kamal.

[Gladiator]

On Aug 12, 7:16*pm, Gladiator <[e-mail]:

On Aug 12, 5:41*pm, Serge Rielau <[e-mail]:

Gladiator wrote:

On Aug 12, 3:43 pm, Serge Rielau <[e-mail]:
>Gladiator wrote:
>>Here are two simple stored procedures which were there in a database
>>on a SOLARIS machine, one with an input parameter and the other with
>>no parameter. From a Linux DB2 client command prompt, the call to
>>stored procedure with no parameter works fine where as the call to the
>>procedure with the input parameter does not work. *can any onehelp me
>>on this.
>>Procedure 1 with a parameter:
>>CREATE PROCEDURE DMSODS.test_proc (IN n INT)
>>* * LANGUAGE SQL
>>P1: * BEGIN
>>* * * DECLARE SQLSTATE CHAR(5);
>>END P1
>>Procedure 2 with no parameter:
>>CREATE PROCEDURE DMSODS.test_proc1 ()
>>* * LANGUAGE SQL
>>P1: * BEGIN
>>* * * DECLARE SQLSTATE CHAR(5);
>>END P1
>>Call from Linux:
>>db2 "call test_proc(1)"
>>SQL0104N *An unexpected token ")" was found following "BEGIN-OF-
>>STATEMENT".
>>Expected tokens may include: *"<space>". *SQLSTATE=42601
>>*db2 "call test_proc1()"
>>* Return Status = 0
>Smells like a linux shell problem.
>What happens if you type:
>db2
>call test_proc(1)
>quit
Does it make some difference if we execute a procedure from a 32-bit
instance client which is accesing the procedure/database on a 64-bit
instance ?

No, I'm 99.9% certain that this is not a DB2 problem.
It seems like the linux shell you are using is doing something to the
text in the double quotes.
Please try what i proposed it will rule out any issues along the 32/64
bit boundary, OS differences and server issues.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Hide quoted text -

- Show quoted text -

Hi serge ,

it gives me the same result

db2 =call test_proc(1)
SQL0104N *An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: *"<space>". *SQLSTATE=42601

Thanks ,
Kamal.- Hide quoted text -

- Show quoted text -

one more thing i found was. i tried the same command on 2 different
linux clients which has V9.5 fix pack 0 . It was failing on both the
machines. It seems to be executing fine on any other client version of
DB2 .

One more query i have is... If i have a client installed on a machine
how do i find which client it is ( Runtime client, admin Client or
Application development client).

Thanks ,
Kamal.

[Serge Rielau]

Gladiator wrote:

>db2 =call test_proc(1)
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601

OK.. now that is getting my attention. I admit to being stumped...
Just for paranoias sake try these:
db2 =call test_proc(a)
SQL0206N "A" is not valid in the context where it is used. SQLSTATE=42703
db2 =values 1

1
-----------
1

1 record(s) selected.

What do you get?
(I'm trying to figure out whether something with your terminator is broken)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Jan M. Nelken]

Gladiator wrote:

Here are two simple stored procedures which were there in a database
on a SOLARIS machine, one with an input parameter and the other with
no parameter. From a Linux DB2 client command prompt, the call to
stored procedure with no parameter works fine where as the call to the
procedure with the input parameter does not work. can any onehelp me
on this.

Procedure 1 with a parameter:

CREATE PROCEDURE DMSODS.test_proc (IN n INT)
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Procedure 2 with no parameter:

CREATE PROCEDURE DMSODS.test_proc1 ()
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1

Call from Linux:
db2 "call test_proc(1)"
SQL0104N An unexpected token ")" was found following "BEGIN-OF-
STATEMENT".
Expected tokens may include: "<space>". SQLSTATE=42601

db2 "call test_proc1()"

Return Status = 0
Thanks,
Kamalnath.V

Quick test on Linux (Red Hat 5.1) shows that:

[db2iv952@riscjmn ~]$ db2 -td@ -vf test.db2
CREATE PROCEDURE DMSODS.test_proc (IN n INT)
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5);
END P1
DB20000I The SQL command completed successfully.

[db2iv952@riscjmn ~]$ db2 call "dmsods.test_proc(1)"

Return Status = 0

so it is working.
My environment is:

[db2iv952@riscjmn ~]$ bash --version
GNU bash, version 3.1.17(1)-release (x86_64-redhat-linux-gnu)
Copyright (C) 2005 Free Software Foundation, Inc.
[db2iv952@riscjmn ~]$ uname -a
Linux riscjmn.torolab.ibm.com 2.6.18-53.el5xen #1 SMP Wed Oct 10
16:48:44 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
This is where my DB2 is newer than yours:

[db2iv952@riscjmn ~]$ db2level
DB21085I Instance "db2iv952" uses "64" bits and DB2 code release
"SQL09052" with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s080623", "MI00239", and Fix
Pack "2".
Product is installed at "/opt/ibm/db2/V9.5".
I will check tomorrow whether DB2 V9.5 GA has the problem you are
describing.

Jan M. Nelken