home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC99901 Status: Closed

SELECT INTO GLOBAL VARIABLE WITH 'FOR UPDATE' SPECIFIED RETURNS
SQL0151N ERROR

product:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problem description:
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
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 Version 10.1 Fix Pack 4.                       * 
****************************************************************
Local Fix:
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 %
available fix packs:
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

Solution
This problem is first fixed in DB2 Version 10.1 Fix Pack 4.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
06.03.2014
03.06.2014
03.06.2014
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
10.1.0.4 FixList