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?

Please write concrete statements.

a) Create statements of the two MQTs.

b) The query statement.
c) The insert statement.

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:

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.

Another a little issue is...
it might be replaced by
WHERE
SALE_DOCUMENT_TYPE_D41.DOCUMENT_TYPE not in ('99', '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.

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.

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.

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
;

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.

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.