A fix is available
APAR status
Closed as program error.
Error description
You might encounter situations where handling of certain LIKE predicates could cause poor query performance. Following example describes the situation: - TAB1 having having columns C1 smallint C2 int C3 char(3) - column c3 having check constraint c3 between 'YES' and 'NON' - doing select on this table in form of select * from TAB1 where ... and C3 LIKE 'AA1234%' Instead of determining at compilation time that the LIKE predicate returns no rows, we end up with a time consuming table scan at run time. In contrast to the above explained example we have better behaviour if using another predicate - e.g. C3 LIKE 'AA1' Now query rewrite recognizes that the equality selects no rows, because 'AA1' falls outside the range allowed by the check constraint defined on TAB1.
Local fix
Create an index on the concering column (column C3 for the example above). This will result in an index scan which returns 0 rows and therefore returns very quickly.
Problem summary
USERS AFFECTED: all PROBLEM DESCRIPTION: Current LIKE predicate handling in DB2 may cause qoor query performance as a suboptimal access plan is used. PROBLEM SUMMARY: You might encounter situations where handling of certain LIKE predicates could cause poor query performance. Following example describes the situation: - TAB1 having having columns C1 smallint C2 int C3 char(3) - column c3 having check constraint c3 between 'YES' and 'NON' - doing select on this table in form of select * from TAB1 where ... and C3 LIKE 'AA1234%' Instead of determining at compilation time that the LIKE predicate returns no rows, we end up with a time consuming table scan at run time. In contrast to the above explained example we have better behaviour if using another predicate - e.g. C3 LIKE 'AA1' Now query rewrite recognizes that the equality selects no rows, because 'AA1' falls outside the range allowed by the check constraint defined on TAB1. LOCAL FIX: Create an index on the concering column (column C3 for the example above). This will result in an index scan which returns 0 rows and therefore returns very quickly.
Problem conclusion
First fixed in DB2 UDB Version 8, FixPak 16 (s080111)
Temporary fix
see LOCAL FIX
Comments
APAR Information
APAR number
IY82829
Reported component name
DB2 CEE AIX
Reported component ID
5765F3000
Reported release
820
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2006-03-31
Closed date
2008-02-06
Last modified date
2008-02-06
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
IZ02639
Fix information
Fixed component name
DB2 CEE AIX
Fixed component ID
5765F3000
Applicable component levels
R810 PSN
UP
R820 PSN
UP
R910 PSN
UP
R950 PSN
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"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":"820"}]
Document Information
Modified date:
02 October 2021