By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

CLP Stored Procedure Call differences

P: n/a
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

"myprocedure" 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

Jun 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<ha*********@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.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

"myprocedure" 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..
Jun 8 '06 #2

P: n/a
ha*********@gmail.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

"myprocedure" 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/
Jun 8 '06 #3

P: n/a
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*********@gmail.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

"myprocedure" 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/


Jun 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.