DB2 FOR LUW / DB2FORLUW / 970 - 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 a SQL procedure.         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Various errors, such as SQL0151N and SQL0206N, may occur     * 
* when                                                         * 
* creating a SQL procedure that contains a TRUNCATE statement. * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to V9.7 FixPack 6 or later.                   * 
****************************************************************

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

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
 

This problem is first Fixed in DB2 V9.7 FixPack 6.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC84358 
follow-up : 

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

19.10.2011
11.06.2012
11.06.2012

9.7.FP6