A fix is available
APAR status
Closed as program error.
Error description
For a sql coded with a OmniFind function, a sub-optimal access path results when an inefficient index is chosen for the inner table of a NLJ for sort avoidance. The inefficient index is selected over the more efficient ROWID index with clusterratio = 0. Additional Keywords: SQLACCESSPATH SQLINDEX
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: * * All users of DB2 11 and DB2 12 for z/OS * * who utilize tables with text search * * functions and queries with the * * SELECT DISTINCT clause. * **************************************************************** * PROBLEM DESCRIPTION: * * DB2 may choose a suboptimal access * * plan for a query with predicates * * containing text search functions * * when the following conditions * * are met. * * * * (1) The query has SELECT DISTINCT * * (2) The columns in (1) are from * * a table with text search functions * * (3) There exists an index to avoid * * sort for SELECT DISTINCT * * (4) The text search predicate can be * * evaluated by the OmniFind server * * * * DB2 may choose the index plan to * * avoid sort for SELECT DISTINCT * * when the access plan to facilitate * * (4) performs better. * * * * SQLACCESSPATH SQLWHERE * * TEXTSEARCH * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** DB2 may choose a suboptimal access plan for a query with predicates containing text search functions when the following conditions are met. (1) The query has SELECT DISTINCT (2) The columns in (1) are from a table with text search functions (3) There exists the indexes to avoid sort for SELECT DISTINCT (4) The text search predicate can be evaluated by the OmniFind server and by using the ROWID index. DB2 may choose the index plan to avoid sort for SELECT DISTINCT when the access plan to facilitate (4) performs better. The problem is that DB2 failed to recognize that for a table with OmniFind text search functions in the leading position, DB2 will always evaluate the OmniFind text search functions before accessing the table. As a result, a regular index cannot be used to avoid sort for SELECT DISTINCT.
Problem conclusion
This APAR fixes the problem described above by recognizing when the leading table contains predicates with text search functions, that it cannot utilize the indexes to avoid a sort for SELECT DISTINCT. The fix may change access plans and thus can have a performance impact.
Temporary fix
Comments
APAR Information
APAR number
PI89074
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-10-20
Closed date
2017-11-28
Last modified date
2018-01-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI52181 UI52182
Modules/Macros
DSNXOPRP
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:
03 January 2018