CLP Stored Procedure Call differences

[harborboy76]

I am calling the exact same stored procedure called myprocedure from 2
different boxes from the CLP, but I'm experiencing different behaviors
between them. After I was unable to get
any support from IBM due to V7.1 being no longer supported, I figure
someone here might
be able to help.

Is DB2 V7 more forgiving in the way I can call my stored procedure ? If
it's defined with CHARACTER as incoming parameter, am I not required to
put any quotes around it ?
Or does it have something to do with my environment (Dynix vs Linux) ?

CREATE PROCEDURE myprocedure (
IN
v_in_proc_id CHAR(6),
OUT
RETURN_VAL INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
..
..
END
BOX1
---------
SEQUENT dynix/ptx 4.5.3 (supposedly equivalent to NUMA Q)
DB2 Client V7.1

--->db2 "call myprocedure (xxx,?)"
RETURN_VAL: 1

"myprocedur e" RETURN_STATUS: "1"

BOX2
---------
Linux SUSE 9
DB2 Client V8.2

----->db2 "call myprocedure (xxx,?)"
SQL0408N A value is not compatible with the data type of its
assignment
target. Target name is "V_IN_PROC_ ID". SQLSTATE=42821
---------
If I put a single quote around the variable, then it will run in both
the way
it's supposed to. I need to find out why calling a procedure without
quotes was allowed in BOX1. Has anyone run into this in the old days ;)
?

Thanks!!

Nick

[Mark A]

<ha*********@gm ail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

I am calling the exact same stored procedure called myprocedure from 2
different boxes from the CLP, but I'm experiencing different behaviors
between them. After I was unable to get
any support from IBM due to V7.1 being no longer supported, I figure
someone here might
be able to help.

Is DB2 V7 more forgiving in the way I can call my stored procedure ? If
it's defined with CHARACTER as incoming parameter, am I not required to
put any quotes around it ?
Or does it have something to do with my environment (Dynix vs Linux) ?

CREATE PROCEDURE myprocedure (
IN
v_in_proc_id CHAR(6),
OUT
RETURN_VAL INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
.
.
END
BOX1
---------
SEQUENT dynix/ptx 4.5.3 (supposedly equivalent to NUMA Q)
DB2 Client V7.1

--->db2 "call myprocedure (xxx,?)"
RETURN_VAL: 1

"myprocedur e" RETURN_STATUS: "1"

BOX2
---------
Linux SUSE 9
DB2 Client V8.2

----->db2 "call myprocedure (xxx,?)"
SQL0408N A value is not compatible with the data type of its
assignment
target. Target name is "V_IN_PROC_ ID". SQLSTATE=42821
---------
If I put a single quote around the variable, then it will run in both
the way
it's supposed to. I need to find out why calling a procedure without
quotes was allowed in BOX1. Has anyone run into this in the old days ;)
?

Thanks!!

Nick

Using a DB2 version 8 client with a DB2 version 7 server is not supported.
Even though some things may work OK, there are some things that do not work.

However, it might help if you could update your DB2 Version 7 server with
the latest version 7 fixpack, so that you have a version of DB2 server that
was released after the Version 8 client was released..

[Serge Rielau]

ha*********@gma il.com wrote:

I am calling the exact same stored procedure called myprocedure from 2
different boxes from the CLP, but I'm experiencing different behaviors
between them. After I was unable to get
any support from IBM due to V7.1 being no longer supported, I figure
someone here might
be able to help.

Is DB2 V7 more forgiving in the way I can call my stored procedure ? If
it's defined with CHARACTER as incoming parameter, am I not required to
put any quotes around it ?
Or does it have something to do with my environment (Dynix vs Linux) ?

CREATE PROCEDURE myprocedure (
IN
v_in_proc_id CHAR(6),
OUT
RETURN_VAL INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
.
.
END
BOX1
---------
SEQUENT dynix/ptx 4.5.3 (supposedly equivalent to NUMA Q)
DB2 Client V7.1

--->db2 "call myprocedure (xxx,?)"
RETURN_VAL: 1

"myprocedur e" RETURN_STATUS: "1"

BOX2
---------
Linux SUSE 9
DB2 Client V8.2

----->db2 "call myprocedure (xxx,?)"
SQL0408N A value is not compatible with the data type of its
assignment
target. Target name is "V_IN_PROC_ ID". SQLSTATE=42821
---------
If I put a single quote around the variable, then it will run in both
the way
it's supposed to. I need to find out why calling a procedure without
quotes was allowed in BOX1. Has anyone run into this in the old days ;)
?

In V7 CALL was an API call outside of SQL and CLP hid it from the user.
Unfortunately the quotes for strings were not enforced. That was a bug.
In V8 CALL is a regular SQL statement and hence the SQL rules for
literals must be observed.
In return CALL can now be dynamically prepared, allows expressions and
even subqueries in the parameter list:
SET txt = 'CALL P((SELECT COUNT(*) FROM T), ?, ? * 10)';
PREPARE STMT FROM TXT;
EXECUTE STMT USING x, y;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[harborboy76]

HI Serge,

Thank you very much for the information. Is there any
document that I can refer to regarding this V7 bug or any links
that I can refer to ? I would love to do some more reading on this.
I tried looking up in the V7 documentation without success.

Thank you!
Nick

Serge Rielau wrote:

ha*********@gma il.com wrote:

I am calling the exact same stored procedure called myprocedure from 2
different boxes from the CLP, but I'm experiencing different behaviors
between them. After I was unable to get
any support from IBM due to V7.1 being no longer supported, I figure
someone here might
be able to help.

Is DB2 V7 more forgiving in the way I can call my stored procedure ? If
it's defined with CHARACTER as incoming parameter, am I not required to
put any quotes around it ?
Or does it have something to do with my environment (Dynix vs Linux) ?

CREATE PROCEDURE myprocedure (
IN
v_in_proc_id CHAR(6),
OUT
RETURN_VAL INTEGER
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
.
.
END
BOX1
---------
SEQUENT dynix/ptx 4.5.3 (supposedly equivalent to NUMA Q)
DB2 Client V7.1

--->db2 "call myprocedure (xxx,?)"
RETURN_VAL: 1

"myprocedur e" RETURN_STATUS: "1"

BOX2
---------
Linux SUSE 9
DB2 Client V8.2

----->db2 "call myprocedure (xxx,?)"
SQL0408N A value is not compatible with the data type of its
assignment
target. Target name is "V_IN_PROC_ ID". SQLSTATE=42821
---------
If I put a single quote around the variable, then it will run in both
the way
it's supposed to. I need to find out why calling a procedure without
quotes was allowed in BOX1. Has anyone run into this in the old days ;)
?

In V7 CALL was an API call outside of SQL and CLP hid it from the user.
Unfortunately the quotes for strings were not enforced. That was a bug.
In V8 CALL is a regular SQL statement and hence the SQL rules for
literals must be observed.
In return CALL can now be dynamically prepared, allows expressions and
even subqueries in the parameter list:
SET txt = 'CALL P((SELECT COUNT(*) FROM T), ?, ? * 10)';
PREPARE STMT FROM TXT;
EXECUTE STMT USING x, y;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/