IBM Support

IC75200: DB2 CAN TRAP WHEN COMPILING A SPECIFIC TYPE OF SELECT STATEMENT

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • DB2 can trap when compiling a select statement if the optimized
    select statement satisfies all of the conditions below
    
    
    1. Either the select statement has a group-by-clause, or the
    select-clause contains the "distinct" keyword.
    
    2. The from-clause has a common table expression or a nested
    table expression.
    
    3. The where-clause of the table expression has an equity
    predicate such as column1 = <constant>.
    
    4. The where-clause of the select statement has a predicate such
    as column1 = column2, in which column1 comes from the table
    expression, and column2 is part of a unique key of other tables
    referenced in the from-clause. And column2 is not included in
    the select-clause, but column1 is included.
    
    
    
    
    An example of such a query is given below. Assume that
    T4(D1, D2, D4) is a unique key on table T4.
    
    
    SELECT DISTINCT T4.D1, T4.D3, T4.D4, T3.C3, T3.C4
    FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY T2.B2, T2.B3) as C1,
                T2.B1 as C2, T2.B2 as C3,T2.B3 as C4
         FROM
             (SELECT T1.A1 as B1, T1.A2 as B2, T1.A3 as B3
              FROM T1
              WHERE (201101 = T1.A2)
             ) AS T2
        ) AS T3,
        T4
    WHERE (T3.C1 <= 30) AND (1 <= T3.C1) AND (T4.D4 = T3.C2) AND
          (T3.C4 = T4.D1) AND (T3.C3 = T4.D2)
    
    You can find the optimized select statement in db2exfmt output.
    
    
    The corresponding call stack can look like this:
    
    in <sqlno_kc_construct_kc_key> <sqlno_key_card.C:3502>
    in <sqlno_kc_key_card> <sqlno_key_card.C:7310>
    in <sqlno_kc_all_key_card> <sqlno_key_card.C:991>
    in <sqlno_prop_mate> <sqlno_prop_misc.C:2324>
    in <MATE> <sqlno_crule_pop.C:398>
    in <sqlno_crule_simple_mate> <sqlno_crule.C:11373>
    in <sqlno_crule_mate_root> <sqlno_crule.C:11129>
    in <sqlno_crule_mate> <sqlno_crule_main.C:1823>
    

Local fix

  • Add an equity predicate to the select statement.
    
    Given the above example, you can add predicate T4.D2 = 201101 to
    the select statement, and the select statement becomes
    
    
    SELECT DISTINCT T4.D1, T4.D3, T4.D4, T3.C4, T3.C3
    FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY T2.B2, T2.B3) as C1,
                T2.B1 as C2, T2.B2 as C3,T2.B3 as C4
         FROM
             (SELECT T1.A1 as B1, T1.A2 as B2, T1.A3 as B3
              FROM T1
              WHERE (201101 = T1.A2)
             ) AS T2
        ) AS T3,
        T4
    WHERE (T3.C1 <= 30) AND (1 <= T3.C1) AND (T4.D4 = T3.C2) AND
          (T3.C4 = T4.D1) AND (T3.C3 = T4.D2) and (T4.D2 = 201101)
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error description field for more information.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Version 9.7 FixPack 5.                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 Version 9.7 FixPack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC75200

  • 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

    2011-03-23

  • Closed date

    2011-12-09

  • Last modified date

    2011-12-09

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

    IC74505

  • 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 #: IC75200

Modified date: 09 December 2011