IBM Support

JR27729: MQT RECOMMENDATIONS GIVEN BY THE DB2 DESIGN ADVISOR (DB2ADVIS) MIGHT NOT BE IN PROPER SQL SYNTAX

Subscribe

You can track all active APARs for this component.

 

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:

    JR27722

  • 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

[{"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":"950"}]

Document Information

Modified date:
07 October 2021