Fixes are available
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
A query over nicknames containing FETCH FIRST n ROWS ONLY clause may return incorrect results when the following conditions are met: 1. The query contains a FETCH FIRST n ROWS ONLY clause. 2. The query contains a join that can be processed in the remote data source. 3. The query join predicate can only be processed locally or the query contains other predicates that can only be processed locally and in both cases cannot be pushed down into the remote data source. A query containing column organized tables with similar criteria may also return incorrect results when the query also contains an ORDER BY clause. Example: *connect to remote database* CREATE TABLE remote.t1 (c1 int); CREATE TABLE remote.t2 (c1 int, c2 varchar(255)); insert into remote.t1 values (1); insert into remote.t2 values (1, 'bar'),(1, 'foo_bar'); *connect to local database* CREATE NICKNAME local.t1 FOR myserver.remote.t1; CREATE NICKNAME local.t2 FOR myserver.remote.t2; SET SCHEMA LOCAL; SELECT t2.c1 FROM t1 JOIN t2 ON t1.c1 = t2.c1 where INSTR(t2.c2, 'foo_', 1) > 0 FETCH FIRST 1 ROW ONLY; Expected Results: C1 ----------- 1 1 record(s) selected. Actual Results: C1 ----------- 0 record(s) selected. An indication of this problem can be found within the DB2 access plan details. For example, the access plan for the query above has a FILTER operation above a SHIP operation, and the SHIP operation is estimated to return 1 row. This may indicate that the remote data source is processing the FETCH FIRST n ROWS ONLY clause and the local database is processing the predicate that can only be processed locally: Rows RETURN ( 1) Cost I/O | 0.333333 FILTER ( 2) 596.263 57 | 1 SHIP ( 3) 596.228 57 /------+------\ 1000 1000 NICKNM: LOCAL NICKNM: LOCAL T2 T1 Q1 Q2 For column organized tables, the access plan above will use a CTQ operator instead of a SHIP operator.
Local fix
Either of these workarounds will avoid the incorrect results for nicknames: 1. Disable the FETCH FIRST n ROWS ONLY clause from being processed by the remote server: alter server myserver options(add db2_fetch_n_rows 'N') 2. If the predicate contains a function, create a function mapping object so that the predicate can be processed by the remote data source. In the example above, a function mapping object for the SYSIBM.INSTR function will allow the predicate to be processed by the remote data source.
Problem summary
**************************************************************** * USERS AFFECTED: * * NICKNAMES OR COLUMN ORGANIZED TABLES * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Please, See Error Description and install this fix. * ****************************************************************
Problem conclusion
Please, See Error Description and install this fix.
Temporary fix
Comments
APAR Information
APAR number
IT23741
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-01-15
Closed date
2018-03-16
Last modified date
2018-03-16
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 FOR LUW
Fixed component ID
DB2FORLUW
Applicable component levels
RB10 PSY
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2022