PM60826: INCORROUT WHEN USING LIKE :HOST-VAR / '?' PREDICATE AND THE HOST-VAR VALUE INCLUDES A PATTERN OR ESCAPE EXPRESSION

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The incorrect output was returned when using LIKE predicate
  with host-var or '?'(parameter marker) and the host-var value
  included a pattern or escape expression.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users of queries that    *
  *                 use workfiles (CM and above)                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: Problem 1:                              *
  *                                                              *
  *                      INCORROUT returned after repeated SQL   *
  *                      OPEN cursor for a query that uses       *
  *                      LIKE :host-var or '?' (parameter        *
  *                      marker) predicate when the host-var     *
  *                      value includes a pattern or escape      *
  *                      expression and the user application     *
  *                      changes the host-var value between      *
  *                      the initial OPEN cursor and             *
  *                      a subsequent OPEN cursor.               *
  *                                                              *
  *                      Problem 2:                              *
  *                                                              *
  *                      Storage leak possible on queries that   *
  *                      use workfiles                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  Problem 1:
  ----------
  
  In certain cases of a query / SELECT that specifies a LIKE
  :host-variable or '?' (parameter marker) predicate and the user
  application's input host-variable value includes a pattern or
  escape expression, incorrout is returned after the second or
  later SQL OPEN or execution of that query (following an SQL
  CLOSE cursor) within the same transaction and the user
  application changed the LIKE host-var value since the first OPEN
  or execution of the query.
  
  The certain cases of incorrout for the above described scenario
  are when DB2 determines that --
  (1) the query results must be 'staged' into an internal DB2
      workfile before returning the result to the user
      application, and
  (2) DB2 needs to apply the LIKE :host-var /'?' predicate to
      that workfile result.
  
  If DB2 applies this specific LIKE predicate directly to
  the base table rows themselves, the incorrout does not occur.
  
  DB2 Development determined that the problem occurs due to DB2
  internal predicate optimization done for this specific case of
  LIKE :host-var / '?' predicate (as previously described) when
  applied to a DB2 internal workfile result. This predicate
  optimization should be destroyed on the SQL CLOSE for the query
  and new predicate optimization built on a subsequent re-OPEN for
  the query, when a new input value for the LIKE :host-var / '?'
  would be considered when building the predicate optimization.
  However, in this incorrout case using workfile results,
  the optimization is not redone on a subsequent re-OPEN of
  the query when the :host-var value could have changed since
  the initial SQL OPEN. Therefore, the re-OPEN used predicate
  optimization that was built using the input LIKE :host-var value
  from the initial OPEN for the query.
  
  This incorrout for this specific LIKE predicate case may also
  occur for other SQL statements where this LIKE predicate can be
  used.
  
  Example of a query that exposed this problem:
  
  SELECT A, B, C, D
    FROM  TAB1
    WHERE C = ? AND E LIKE ?
  
  
  Problem 2:
  ----------
  
  Storage leak may occur after continued runs of queries that use
  workfiles. The leak involves the so-called P Procs (predicate
  procs), new in V10 CM and higher, when they are built for
  workfiles. The eyecatcher in the dump is 'SelP'.
  
  The problem is that these procs are acquired but not freed.
  
  Example of a query for this problem is same as for Problem 1.
  

Problem conclusion

• Both problems are corrected by disabling the building of P Procs
  when it comes to workfile processing.
  
  Additional keywords: SQLINCORR SQLINCORROUT DB2INCORR/K
                       PARAMETERMARKER SQLPMARKER SQLPMK
                       SQLLIKE SQLHOSTVAR DB2STGLK/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK79281

Modules/Macros

• DSNIWNRF
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK79281

     UP12/06/19 P F206

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.