DB2 FOR LUW / DB2FORLUW / A10 - DB2

When you specify the FOR UPDATE clause in a SELECT INTO 
statement that assigns a value to a global variable, module 
variable, or PL/SQL package variable, DB2 reports error 
SQL0151N. 
 
The following script illustrates the error: 
 
set serveroutput on % 
 
create table staff(id integer, name varchar(32), dept smallint) 
% 
insert into staff values(10, 'Smith', 123) % 
 
CREATE OR REPLACE MODULE M1 % 
 
ALTER MODULE M1 ADD VARIABLE ch_name ANCHOR staff.name % 
 
ALTER MODULE M1 PUBLISH PROCEDURE example(in p_id anchor to 
staff.id) 
BEGIN 
  declare l_name anchor to staff.name; 
 
  SELECT name INTO ch_name 
  FROM   staff 
  WHERE id = p_id 
  FOR UPDATE WITH RS; 
 
  call dbms_output.put_line(ch_name); 
END % 
 
call m1.example(10) % 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: SQL0151N  The column "NAME" cannot be 
updated.  LINE NUMBER=3.  SQLSTATE=42808

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 Version 10.1 Fix Pack 4.                       * 
****************************************************************

Declare a local variable to receive the value from the SELECT 
INTO, and then assign the local variable to the global variable. 
For example: 
 
ALTER MODULE M1 PUBLISH PROCEDURE example(in p_id anchor to 
staff.id) 
BEGIN 
  declare l_name anchor to staff.name; 
 
  SELECT name INTO l_name 
  FROM   staff 
  WHERE id = p_id 
  FOR UPDATE WITH RS; 
 
  set ch_name = l_name; 
 
  call dbms_output.put_line(ch_name); 
END %

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

This problem is first fixed in DB2 Version 10.1 Fix Pack 4.

not known / see Local fix

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

06.03.2014
03.06.2014
03.06.2014