DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under rare conditions, DB2 might erroneously return a SQL0551N 
error message: 
 
For this to occur all of the following conditions will need to 
be present 
 
1) The command is being executed during the creation of 
function, procedure or trigger. 
 
2) The body of this object contains a Common Table Expression 
(CTE) referencing a Declared Global Temporary Table (DGTT) or a 
View 
 
3) The command was executed by a user with non-dbadm privileges 
or who does not have the select privilege on the tables that 
make up the view. 
 
Example: 
DECLARE GLOBAL TEMPORARY TABLE SESSION.DGTT1 
( 
    c1 int, 
    c2 int 
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN userspace; 
 
CREATE or replace PROCEDURE proc1() 
LANGUAGE SQL 
MODIFIES SQL DATA 
DYNAMIC RESULT SETS 1 
BEGIN 
    -- Local Variables Declaration 
 
    DECLARE CUR CURSOR WITH RETURN TO CALLER 
    FOR 
        WITH CTE AS 
        ( 
            SELECT 
                c1, 
                c2 
              FROM SESSION.DGTT1 
              where c1 > 10 
    ) 
    SELECT * 
        FROM CTE 
    WITH UR; 
 
 ...... 
 
END

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 V9.7 users                                           * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to  DB2 V9.7 Fix Pack 11 or higher.                  * 
****************************************************************

To Workaround, either: 
- Execute the command as a user with dbadm authority or grant 
select to the user on the underlying tables in the view. 
- Modify the procedure text to inline the Common Table 
Expression (CTE). 
 
Below is an example of the temp table and proc1 modified to 
inline the CTE. 
 
DECLARE GLOBAL TEMPORARY TABLE SESSION.DGTT1 
( 
    c1 int, 
    c2 int 
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED IN userspace; 
 
CREATE or replace PROCEDURE proc1() 
LANGUAGE SQL 
MODIFIES SQL DATA 
DYNAMIC RESULT SETS 1 
BEGIN 
    -- Local Variables Declaration 
 
    DECLARE CUR CURSOR WITH RETURN TO CALLER 
    FOR SELECT 
            c1, 
            c2 
            FROM SESSION.DGTT1 
            where c1 > 10 
 
 ...... 
 
END

Fixed in DB2 V9.7 Fix Pack 11

not known / see Local fix

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

23.01.2015
07.10.2015
07.10.2015

9.7.FP11