IBM Support

IC78348: DB2 might abend while creating MQT whose definition is recursive and has common table expression as well as IN predicate

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • MQT creation might trap with infinite recursive calls of
    sqlnq_reroutable_ast_rec in the trap file. This happens only
    when MQT definition has the following characteristics:
    1. MQT definition has CSE (Common Sub Expression), called V1
    2. MQT has recursive UNION ALL construct, called V2. V2 has two
    union all branches. The first union branch references V1 and the
    second branch references V1 and V2 of course.
    3. V2 second branch has IN predicate and the element list
    consists of multiple values or expressions.
    
    Example:
    -- create a base table
    create table t1(c1 int, c2 int);
    -- mqt creation traps
    create table mqt as (
    with v1(c1, c2) as (select * from t1),
          v2(level, c1, c2) as (select 1, v1.* from v1 union all
                                      select parent.level+1,
    parent.c1, parent.c2 from v2 parent, v1 child
                                      where parent.c1 = child.c1 and
    child.c2 in (1,2) and parent.level < 10)
    select c1, c2 from v2
    ) data initially deferred refresh deferred;
    

Local fix

  • Make V1 a regular view and change MQT definition to reference V1
    instead of common table expression.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 6.                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 9.7 Fix Pack 6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC78348

  • 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-08-26

  • Closed date

    2012-06-06

  • Last modified date

    2012-06-06

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

    IC74565

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

Modified date: 06 June 2012