A fix is available
APAR status
Closed as program error.
Error description
Error Description: Incorrect output (less rows found) may happen when a query on a view contains the LIKE predicate and the VIEW has a UNION in its definition. This incorrout output is illustrated below: In view TESTTB the column COL1 contains 13 rows of data 'woody'. The following two queries returns different results. "SELECT * FROM TESTTB WHERE COL1 LIKE 'woody';" returns 7 rows. "SELECT * FROM TESTTB WHERE COL1 = 'woody';" returns 13 rows.
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 9 and 10 for z/OS users of an SQL * * statement that contains a table expression * * or view with a set operator and a LIKE * * predicate matching to an index on * * expression. * **************************************************************** * PROBLEM DESCRIPTION: 1. It contains a table expression or * * view with a set operator, and the * * table expression or view is * * materialized. * * 2. It contains a LIKE predicate that * * can be pushed down to the * * materialized table expression or * * view. * * 3. After push down, the LIKE * * predicate contains an expression, * * which matches to an index on * * expression. * * 4. The expressions in the different * * set branches return data with * * different nullability. * **************************************************************** * RECOMMENDATION: * **************************************************************** Incorrect output could happen for an SQL statement that satisfies all of the following conditions: 1. It contains a table expression or view with a set operator, and the table expression or view is materialized. 2. It contains a LIKE predicate that can be pushed down to the materialized table expression or view. 3. After push down, the LIKE predicate contains an expression, which matches to an index on expression. 4. The expressions in the different set branches return data with different nullability. An examples is shown below: CREATE TABLE T1 ( C1 VARCHAR(10) NOT NULL, C2 VARCHAR(10) ) CCSID UNICODE VOLATILE; CREATE INDEX IX1 ON T1 ( LOWER(C1, 'De_DE') ) NOT PADDED; INSERT INTO T1 VALUES('ABC', 'ABC'); INSERT INTO T1 VALUES('ABC', 'ABC'); SELECT * FROM (SELECT LOWER(C1, 'De_DE'), 1 FROM T1 UNION SELECT LOWER(C2, 'De_DE'), 2 FROM T1 ) TX(C1, C2) WHERE TX.C1 LIKE 'abc'; The query above should return 2 rows, while it only returns 1 row. DB2 did not correctly process the aforementioned SQL statements, which caused the incorrect output.
Problem conclusion
DB2 has been modified to correctly process the aforementioned SQL statement, so there will be no incorrect output. Additional keywords: SQLUNION SQLPREDPUSHDOWN SQLINDEXONEXP SQLINCORR INCORROUT SQLINCORROUT DB2INCORR/K SQLLIKE SQLINTERSECT SQLINTERSECTALL SQLEXCEPT SQLEXCEPTALL SQLUNIONALL SQLTABLEEXPR SQLVIEW SQLCTE
Temporary fix
********* * HIPER * *********
Comments
APAR Information
APAR number
PM75486
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2012-10-22
Closed date
2012-12-11
Last modified date
2013-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK90215 UK90216
Modules/Macros
DSNXOW2D
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":"9.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":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 January 2013