PI61265: DB2 MIGHT TRAP DURING EXECUTION OF A QUERY WHEN THE ROW WIDTH ON THE INNER SCAN OF OF MSJN IS GREATER THAN 32K

APAR status

• Closed as program error.

Error description

• DB2 might trap during execution of a query when the row width
  on the inner scan of of MSJN is greater than 32k
  
  This can be verified using the explain data as below:
  
  Access Plan:
            MSJOIN
            (   2)
             33.53
               4
          /---+----\
         2          0.04
      TBSCAN       FILTER
      (   3)       (   6)
      9.15484      16.371
         1            2
        |            |
         2            1
      SORT         TBSCAN
      (   4)       (   7)
      9.10336      16.371
         1            2
        |            |
         2            1
      TBSCAN       SORT
      (   5)       (   8)
      8.99536      16.3195
         1            2
        |            |
         2            1
  TABLE: MKANDIAH  TBSCAN
        T1         (   9)
        Q2         16.2115
                      2
                     |
                      1
               TABLE: MKANDIAH
                     T2  ===========>Table T2 has wide column
                     Q1
  The trap may show a stack similar to below:
  sqlri_hashRecUnBuild
  sqlri_mgjnCopyOutInner
  sqlri_mj
  sqlriSectInvoke
  
  
  You can see this issue being reproduced through the following
  steps:
  
  create bufferpool bp1_32k pagesize 32k;
  create system temporary tablespace ts1 pagesize 32k bufferpool
  bp1_32k;
  create table t1 (c1 int, c2 int) organize by row;
  create table t2 (c1 int, v1 varchar(32000), v2 varchar(2000),
  v3 varchar(32000)) organize by row;
  
  insert into t1 values (1,1),(1,2);
  insert into t2 values (
  1,
  substr(cast('ABCD' as varchar(32000)),1,32000),
  substr(cast('ABCD' as varchar(32000)),1,2000),
  substr(cast('ABCD' as varchar(32000)),1,32000));
  insert into t2 select * from t2;
  
  select * from t1,t2 where t1.c1=t2.c1
   /* <OPTGUIDELINES>
     <MSJOIN>
       <ACCESS TABLE="T1"/>
       <ACCESS TABLE="T2" />
     </MSJOIN>
  </OPTGUIDELINES> */;
  

Local fix

• N/A
  

Problem summary

• See error description
  

Problem conclusion

• The problem is fixed in Version 4.2 and later fix packs.
  

Temporary fix

Comments

APAR Information

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

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

Modules/Macros

• Unknown
  

Fix information

• Fixed component name

  INFO BIGINSIGHT

• Fixed component ID

  5725C0900

Applicable component levels

• R420 PSY

     UP