Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33

    Unanswered: different behaviour between SELECT and INSERT

    I'm going crazy.
    Hi all I need all your professional knowledge to get explained this DB2 behaviour.

    I'm running DB2 LUW 9.5 FP7 on RedHat Linux.
    It serves a BI environment so I made a lot of optimizztion to get fast results.
    I have built 2 summary tables based on the main fact table, the first has been sumarized by day and the second has been summarized by month.
    When I run a query summarizing the resukts by month, the execution plan involves the second MQT (grouped by month) as we can expect.
    Now the surprise!
    When I use the same query to populate the datamart table... the plan changes in the way using the less aggregated MQT (grouped by day).
    This is a non-sense for me.

    Why this happens?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please write concrete statements.

    a) Create statements of the two MQTs.

    b) The query statement.
    When I run a query summarizing the resukts by month, the execution plan involves the second MQT ...
    c) The insert statement.
    When I use the same query to populate the datamart table...

  3. #3
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33
    a1) the first MQT DDL (group by day)
    CREATE TABLE "DWHN1"."VEN_ANDFATSUM_MQ"
    ( "ROW_COUNT", "DATE_ID", "WAREHOUSE_ID",
    "DOCUMENT_TYPE", "CUSTOMER_ID", "PROMO_ID", "NET_PRICE_SUM", "ITEM_QUANTITY_SUM",
    GIFTED_QUANTITY_SUM, MISSING_QUANTITY_SUM, PURCHASE_PRICE_SUM, LABEL_PRICE_SUM,
    LIST_PRICE_SUM, SELLING_PRICE_SUM, NET_AMOUNT_SUM, ITEM_DISCOUNT_SUM,
    VALACQ_SUM )
    AS
    ( SELECT COUNT (*) AS ROW_COUNT,
    DATE_ID,
    WAREHOUSE_ID,
    DOCUMENT_TYPE,
    CUSTOMER_ID,
    PROMO_ID,
    SUM (NET_PRICE) AS NET_PRICE_SUM,
    SUM (ITEM_QUANTITY) AS ITEM_QUANTITY_SUM,
    SUM (GIFTED_QUANTITY) AS GIFTED_QUANTITY_SUM,
    SUM (MISSING_QUANTITY) AS MISSING_QUANTITY_SUM,
    SUM (PURCHASE_PRICE) AS PURCHASE_PRICE_SUM,
    SUM (LABEL_PRICE) AS LABEL_PRICE_SUM,
    SUM (LIST_PRICE) AS LIST_PRICE_SUM,
    SUM (SELLING_PRICE) AS SELLING_PRICE_SUM,
    SUM (NET_AMOUNT) AS NET_AMOUNT_SUM,
    SUM (ITEM_DISCOUNT) AS ITEM_DISCOUNT_SUM,
    SUM (PURCHASE_PRICE * (ITEM_QUANTITY + GIFTED_QUANTITY)) AS VALACQ_SUM
    FROM
    SELLING.SALE_F
    GROUP BY
    DATE_ID,
    WAREHOUSE_ID,
    DOCUMENT_TYPE,
    CUSTOMER_ID,
    PROMO_ID )
    DATA INITIALLY DEFERRED REFRESH
    DEFERRED ENABLE QUERY OPTIMIZATION IN "SALES_MQT1" VALUE COMPRESSION;

    a2) the second MQT DDL (group by month)
    CREATE TABLE "DWHN1"."VEN_ANDFATMTHSUM_MQ" ( "ROW_COUNT", "ID_YEAR", "ID_QUARTER", "ID_MONTH", "WAREHOUSE_ID", "DOCUMENT_TYPE", "CUSTOMER_ID",
    "PROMO_ID", "NET_PRICE_SUM", "ITEM_QUANTITY_SUM", "GIFTED_QUANTITY_SUM",
    "MISSING_QUANTITY_SUM", "PURCHASE_PRICE_SUM", "LABEL_PRICE_SUM", "LIST_PRICE_SUM",
    "SELLING_PRICE_SUM", "NET_AMOUNT_SUM", "ITEM_DISCOUNT_SUM", "VALACQ_SUM"
    )
    AS (
    SELECT
    COUNT (*) AS ROW_COUNT,
    D.ID_YEAR,
    D.ID_QUARTER,
    D.ID_MONTH,
    S.WAREHOUSE_ID,
    S.DOCUMENT_TYPE,
    S.CUSTOMER_ID,
    S.PROMO_ID,
    SUM (S.NET_PRICE) AS NET_PRICE_SUM,
    SUM (S.ITEM_QUANTITY) AS ITEM_QUANTITY_SUM,
    SUM (S.GIFTED_QUANTITY) AS GIFTED_QUANTITY_SUM,
    SUM (S.MISSING_QUANTITY) AS MISSING_QUANTITY_SUM,
    SUM (S.PURCHASE_PRICE) AS PURCHASE_PRICE_SUM,
    SUM (S.LABEL_PRICE) AS LABEL_PRICE_SUM,
    SUM (S.LIST_PRICE) AS LIST_PRICE_SUM,
    SUM (S.SELLING_PRICE) AS SELLING_PRICE_SUM,
    SUM (S.NET_AMOUNT) AS NET_AMOUNT_SUM,
    SUM (S.ITEM_DISCOUNT) AS ITEM_DISCOUNT_SUM,
    SUM (S.PURCHASE_PRICE * (S.ITEM_QUANTITY + S.GIFTED_QUANTITY)) AS VALACQ_SUM
    FROM
    SELLING.SALE_F S,
    SCD.LK_DATE D
    WHERE
    S.DATE_ID=D.ID_DAY
    GROUP BY
    D.ID_YEAR,
    D.ID_QUARTER,
    D.ID_MONTH,
    S.WAREHOUSE_ID,
    S.DOCUMENT_TYPE,
    S.CUSTOMER_ID,
    S.PROMO_ID )
    DATA INITIALLY DEFERRED
    REFRESH DEFERRED ENABLE QUERY OPTIMIZATION IN "SALES_MQT1"
    VALUE COMPRESSION;

    b) select query
    SELECT
    WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME,
    WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME_DS,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR AS FISCAL_YEAR,
    LK_FISCAL_YEAR54.DSS_FISCAL_YEAR AS FISCAL_YEAR_DS,
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER AS FISCAL_QUARTER,
    LK_FISCAL_QUARTER60.DSS_FISCAL_QUARTER AS FISCAL_QUARTER_DS,
    LK_MONTH57.ID_MONTH AS MONTH_ID,
    LK_MONTH57.DSS_MONTH AS MONTH_DS,
    SUM(SALES_F.NET_PRICE( AS NET_PRICE_SUM
    FROM
    SCD.LK_DATE LK_DATE55,
    SCD.LK_FISCAL_QUARTER LK_FISCAL_QUARTER60,
    SCD.LK_FISCAL_YEAR LK_FISCAL_YEAR54,
    SCD.LK_MONTH LK_MONTH57,
    SCD.SALE_DOCUMENT_TYPE_D SALE_DOCUMENT_TYPE_D41,
    SCD.WAREHOUSE WAREHOUSE_D28,
    SELLING.SALES_F SALES_F
    WHERE
    (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE != '99')
    AND (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('85', '91')
    AND (WAREHOUSE_D28.WAREHOUSE_TYPE='Fisico' AND WAREHOUSE_D28.COMPANY_NAME <> 'ACME')
    AND LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER=LK_MONTH57.I D_FISCAL_QUARTER
    AND LK_FISCAL_YEAR54.ID_FISCAL_YEAR=LK_FISCAL_QUARTER6 0.ID_FISCAL_YEAR
    AND LK_MONTH57.ID_MONTH=LK_DATE55.ID_MONTH
    AND SALES_F.DOCUMENT_TYPE=SALE_DOCUMENT_TYPED41.DOCUME NT_TYPE
    AND SALES_F.WAREHOUSE_ID=WAREHOUSE_D28.WAREHOUSE_ID
    GROUP BY
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.DSS_FISCAL_YEAR,
    WAREHOUSE_D28.SYSTEM_NAME,
    LK_MONTH57.ID_MONTH,
    LK_FISCAL_QUARTER60.DSS_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR,
    LK_MONTH57.DSS_MONTH:

    c) insert statement
    INSERT INTO DX00000001
    SELECT
    WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME,
    WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME_DS,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR AS FISCAL_YEAR,
    LK_FISCAL_YEAR54.DSS_FISCAL_YEAR AS FISCAL_YEAR_DS,
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER AS FISCAL_QUARTER,
    LK_FISCAL_QUARTER60.DSS_FISCAL_QUARTER AS FISCAL_QUARTER_DS,
    LK_MONTH57.ID_MONTH AS MONTH_ID,
    LK_MONTH57.DSS_MONTH AS MONTH_DS,
    SUM(SALES_F.NET_PRICE( AS NET_PRICE_SUM
    FROM
    SCD.LK_DATE LK_DATE55,
    SCD.LK_FISCAL_QUARTER LK_FISCAL_QUARTER60,
    SCD.LK_FISCAL_YEAR LK_FISCAL_YEAR54,
    SCD.LK_MONTH LK_MONTH57,
    SCD.SALE_DOCUMENT_TYPE_D SALE_DOCUMENT_TYPE_D41,
    SCD.WAREHOUSE WAREHOUSE_D28,
    SELLING.SALES_F SALES_F
    WHERE
    (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE != '99')
    AND (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('85', '91')
    AND (WAREHOUSE_D28.WAREHOUSE_TYPE='Fisico' AND WAREHOUSE_D28.COMPANY_NAME <> 'ACME')
    AND LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER=LK_MONTH57.I D_FISCAL_QUARTER
    AND LK_FISCAL_YEAR54.ID_FISCAL_YEAR=LK_FISCAL_QUARTER6 0.ID_FISCAL_YEAR
    AND LK_MONTH57.ID_MONTH=LK_DATE55.ID_MONTH
    AND SALES_F.DOCUMENT_TYPE=SALE_DOCUMENT_TYPED41.DOCUME NT_TYPE
    AND SALES_F.WAREHOUSE_ID=WAREHOUSE_D28.WAREHOUSE_ID
    GROUP BY
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.DSS_FISCAL_YEAR,
    WAREHOUSE_D28.SYSTEM_NAME,
    LK_MONTH57.ID_MONTH,
    LK_FISCAL_QUARTER60.DSS_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR,
    LK_MONTH57.DSS_MONTH:

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There were two unbalanced parentheses.
    I assumed ....
    , SUM(SALES_F.NET_PRICE/*(*/) AS NET_PRICE_SUM
    and
    AND /*(*/SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('85', '91')


    You didn't specify the relationship of SCD.LK_DATE and SELLING.SALES_F in your query and insert statement.
    There were relationships between tables in each (temporary named)table group
    table group 1: SCD.LK_DATE , SCD.LK_FISCAL_QUARTER , SCD.LK_FISCAL_YEAR , SCD.LK_MONTH
    table group 2: SCD.SALE_DOCUMENT_TYPE_D , SCD.WAREHOUSE , SELLING.SALES_F
    But, no relationship between table group 1 and table group 2.
    That means the query requested cartesian join of (result of joins in table group 1) and (result of joins in table group 2).

    Please try by adding the following condition(equivalent to second MQT) in your query and insert statement.
    AND SALES_F.DATE_ID = LK_DATE55.ID_DAY

    Optimizer doesn't investigate all join sequences and join methods depending on current optimization level.
    So, if you didn't supply enough conditions, optimizer might choose non-optimal access path.


    By the way, I wondered how you got right result from your query
    without specifying relationship of SCD.LK_DATE and SELLING.SALES_F.
    Last edited by tonkuma; 12-10-11 at 12:21. Reason: Add explanations.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another a little issue is...
    ...
    WHERE
    (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE != '99')
    AND (SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('85', '91')
    ...
    it might be replaced by
    WHERE
    SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('99', '85', '91')

  6. #6
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33
    Quote Originally Posted by tonkuma View Post
    There were two unbalanced parentheses.
    I assumed ....
    , SUM(SALES_F.NET_PRICE/*(*/) AS NET_PRICE_SUM
    and
    AND /*(*/SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('85', '91')
    you are right; my fault. I had no chance to use COPY & PASTE function to report the query so there are a lot of errors.
    By the way, the parenthesis are correctly assumed by you.

    Quote Originally Posted by tonkuma View Post
    You didn't specify the relationship of SCD.LK_DATE and SELLING.SALES_F in your query and insert statement.
    There were relationships between tables in each (temporary named)table group
    table group 1: SCD.LK_DATE , SCD.LK_FISCAL_QUARTER , SCD.LK_FISCAL_YEAR , SCD.LK_MONTH
    table group 2: SCD.SALE_DOCUMENT_TYPE_D , SCD.WAREHOUSE , SELLING.SALES_F
    But, no relationship between table group 1 and table group 2.
    That means the query requested cartesian join of (result of joins in table group 1) and (result of joins in table group 2).

    Please try by adding the following condition(equivalent to second MQT) in your query and insert statement.
    AND SALES_F.DATE_ID = LK_DATE55.ID_DAY
    and again you are right! The join condition were in place, but missed by me while copying. Sorry.

    If can help I have optimization level set to 7, and tryied also setting it to 9, but without results.
    Last edited by dvillani; 12-11-11 at 10:41.

  7. #7
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33
    Yes I know, but this is an application generated filter condition that I can't control.
    They have been defined separately as two distinct filter conditions and cannot be combined.
    I hope the optimizer is smart enough to consider them a single filter condition.

  8. #8
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33

    Updated query

    The following is the right query I'm using (without the typing errors):

    INSERT INTO DX000000001
    SELECT
    WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME_ID,WAREHOUSE_D28.SYSTEM_NAME as SYSTEM_NAME_DS,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR as FISCAL_YEAR_ID,LK_FISCAL_YEAR54.DSS_FISCAL_YEAR as FISCAL_YEAR_DS,
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER as FISCAL_QUARTER_ID,LK_FISCAL_QUARTER60.DSS_FISCAL_Q UARTER as FISCAL_QUARTER_DS,
    LK_MONTH57.ID_MONTH as MONTH_ID,LK_MONTH57.DSS_MONTH as MONTH_DS,
    SUM(SALES_F.NET_PRICE) AS NET_PRICE_SUM_
    FROM
    SCD.LK_DATE LK_DATE55,
    SCD.LK_FISCAL_QUARTER LK_FISCAL_QUARTER60,
    SCD.LK_FISCAL_YEAR LK_FISCAL_YEAR54,
    SCD.LK_MONTH LK_MONTH57,
    SCD.SALE_DOCUMENT_TYPE_D SALE_DOCUMENT_TYPE_D41,
    SCD.WAREHOUSE_D WAREHOUSE_D28,
    SELLING.SALES_F SALES_F
    WHERE
    ( SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE != '99' )
    AND ( SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ( '85' , '91' ) )
    AND ( WAREHOUSE_D28.WAREHOUSE_TYPE = 'Fisico' AND WAREHOUSE_D28.COMPANY_NAME <> 'ACME' )
    AND LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER=LK_MONTH57.I D_FISCAL_QUARTER
    AND LK_FISCAL_YEAR54.ID_FISCAL_YEAR=LK_FISCAL_QUARTER6 0.ID_FISCAL_YEAR
    AND LK_MONTH57.ID_MONTH=LK_DATE55.ID_MONTH
    AND SALES_F.DATE_ID=LK_DATE55.ID_DAY
    AND SALES_F.DOCUMENT_TYPE=SALE_DOCUMENT_TYPE_D41.DOCUM ENT_TYPE
    AND SALES_F.WAREHOUSE_ID=WAREHOUSE_D28.WAREHOUSE_ID
    GROUP BY
    LK_FISCAL_QUARTER60.ID_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.DSS_FISCAL_YEAR,
    WAREHOUSE_D28.SYSTEM_NAME,
    LK_MONTH57.ID_MONTH,
    LK_FISCAL_QUARTER60.DSS_FISCAL_QUARTER,
    LK_FISCAL_YEAR54.ID_FISCAL_YEAR,
    LK_MONTH57.DSS_MONTH
    ;

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dvillani View Post
    The following is the right query I'm using (without the typing errors):


    ...AND SALES_F.DATE_ID=LK_DATE55.ID_DAY...
    ID_DAY would not be present in the "monthly" MQT, would it?

  10. #10
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33
    Quote Originally Posted by n_i View Post
    ID_DAY would not be present in the "monthly" MQT, would it?
    the "monthly" MQT is grouped by month values. ID_DAY is not needed.

    And today I have an update on the case.

    Tested the behaviour on DB2 9.7 and it work in the way I expect to do!
    The right MQT has been selected for the INSERT query.
    This suggested to check for an updated fixpack for my installed 9.5 version.
    IBM site show the latest fixpack 8 is on line and now I'm downloding it to test the changes.
    While browsing I see the APAR IC75527 "QUERY COMPILER MAY NOT MATCH MQT TO MODIFYING STMT WHOSE SOURCE SUBQUERY HAS UNION ALL JOINING WITH MQT DEPENDENT BASE TABLE" seems to match the case.
    I will let you know of my test result just after installed the fixpack.

  11. #11
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33

    [SOLVED] Installing Fixpack 8

    Hi All,

    the problem has been solved just after installing the latest Fixpack 8 for DB2 9.5
    These kind of trouble are always hard to find, but fortunately there are always very active support developers working at IBM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •