A fix is available
APAR status
Closed as program error.
Error description
Incorrect output (less rows returned) can happen on a SQL statement that satisfies ALL of the following conditions: - JOIN - combination of AND/OR predicates - SUBSTR functions in: IN predicates with more than one IN-list item, or BETWEEN predicates, or equal predicates - SUBSTR is on a column declared as VARCHAR (or VARGRAPHIC) 254 or larger - SUBSTR has to start from position 1 Additional keywords to use when searching: INLIST SQLINLIST DB2INCORR/K INCORROUT SQLINCORR SQLINCORROUT
Local fix
Disable query re-write hidden zparm
Problem summary
**************************************************************** * USERS AFFECTED: 1. All users of DB2 10 and DB2 11 for z/OS * * who use local predicates with literal * * strings that are 254 bytes long or longer * * * * 2. All users of DB2 11 for z/OS who use * * queries use SUBSTR is on a column declared * * as VARCHAR or VARGRAPHIC with length * * of 254 or longer * **************************************************************** * PROBLEM DESCRIPTION: On both DB2 10 and DB2 11, incorrect * * output (too few rows returned) can * * occur for a query meeting all of the * * following conditions: * * - involves at least 2 tables * * - has a combination of AND/OR * * predicates * * - has local predicates containing * * literal strings of 254 bytes long * * or longer * * * * On DB2 11, incorrect output (too few * * rows returned) can occur for a query * * meeting all of the following * * conditions: * * - involves at least 2 tables * * - has a combination of AND/OR * * predicates * * - SUBSTR functions in local predicates * * - SUBSTR is on a column declared as * * VARCHAR or VARGRAPHIC with length * * of 254 or longer * * - SUBSTR has a starting position of 1 * **************************************************************** * RECOMMENDATION: * **************************************************************** On both DB2 10 and DB2 11, incorrect output (too few rows returned) can occur for a query meeting all of the following conditions: - involves at least 2 tables - has a combination of AND/OR predicates - has local predicates containing literal strings of 254 bytes long or longer On DB2 11, incorrect output (too few rows returned) can occur for a query meeting all of the following conditions: - involves at least 2 tables - has a combination of AND/OR predicates - SUBSTR functions in local predicates - SUBSTR is on a column declared as VARCHAR or VARGRAPHIC with length of 254 or longer - SUBSTR has a starting position of 1 An incorrect set of added predicates are generated for the query, which leads to incorrect output. Additional Keywords: SQLINCORROUT SQLINCORR INCORROUT DB2INCORR/K SQLSUBSTR SQLBETWEEN SQLIN SQLVARCHAR SQLVARGRAPHIC
Problem conclusion
DB2 code is updated to generate the correct added predicates or the correct predicate structure for queries matching the conditions above.
Temporary fix
********* * HIPER * *********
Comments
×**** PE16/06/10 FIX IN ERROR. SEE APAR PI63512 FOR DESCRIPTION ×**** PE16/06/10 FIX IN ERROR. SEE APAR PI63512 FOR DESCRIPTION
APAR Information
APAR number
PI56056
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-01-26
Closed date
2016-02-22
Last modified date
2016-08-25
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI35552 UI35553
Modules/Macros
DSNXOPPD
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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":"11.0","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":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
25 August 2016