PI45907: INVALID PROCMS AND PROCSU VALUES FOR QUERY WITH NONCORRELATED FULLSELECT

APAR status

• Closed as fixed if next.

Error description

• The processor cost estimates PROCMS and PROCSU in explain table
  DSN_STATEMNT_TABLE fail to include the accumulated costs of
  noncorrelated fullselects.
  For example:
  SELECT
    (SELECT COUNT(COLA) FROM TBL1 WHERE COLB = ? AND COLC = ?) AS
  CNT1,
    <SELECT COUNT(COLA) FROM TBL1 WHERE COLB <> ? AND COLD = ?) AS
  CNT2
  FROM SYSIBM.SYSDUMMY1;
  The values of PROCMS and PROCSU will reflect the cost of
  selecting a single row from SYSIBM.SYSDUMMY1 and will exclude
  the activity on TBL1.
  However if the query is rewritten using UNION statements:
  SELECT COUNT(COLA) FROM TBL1 WHERE COLB = ? AND COLC = ?
  UNION
  SELECT COUNT(COLA) FROM TBL1 WHERE COLB <> ? AND COLD = ?
  The values PROCSU and PROCMS now include the combined costs of
  the 2 queries against TBL1.
  
  
  
  Keywords:
  SQLACCESSPATH
  SQLPERFORMANCE
  SQLNONCORRSUBQ
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the  *
  *                 PROCMS and PROCSU fields of the              *
  *                 DSN_STATEMNT_TABLE will be affected.         *
  ****************************************************************
  * PROBLEM DESCRIPTION: Invalid PROCMS and PROCSU values in     *
  *                      Explain table DSN_STATEMNT_TABLE        *
  *                      for a query with a noncorrelated        *
  *                      subquery SELECT.                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In DSN_STATEMNT_TABLE, it shows invalid PROCMS and PROCSU values
  for a query with a noncorrelated subquery SELECT. This problem
  occurs when the subquery SELECT is used as the fields.
  
  Here is an example:
  
    Select (
            (Select Count(C1) from T1) As Col1
           ,(Select Count(C2) from T1) As Col2
           ) From SYSIBM.SYSDUMMY1;
  
  Additional Keywords:
  SQLEXPLAIN SQLNONCORRSUBQ SQLSELECT
  

Problem conclusion

Temporary fix

Comments

• This APAR is being closed as FIN, and the problem will be fixed
  in the next release of DB2.                           (pi45907)
  

APAR Information

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

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

Fix information

Applicable component levels

• RA10 PSN

     UP

• RB10 PSN

     UP