IBM Support

IT06721: DB2 MIGHT PRODUCE SQL0551N WHEN CREATING A COMPILED ROUTINE OR TRIGGER REFERENCING A DGTT OR VIEW

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • 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
    

Local fix

  • 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
    

Problem summary

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

Problem conclusion

  • Fixed in DB2 V9.7 Fix Pack 11
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT06721

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-01-23

  • Closed date

    2015-10-07

  • Last modified date

    2015-10-07

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT06721

Modified date: 07 October 2015