PH31796: A QUERY USING DEGREE='ANY' MAY HAVE IDENTICAL ROWS RETURNED

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A Query using DEGREE='ANY' may have identical rows returned.
  
  1) A query runs on parallelism and the query referencing
     a table in a tablespace is defined as PAGENUM RELATIVE.
  
  2) It is not easy to identify the impacted queries directly.
  
     A query is provided, to help identify the possible
     impacted queries.
  
     Customer needs to collect the EXPLAIN output for all
     the possible queries first, then join them with the
     catalog tables.  After executes the following query,
  
     a). If the PART_INNER = 'N' and the first entry of the same
         value of GROUPID in the same QUERYNO, QBLOCKNO group has
         the value PAGENUM = 'R', then this query is a possible
         impacted query.
  
     b). If the PART_INNER = 'Y' and any of the entries has the
         value PAGENUM = 'R' in the same GROUPID with the same
         QUERYNO, QBLOCKNO group, then this query is a possible
         impacted query.
  
  SELECT EP.*, S.PAGENUM
  FROM SYSIBM.SYSTABLES T,
  SYSIBM.SYSTABLESPACE S,
  (SELECT A.QUERYNO, A.QBLOCKNO, B.GROUPID,
  A.PLANNO,
  SUBSTR(A.CREATOR, 1, 20) AS TB_CREATOR,
  SUBSTR(A.TNAME, 1, 20) AS TBNM,
  B.PART_INNER
  FROM PLAN_TABLE A, DSN_PGROUP_TABLE B
  WHERE A.QUERYNO = B.QUERYNO
  AND A.QBLOCKNO = B.QBLOCKNO
  AND ( A.ACCESS_PGROUP_ID = B.GROUPID
  OR A.JOIN_PGROUP_ID = B.GROUPID)
  AND B.RANGEKIND = 'P'
  AND B.PARTKIND = 'L'
  ORDER BY A.QUERYNO, A.QBLOCKNO, B.GROUPID, A.PLANNO
  ) EP (QUERYNO, QBLOCKNO, GROUPID, PLANNO, TB_CREATOR,
  TBNM, PART_INNER)
  WHERE EP.TB_CREATOR = T.CREATOR
  AND EP.TBNM = T.NAME
  AND T.DBNAME = S.DBNAME
  AND T.TSNAME = S.NAME
  ORDER BY EP.QUERYNO, EP.QBLOCKNO, EP.GROUPID, EP.PLANNO
  ;
  

Local fix

• Disable parallelism.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of                                 *
  * paraellelism using a query against a                         *
  * partition-by-range (PBR) table space with                    *
  * relative page numbering.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query using parallelism may return                         *
  * more rows than expected as some rows                         *
  * are identical.                                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When query parallelism is run against a PBR table space using
  relative page numbering, each child task gets a set of
  page ranges to process. Db2 incorrectly goes beyond end of
  a range and continues to look for a qualified row until the
  end of the table space is reached. This causes incorrect output
  in the result set showing identical rows.
  

Problem conclusion

• Db2 table space scan path has been fixed to correctly check
  for end of a processing range when relative page numbering is
  used in a PBR table space.
  Additional Keywords:
  DB2PARALL/K
  PARALLELISM
  SQLPARALLELISM
  DB2INCORR/K
  INCORROUT
  SQLINCORR
  SQLINCORROUT
  

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:

  UI73397

Modules/Macros

• DSNIRNXT
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI73397

     UP21/01/16 P F101 ¢

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.