A fix is available
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 number
PM60826
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2012-03-20
Closed date
2012-06-01
Last modified date
2012-07-02
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 July 2012