IBM Support

JR30276: SQL0901N IS RETURNED WHEN AN SQL STATEMENT MODIFIES A TABLE AND CONTAINS MULTIPLE REFERENCES TO A VIEW OVER THE SAME TABLE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • SQL0901N is returned when an SQL statement modifies a table and
    contains multiple references to a view over the same table and
    the view references an SQL function.
    
    SQL0901N is returned when the following criteria are met:
    
    1. A view V1 references both a table T1 and an SQL function
       with BEGIN ATOMIC body
    
    2. Table T1 is modified and V1 is referenced:
       - in the body of a recursive trigger,
    OR
       - at least 3 times in a dynamic compound statement
         (BEGIN ATOMIC), SQL table function or a trigger that has
         been invoked
    
    You may then recieve an error like:
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed. (Reason "Bad Plan;
    Unresolved QNC found".)  SQLSTATE=58004
    

Local fix

  • 1. For simple function, use simple RETURN and avoid BEGIN ATOMIC
       block in the function body if possible.
    2. Rewrite the function using non-SQL language.
    3. Avoid using recursive trigger if possible.
    4. If no recursive trigger is involved, try to manually split
       the view (for example, create another view with the same view
       definition) so that each view is referenced no more than two
       times.
    

Problem summary

  • Users Affected: ALL
    
    Problem Description:
    
    SQL0901N is returned when an SQL statement modifies a table and
    contains multiple references to a view over the same table and
    the view references an SQL function.
    
    SQL0901N is returned when the following criteria are met:
    
    1. A view V1 references both a table T1 and an SQL function
       with BEGIN ATOMIC body
    
    2. Table T1 is modified and V1 is referenced:
       - in the body of a recursive trigger,
    OR
       - at least 3 times in a dynamic compound statement
         (BEGIN ATOMIC), SQL table function or a trigger that has
         been invoked
    
    You may then recieve an error like:
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed. (Reason "Bad Plan;
    Unresolved QNC found".)  SQLSTATE=58004
    
    Problem Summary:  See Problem Description.
    

Problem conclusion

  • First fixed in DB2 UDB Version 9.5, FixPak 3 (build s081118)
    

Temporary fix

  • 1. For simple function, use simple RETURN and avoid BEGIN ATOMIC
       block in the function body if possible.
    2. Rewrite the function using non-SQL language.
    3. Avoid using recursive trigger if possible.
    4. If no recursive trigger is involved, try to manually split
       the view (for example, create another view with the same view
       definition) so that each view is referenced no more than two
       times.
    

Comments

APAR Information

  • APAR number

    JR30276

  • Reported component name

    DB2 UDB WSE WIN

  • Reported component ID

    5765F3501

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-08-21

  • Closed date

    2009-03-10

  • Last modified date

    2009-03-10

  • 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 UDB WSE WIN

  • Fixed component ID

    5765F3501

Applicable component levels

  • R810 PSN

       UP

  • R820 PSN

       UP

  • R910 PSN

       UP

  • R950 PSN

       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":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
10 March 2009