DB2 FOR LUW / DB2FORLUW / A10 - DB2

Various errors, such as SQL0151N and SQL0206N, may occur when 
creating a SQL procedure that contains a TRUNCATE statement. 
The following steps illustrate such a scenario: 
. 
1. Create a table and its alias. 
$ db2 "create table S1.T1 (C1 int)" 
$ db2 "create alias S2.T1 for table S1.T1" 
 
2. Create a SQL procedure with TRUNCATE statement. 
p1.sql 
------------------------------ 
CREATE OR REPLACE PROCEDURE P1() 
BEGIN 
TRUNCATE TABLE S2.T1 IMMEDIATE; 
INSERT INTO S2.T1 VALUES 1; 
END@ 
------------------------------ 
$ db2 -td@ -vf p1.sql 
SQL0151N  The column "C1" cannot be updated.  LINE NUMBER=4. 
SQLSTATE=42808 
 
p2.sql 
------------------------------ 
CREATE OR REPLACE PROCEDURE P2() 
BEGIN 
TRUNCATE TABLE S2.T1 IMMEDIATE; 
INSERT INTO S1.T1 VALUES 1; 
END@ 
------------------------------ 
$ db2 -td@ -vf p2.sql 
SQL0206N  "C1" is not valid in the context where it is used. 
LINE NUMBER=4. 
SQLSTATE=42703

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who use TRUNCATE statement in SQL procedures.          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to V10.1 FixPack 1 or later.                  * 
****************************************************************

Replace the alias in the TRUNCATE statement with the base table 
OR 
Substitute the TRUNCATE statement with the DELETE statement

DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

This problem was first fixed in DB2 V10.1 FixPack 1.

not known / see Local fix

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

13.06.2012
05.11.2012
05.11.2012