Possible DB2 bug in the stored procedure parameters handling.

[Konstantin Andreev]

Hello, everybody.

I've noticed very strange DB2 behaviour in a stored procedure
parameters handling, and it looks like a bug for me. Consider stored
procedure defined as:

Create procedure test_proc(
IN p_x char(1),
OUT p_y char(1)
)
language sql result sets 0 modifies sql data BEGIN ...

Nothing special occurs if you make such a call from another stored
procedure:

call test_proc( var_x, var_y );

But if you pass the same variable for both parameters, like:

call test_proc( the_var, the_var );

then parameter "p_x" gets NULL value upon the entry "test_proc",
regardless of the real "the_var" value.

Why so ? Is this a bug or a feature ? Is this documented somewhere ?
I've detected this on "DB2 8.1.6 Enterprise Edition for Windows IA32".
--
Konstantin Andreev.

[Serge Rielau]

Konstantin Andreev wrote:

Hello, everybody.

I've noticed very strange DB2 behaviour in a stored procedure
parameters handling, and it looks like a bug for me. Consider stored
procedure defined as:

Create procedure test_proc(
IN p_x char(1),
OUT p_y char(1)
)
language sql result sets 0 modifies sql data BEGIN ...

Nothing special occurs if you make such a call from another stored
procedure:

call test_proc( var_x, var_y );

But if you pass the same variable for both parameters, like:

call test_proc( the_var, the_var );

then parameter "p_x" gets NULL value upon the entry "test_proc",
regardless of the real "the_var" value.

Why so ? Is this a bug or a feature ? Is this documented somewhere ?
I've detected this on "DB2 8.1.6 Enterprise Edition for Windows IA32".
--
Konstantin Andreev.

I tried to repro like this on DB2 Viper, but was unsuccessful:

db2 => CREATE PROCEDURE P(IN a INT, OUT b INT) SET b = a;
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE D(INOUT c INT) CALL P(c, c);
DB20000I The SQL command completed successfully.
db2 => CALL D(5);

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : 5

Return Status = 0

Can you provide a script. Clearly what you observe sounds wrong.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Konstantin Andreev]

Serge Rielau пи�ал(а):

I tried to repro like this on DB2 Viper, but was unsuccessful:

db2 => CREATE PROCEDURE P(IN a INT, OUT b INT) SET b = a;
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE D(INOUT c INT) CALL P(c, c);
DB20000I The SQL command completed successfully.
db2 => CALL D(5);

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : 5

Return Status = 0
I tried this, it works in my system too.
Can you provide a script. Clearly what you observe sounds wrong.

Below is the script. When called, the procedure saves it's parameters
in temporary table, and we can analyse them later. Here the call log on
my system:

X Y
------
a - the parameters of the "test_proc( x, y )" call
- - the parameters of the "test_proc( x, x )" call
------------------------------------------------------------------------------
DECLARE GLOBAL TEMPORARY TABLE #t(
x char(1), y char(1)
)
ON COMMIT PRESERVE ROWS NOT LOGGED
@
Create procedure test_proc(
IN p_x char(1),
OUT p_y char(1)
) Begin
insert into session.#t values ( p_x, p_y );
end
@
Create procedure test_wrap(
) Begin
declare x, y char(1);

values( 'a', 'b' ) into x, y;

call test_proc( x, y ); -- OK
call test_proc( x, x ); -- ERROR, ERROR !!!
end
@
call test_wrap()
@
select * from session.#t
@
drop procedure test_proc @
drop procedure test_wrap @
drop table session.#t @
------------------------------------------------------------------------------

[Konstantin Andreev]

Serge Rielau wrote:

db2 => CREATE PROCEDURE P(IN a INT, OUT b INT) SET b = a;
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE D(INOUT c INT) CALL P(c, c);
DB20000I The SQL command completed successfully.
db2 => CALL D(5);

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : 5

Return Status = 0

This approach will also fail ( successfull repro ) if you change INT to
CHAR(x):

db2 => CREATE PROCEDURE P(IN a char(10), OUT b char(10)) SET b = a
DB20000I The SQL command completed successfully.
db2 => cREATE PROCEDURE D(INOUT c char(10)) CALL P(c, c)
DB20000I The SQL command completed successfully.
db2 => call d('a')

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : -

Return Status = 0
--
Konstantin Andreev.

[Serge Rielau]

Konstantin Andreev wrote:

Serge Rielau wrote:

db2 => CREATE PROCEDURE P(IN a INT, OUT b INT) SET b = a;
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE D(INOUT c INT) CALL P(c, c);
DB20000I The SQL command completed successfully.
db2 => CALL D(5);

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : 5

Return Status = 0

This approach will also fail ( successfull repro ) if you change INT to
CHAR(x):

db2 => CREATE PROCEDURE P(IN a char(10), OUT b char(10)) SET b = a
DB20000I The SQL command completed successfully.
db2 => cREATE PROCEDURE D(INOUT c char(10)) CALL P(c, c)
DB20000I The SQL command completed successfully.
db2 => call d('a')

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : -

Return Status = 0
--
Konstantin Andreev.

Works on DB2 Viper. I'll inquire whether there is a known fix.
If you need a fix please open a PMR.

db2 => CREATE PROCEDURE P(IN a CHAR(10), OUT b char(10)) SET b = a;
DB20000I The SQL command completed successfully.
db2 => CREATE PROCEDURE D(INOUT c char(10)) CALL P(c, c);
DB20000I The SQL command completed successfully.
db2 => CALL D('a');

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : a

Return Status = 0

db2 =>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Konstantin Andreev]

Serge Rielau пи�ал(а):

This approach will also fail ( successfull repro ) if you change INT toCHAR(x):

db2 => CREATE PROCEDURE P(IN a char(10), OUT b char(10)) SET b = a
DB20000I The SQL command completed successfully.
db2 => cREATE PROCEDURE D(INOUT c char(10)) CALL P(c, c)
DB20000I The SQL command completed successfully.
db2 => call d('a')

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : -

Return Status = 0

Works on DB2 Viper. I'll inquire whether there is a known fix. If you need a fix please open a PMR.

db2 => CREATE PROCEDURE P(IN a CHAR(10), OUT b char(10)) SET b = a;
db2 => CREATE PROCEDURE D(INOUT c char(10)) CALL P(c, c);
db2 => CALL D('a');

Value of output parameters
--------------------------
Parameter Name : C
Parameter Value : a

Return Status = 0

Thank you a lot, Serge. Since this bug is fixed on Viper, then
everything is all right. For now I can use a workaround.
--
Konstantin Andreev.

[Mark A]

"Konstantin Andreev" <dr*******@datatech.ru> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

Thank you a lot, Serge. Since this bug is fixed on Viper, then
everything is all right. For now I can use a workaround.
--
Konstantin Andreev.

Maybe it is fixed before Viper. Do you have the latest fixpack (FP11)?

[Michel Esber]

FP 12 has been released.

[Gert van der Kooij]

In article <11**********************@y43g2000cwc.googlegroups .com>,
mi****@us.automatos.com says...

FP 12 has been released.

But be carefull, they made a mess of it. When downloading the Release
Notes you get the DB2 FP11 release notes. I hope the images are
correct!!