IBM Support

IC69455: SQL0950N WHEN THE SQL QUERY BEING ISSUED CONTAINS REFERENCE TO A SQL BODIED PROCEDURE, FUNCTION OR METHOD.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • You may experience a
    
    SQL0950N A DROP TABLE or DROP INDEX statement cannot be issued
    when an open cursor is currently using the table or index
    
    when the SQL query being issued contains reference to a SQL
    bodied procedure, function or method using input parameters that
    are columns from tables referenced in the main query. The SQL
    bodied procedure, function or method must contain a complex
    common table expression or WITH clause that is referenced in
    multiple portions of the function body and includes a reference
    to the input parameter. For example
    
    CREATE FUNCTION FUNC1(IN_KEY INTEGER)
    RETURNS TABLE(OUT_KEY INTEGER, OUT_NAME VARCHAR(100))
    LANGUAGE SQL
    READS SQL DATA
    RETURN
    WITH A(KEY, NAME) AS (SELECT COL1, MAX(NAME) FROM T WHERE T.COL2
    = IN_KEY GROUP BY COL1),
         B(MNAME) AS (SELECT MAX(NAME) FROM A)
    SELECT KEY, NAME FROM A, B WHERE NAME=MNAME;
    
    
    The common table expression 'A' is referenced in multiple places
    with the function body and it also contains a reference to the
    input parameter.
    
    If the execution plan for this query contains two or more MSJOIN
    operations each reference the common table expression 'A' as the
    left side input to the MSJOIN, then the query execution has the
    potential to experience the error.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Prior to DB2 V97 FP3.                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * SQL0950N WHEN THE SQL QUERY BEING ISSUED CONTAINS REFERENCE  *
    * TO A SQL BODIED PROCEDURE, FUNCTION OR METHOD.               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade DB2 to V97 FP3.                                      *
    ****************************************************************
    

Problem conclusion

  • Problem is first fixed in DB2 V97 FP3.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC69455

  • 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

    2010-06-24

  • Closed date

    2010-09-24

  • Last modified date

    2010-09-24

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

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

    IC69743

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
24 September 2010