IC86178: DB2 CAN HANG WHEN COMPILING A CERTAIN TYPE OF SQL STATEMENTS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When a SQL statement meets the following conditions, DB2 can
    hang, i.e. DB2 can be stuck in an infinite loop, when compiling
    such SQL statement.
    
    1) The SQL statement is eligible for star join planning, that
    is, DB2 can detect a star schema in the tables that involve in
    the SQL statement.
    
    2) The SQL statement contains more than 10 disconnected tables.
    Disconnected tables refer to tables that are not connected by
    join predicates, or the joins are effective Cartesian. For
    example, the following join is effective Cartesian:
    
    select *
    from t1, t2
    where t1.c1=t2.c1 and t1.c1=1
    
    Under transitivity, the join predicate can be replaced by a
    local predicate:
    
    select *
    from t1, t2
    where t2.c1=1 and t1.c1=1
    
    
    For the DB2 agent that is stuck in the compiling, You can
    observe the following stack tracebacks repeatedly occur. Note
    the common calling function sqlno_default_next_part():
    
    sqlno_decide_join_order
    sqlno_common_test_criteria
    sqlno_default_next_part
    
    or
    
    sqlno_collect_ajp
    sqlno_common_test_criteria
    sqlno_default_next_part
    

Local fix

  • Disable star join or disable dynamic programming join
    enumeration by using optimization level 0,1,2, or 3 to
    compile the query.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of DB2 V9.7                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to fix pack 8.                                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in V9.7 fp8
    

Temporary fix

  • Disable star join or disable dynamic programming join
    enumeration by using optimization level 0,1,2, or 3 to
    compile the query.
    

Comments

APAR Information

  • APAR number

    IC86178

  • 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

    2012-08-28

  • Closed date

    2013-04-01

  • Last modified date

    2013-05-21

  • 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

  • R970 PSY

       UP



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC86178

Modified date:

2013-05-21

Translate my page

Machine Translation

Content navigation