IT30017: DB2 MAY RUN OUT OF STATEMENT HEAP WHILE COMPILING QUERIES INVOLVING UNIONS AND PREDICATES WITH WEAK UDTS

Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Db2 may run out of statement heap and return a SQL0101N error
  code while compiling queries involving unions and predicates
  with weak User-defined Distinct Type (UDTs). The query must have
  the following properties:
  
  - The query contains a UNION or UNION ALL
  - The query contains a predicate outside of the union subselects
  - The predicate is a basic predicate and contains a weak
  user-defined type
  
  For example:
  
  create distinct type myweaktype as varchar(8) with weak type
  rules;
  create table t1 (c myweaktype);
  create table t2 (c myweaktype);
  
  select 1 from
  (
    select c from t1
    union
    select c from t2
  ) as myunion
  where
  exists (select 1 from t2 where myunion.c = t2.c)
  and
  exists (select 1 from t2 where myunion.c = t2.c);
  

Local fix

• Rewrite the query to push the predicates into the union
  subselects. For example, the query above can be rewritten to:
  
  select 1 from
  (
    select c from t1
    where
    exists( select 1 from t2 where t1.c = t2.c)
    and
    exists (select 1 from t2 where t1.c = t2.c)
    union
    select c from t1
    where
    exists( select 1 from t2 where t1.c = t2.c)
    and
    exists (select 1 from t2 where t1.c = t2.c)
  ) as myunion;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fix Pack 6                         *
  ****************************************************************
  

Problem conclusion

• The APAR was first fixed in Db2 11.1 Mod 4 Fix Pack 6
  

Temporary fix

Comments

APAR Information

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

  IT30013

• 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

• RB10 PSY

     UP