DB2 FOR LUW / DB2FORLUW / A50 - DB2

SELECT INTO statement does not set value to a global variable as 
below steps. 
 
Problem Reproducible Steps: 
1. $ repro_bad.sh 
   ==> call r01.rowcount( 1 ) returns "COUNT: 0" as below while 
there is one matching row exists at table r01. 
--------------- 
call r01.rowcount( 1 ) 
 
  Return Status = 0 
 
COUNT :0 
--------------- 
 
----- repro_bad.sh ----- 
# 
db2stop 
db2start 
db2 -v create db testdb 
db2 -v connect to testdb 
db2 -v "create table r01(c1 int not null)" 
db2 -v "insert into r01 values(1),(2),(3),(4)" 
db2 -vtd/ -f rowcount_bad.sql 
------------------------ 
 
----- rowcount_bad.sql ----- 
-- test 
SET SERVEROUTPUT ON/ 
 
CREATE OR REPLACE MODULE r01/ 
ALTER MODULE r01 ADD VARIABLE c_c1 INT/ 
ALTER MODULE r01 PUBLISH PROCEDURE rowcount( IN dum INT )/ 
 
ALTER MODULE r01 ADD PROCEDURE rowcount( IN dum INT ) 
MODIFIES SQL DATA 
LANGUAGE SQL 
BEGIN 
--      DECLARE c_c1              INT; 
        DECLARE SQLERRM       VARCHAR(256); 
        DECLARE v_rowcount    INT DEFAULT 0; 
        DECLARE EXIT HANDLER FOR NOT FOUND 
    BEGIN 
        GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT; 
            call DBMS_OUTPUT.PUT_LINE('NOT FOUND !'||SQLERRM); 
    END; 
 
    SELECT C1 INTO c_c1 FROM r01 
         WHERE C1=2 with rs use and keep update locks; 
 
    GET DIAGNOSTICS v_rowcount = ROW_COUNT; 
        call DBMS_OUTPUT.PUT_LINE('COUNT :'||v_rowcount); 
END 
/ 
 
call r01.rowcount( 1 ) 
/ 
-----------------------------

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 10.5                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.5 FixPack 4.                           * 
****************************************************************

Instead of global variables, use local variables.

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 10.5 FixPack 4.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

25.03.2014
08.09.2014
08.09.2014