A fix is available
APAR status
Closed as program error.
Error description
In DB2 10, DB2 may neglect to use multi-index access when one of the predicates that should be matching in the multi-index plan is on a timestamp column. DB2 incorrectly marks the predicate on the timestamp column as stage 2 which means the desired multi-index access path is not a candidate access path. SQLPERFORMANCE SQLACCESSPATH SQLTIMESTAMP
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 for z/OS users of a static SQL * * statement that has a non-boolean term * * predicate with Timestamp column. * **************************************************************** * PROBLEM DESCRIPTION: Performance regression could happen for * * a static SQL statement that has a * * non-boolean term predicate with * * Timestamp column. * **************************************************************** * RECOMMENDATION: * **************************************************************** Performance regression could happen for a static SQL statement that has a non-boolean term predicate with Timestamp column. An examples is shown below: CREATE TABLE T1 (C1 CHAR(16), C2 TIMESTAMP); CREATE INDEX IX1 ON T1 (C1); CREATE INDEX IX2 ON T1 (C2); SELECT C1 INTO :HV1 FROM T1 WHERE (:HV2 > 0 AND C1 = 'X') OR C2 = :HV3; Multiple-index access can not be used for the above static SQL statement, while it could on DB2 9 for z/OS. DB2 did not correctly process the aforementioned SQL statements, which caused the performance regression.
Problem conclusion
DB2 has been modified to correctly process the aforementioned SQL statement, so there will be no the performance regression as multiple-index access can be used. Additional keywords: SQLTIMESTAMP SQLHOSTVAR SQLACCESSPATH SQLPERFORMANCE MIDX MULTIINDEX
Temporary fix
Comments
APAR Information
APAR number
PM70345
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2012-08-06
Closed date
2012-09-28
Last modified date
2012-11-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK82227
Modules/Macros
DSNXOW2D
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK82227
UP12/10/16 P F210
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":"10.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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 November 2012