IBM Support

LI73838: DB2 INSTANCE MAY GENERATE SQL0901 WHILE RUNNING A QUERY CONTAIN ING A TABLE/TEXT SCALAR FUNCTION CORRELATED TO TABLES IN UNION.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A query containing a table function that is directly or
    indirectly correlated to
    one or more tables used in the branches of a UNION (ALL) clause
    (U/A) may cause
    the DB2 instance to abend. Indirect correlation can occur if the
    table function
    is correlated to a table not within a U/A, but if that table is
    joined to the
    U/A.
    
    An example of such a query is:
    
    CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
    CREATE TABLE T2 LIKE T1;
    CREATE TABLE BT LIKE T1;
    
    CREATE FUNCTION TABFUNC(C1 INT)
    RETURNS TABLE (C1 INT,C2 INT,C3 INT)
    LANGUAGE SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN VALUES (1+C1,2,3);
    
    -- Directly correlated
    SELECT  *
    FROM    (SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS UA,
            TABLE (SELECT * FROM TABLE (TABFUNC(UA.C1)) AS TABFN) AS
    Q
    WHERE   UA.C1 = Q.C1;
    
    -- Indirectly correlated
    SELECT  *
    FROM    (SELECT * FROM T1 UNION ALL SELECT * FROM T2) AS UA,
            BT,
            TABLE (SELECT * FROM TABLE (TABFUNC(UA.C1)) AS TABFN) AS
    Q
    WHERE   UA.C1 = BT.C1
    AND     BT.C2 = Q.C1;
    
    This issue may also arise if a TEXT scalar function is used
    instead of a table
    function.
    
    This issue will be fixed starting with DB2 Version 9.1 Fix Pack
    7 and DB2
    Version 9.5 Fix Pack 4.
    
    LOCAL FIX:
    Manually rewrite the query with the table function in each
    branch of the U/A.
    For example, the above queries can be semantically equivalently
    rewritten as:
    
    -- Directly correlated rewrite
    SELECT  *
    FROM    (SELECT T1.*
             FROM T1,
                  TABLE (TABFUNC(T1.C1)) AS TABFN
             WHERE T1.C1 = TABFN.C1
             UNION ALL
             SELECT T2.*
             FROM T2,
                  TABLE (TABFUNC(T2.C1)) AS TABFN
             WHERE T2.C1 = TABFN.C1
            ) AS UA
    
    -- Indirectly correlated rewrite
    SELECT  *
    FROM    (SELECT T1.*
             FROM T1,
                  BT,
                  TABLE (TABFUNC(T1.C1)) AS TABFN
             WHERE T1.C1 = BT.C1
             AND   BT.C2 = TABFN.C1
             UNION ALL
             SELECT T2.*
             FROM T2,
                  BT,
                  TABLE (TABFUNC(T2.C1)) AS TABFN
             WHERE T2.C1 = BT.C1
             AND   BT.C2 = TABFN.C1
            ) AS UA
    

Local fix

  • No local FIX
    

Problem summary

  • DB2 INSTANCE MAY GENERATE SQL0901 WHILE RUNNING A QUERY CONTAIN
    ING A TABLE/TEXT SCALAR FUNCTION CORRELATED TO TABLES IN UNION.
    

Problem conclusion

  • First fixed in db2 v9.1 FP7
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73838

  • Reported component name

    DB2 UDB EXE LIN

  • Reported component ID

    5724E4904

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-10-21

  • Closed date

    2009-04-07

  • Last modified date

    2009-04-07

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

    LI73833

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

    LI73839

Fix information

  • Fixed component name

    DB2 UDB EXE LIN

  • Fixed component ID

    5724E4904

Applicable component levels

  • R910 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 October 2021