DB2 FOR LUW / DB2FORLUW / 970 - DB2

A problem may occur if your SQL procedure contains both: 
 
1) a condition handler for SQLWARNING (for example, DECLARE EXIT 
HANDLER FOR SQLWARNING), and: 
 
2) one of the following SQL statements.  Note that the statement 
can appear anywhere in the procedure, and need not lie within 
the scope of the condition handler: 
 
- A DELETE or UPDATE statement without a WHERE clause; for 
example, 
 
  DELETE FROM T; 
 
- A SELECT INTO statement where the number of items selected 
does not equal the number of host variables specified; for 
example, 
 
  SELECT * INTO I, J FROM T; 
 
where T has only one column, or three or more columns. 
 
If both of these requirements are met, the condition handler 
might get invoked at a point in the stored procedure where it 
ought not to be. 
 
The problem happens only when one of the database objects that 
the stored procedure depends on has been changed since the 
stored procedure was most recently called. 
 
To demonstrate this defect, you can first connect to a database 
then run the following sequence of DB2 commands.  It creates and 
calls twice a stored procedure named warn_test.  The first call 
to warn_test correctly sets the output parameter COMMENT to 
"Result: SUCCESS".  But if your DB2 software contains this 
defect then the second call to warn_test incorrectly sets the 
output parameter COMMENT to "Result: FAILURE". 
 
create table T(C1 integer) % 
 
create procedure warn_test(out comment varchar(128)) 
begin 
  declare message varchar(128) default 'Result: '; 
 
  -- Do-nothing warning handler that exits the routine. 
  declare exit handler for sqlwarning 
  begin 
  end; 
 
  set comment = message || 'FAILURE'; 
 
  -- DELETE without a WHERE clause generates a silent 
  -- compilation warning.  If DB2 performs an implicit rebind of 
  -- this routine, the warning will appear when the preceding 
  -- SET statement is executed. 
  delete from T; 
 
  set comment = message || 'SUCCESS'; 
end % 
 
-- Expect SUCCESS 
call warn_test(?) % 
 
-- Drop/recreate table to invalidate warn_test's package. 
drop table T % 
create table T(C1 integer) % 
 
-- Expect FAILURE 
call warn_test(?) %

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of DB2 for Linux, UNIX and Windows                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* .                                                            * 
****************************************************************

Modify DELETE or UPDATE statements to use WHERE clauses; ensure 
that SELECT INTO statements specify the correct number of source 
and target values.  For example, 
 
DELETE FROM T WHERE 1=1; 
 
SELECT C1 INTO I FROM T; 
 
Alternatively, you can define a CONTINUE handler to ignore the 
warnings associated with these particular situations.  Place 
the CONTINUE handler in the same scope as your original 
SQLWARNING handler.  Condition handlers that identify specific 
SQLSTATE values take precedence over general handlers for 
SQLWARNING, SQLEXCEPTION, or NOT FOUND.  For this defect, you 
should ignore SQLSTATE values '01503' and '01504'. 
 
declare exit handler for sqlwarning 
begin 
  -- ... 
end; 
 
-- Ignore potential warnings from implicit rebind: 
declare continue handler for sqlstate '01503', sqlstate '01504' 
begin 
end;

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 6. 
At a minimum, this fix should be applied on the database server.

Modify DELETE or UPDATE statements to use WHERE clauses; ensure 
that SELECT INTO statements specify the correct number of source 
and target values. 
 
For example: 
 
DELETE FROM T WHERE 1=1; 
 
SELECT C1 INTO I FROM T; 
 
Alternatively, you can define a CONTINUE handler to ignore the 
warnings associated with these particular situations. 
Place the CONTINUE handler in the same scope as your original 
SQLWARNING handler. 
Condition handlers that identify specific SQLSTATE values take 
precedence over general handlers for SQLWARNING, SQLEXCEPTION, 
or NOT FOUND. 
For this defect, you should ignore SQLSTATE values '01503' and 
'01504'. 
 
<code> 
declare exit handler for sqlwarning 
begin 
  -- ... 
end; 
 
-- Ignore potential warnings from implicit rebind: 
declare continue handler for sqlstate '01503', sqlstate '01504' 
begin 
end; 
</code>

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

25.11.2011
12.06.2012
12.06.2012

9.7.FP6