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 | |
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 |