Fixes are available
DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
MQT Recommendations given by the DB2 Design Advisor (db2advis) might not be in proper SQL syntax. The symptom of this can be expressions in any of the queries being evaluated, that contain date/time/timestamp arithmetic that uses duration units such as MICROSECONDS, SECONDS, MINUTES, HOURS, DAYS, MONTHS and YEARS. The same also applies to date/time/timestamp arithmetic with decimal columns or constants. Consider this example: -- Create a table CREATE TABLE T0 (DATE_COL DATE); -- Provide this query as the workload to db2advis SELECT MIN_DATE_COL FROM (SELECT MIN(DAYS(DATE_COL)) MIN_DATE_COL FROM T0 ) T1 WHERE (DATE('9999-12-31') + -T1.MIN_DATE_COL DAY) <= DATE('2007-10-14') ; db2advis has the potential of giving the following MQT recommendation based on this query: CREATE SUMMARY TABLE MQT000000000000000 AS (SELECT Q4.C0 AS "C0" FROM TABLE (SELECT Q3.C0 AS "C0" FROM TABLE (SELECT MIN(Q2.C0) AS "C0" FROM TABLE (SELECT DAYS(Q1.DATE_COL) AS "C0" FROM T0 AS Q1 ) AS Q2 ) AS Q3 ) AS Q4 WHERE( +('12/31/9999', -(Q4.C0), 3) <= '10/14/2007') ) DATA INITIALLY DEFERRED REFRESH DEFERRED The predicate in the MQT recommendation output is not in proper SQL syntax. As mentioned above, similar issues will result when the arithmetic is based on a TIME or TIMESTAMP column/constant instead of a DATE column/constant.
Local fix
If the context of the date time expression in the query workload is known, it might be possible to modify the statement manually with the correct syntax expression.
Problem summary
MQT RECOMMENDATIONS GIVEN BY THE DB2 DESIGN ADVISOR (DB2ADVIS) MIGHT NOT BE IN PROPER SQL SYNTAX
Problem conclusion
MQT Recommendations given by the DB2 Design Advisor (db2advis) might not be in proper SQL syntax. The symptom of this can be expressions in any of the queries being evaluated, that contain date/time/timestamp arithmetic that uses duration units such as MICROSECONDS, SECONDS, MINUTES, HOURS, DAYS, MONTHS and YEARS. The same also applies to date/time/timestamp arithmetic with decimal columns or constants. Consider this example: -- Create a table CREATE TABLE T0 (DATE_COL DATE); -- Provide this query as the workload to db2advis SELECT MIN_DATE_COL FROM (SELECT MIN(DAYS(DATE_COL)) MIN_DATE_COL FROM T0 ) T1 WHERE (DATE('9999-12-31') + -T1.MIN_DATE_COL DAY) <= DATE('2007-10-14') ; db2advis has the potential of giving the following MQT recommendation based on this query: CREATE SUMMARY TABLE MQT000000000000000 AS (SELECT Q4.C0 AS "C0" FROM TABLE (SELECT Q3.C0 AS "C0" FROM TABLE (SELECT MIN(Q2.C0) AS "C0" FROM TABLE (SELECT DAYS(Q1.DATE_COL) AS "C0" FROM T0 AS Q1 ) AS Q2 ) AS Q3 ) AS Q4 WHERE( +('12/31/9999', -(Q4.C0), 3) <= '10/14/2007') ) DATA INITIALLY DEFERRED REFRESH DEFERRED The predicate in the MQT recommendation output is not in proper SQL syntax. As mentioned above, similar issues will result when the arithmetic is based on a TIME or TIMESTAMP column/constant instead of a DATE column/constant.
Temporary fix
If the context of the date time expression in the query workload is known, it might be possible to modify the statement manually with the correct syntax expression.
Comments
APAR Information
APAR number
JR27729
Reported component name
DB2 EDE WIN
Reported component ID
5724N7601
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-10-31
Closed date
2009-03-09
Last modified date
2009-03-09
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 EDE WIN
Fixed component ID
5724N7601
Applicable component levels
R950 PSY
UP
Document Information
Modified date:
07 October 2021