Hi Everyone:

This is my first posting.

I have a complex query and my DBA told me he could not increase the APPLHEAPSZ for me any more. He told to split this query, but I don't know how? Could anyone tell me? Thanks.

SELECT
COALESCE(PRD_NM, PSG_NM, PG_NM, LOB_NM, 'All Products') AS Product
, (SELECT MONTH_NAME_DA FROM T02510B.DATA_DATE) AS Time
, COALESCE(R.ROLE_NM, R.GROUP_NM, 'All Roles') AS Role
, COALESCE(G.GEO_AREA_NM, G. SUB_REGION_NM, G.REGION_NM, 'All Geo Areas' ) AS Geo_Area
, COALESCE(C.PAYMENT_METHOD_DA, 'All Members') as Pay_Method
, COUNT(DISTINCT PRTY_ID_NO ) AS Customers
FROM T02510B.CustTxnFact F
JOIN
T02510B.Product P
ON P.Product = F.Product
JOIN
T02510B.Geo_Area G
ON G.Geo_Area = F.Geo_Area
JOIN
T02510B.Role R
ON R.Role = F.Role
JOIN
T02510B.CARD_MEMBER C
ON
F.PAYMENT_METHOD = C.PAYMENT_METHOD_ID

GROUP BY
GROUPING SETS ( (), ( G.REGION_NM ), ( G. SUB_REGION_NM ), (G.GEO_AREA_NM) )
, GROUPING SETS ( (), ( LOB_NM ), ( PG_NM ), ( PSG_NM ), ( PRD_NM ) )
, GROUPING SETS ( (), ( R.GROUP_NM ), ( R.ROLE_NM ) )
, GROUPING SETS ( (), ( C.PAYMENT_METHOD_DA ))
;

I think you are in heap of trouble.

Hi,

Can you tell as how big the tables are:
- in pages
- in bytes
- in records?

Can you give more info about your operating system, database version and fixpack level.

Can you post the error message. You can also read the Must read before posting for more tips...

Are you sure that "grouping sets" makes you trouble? Try to delete the grouping sets just to make a performance test. Is it better?

Grofaty

How about using a query for each different grouping set? That would be my interpretation of 'splitting' the query as the DBA suggests....

The problem is, if I split it into 3 different grouping set, I lost records -- I call it crossing records.

Hi Grofaty:

I'm sure it's the 'grouping sets' trouble. I tested.

This table has about 270,000 records. My operation system is Windows 2000 and DB is DB2 verison 7. I don't know what is fixpack level...

Error message is SQLSTATE = 54001, The statement is too long or too cmplex.

The DBA has increated the APPLHEAPSZ, I can run 2 grouping sets now, but still cannot run 4 grouping sets.

Thanks,
Jennifer

Hi Marcus_A:

It is but the DBA told me there is a limit for the APPLHEAPSZ

Thanks,
Jennifer

Hi,

Have you try to enlarge the temporaly tablespace. Ask the DBA?

Grofaty

Hi Grofaty:

The problem was solved. My project manager had a fight with DBA and he increased the APPLHEAPSZ for me.

Thanks you very much for your help.

Best wishes,
Jennifer

Hi,

Can you please share with forum, what is the the new value of APPLHEAPSZ?

Thanks,
Grofaty

Sorry Jennifer, my post was meant to be a very bad joke.

The new HEAPSZ is 10240.

Jennifer

Thanks for all who replied my post.

Jennifer

Hi Grofaty:

I have new problem after increasing APPLHEAPSZ.

When I wrote a script using more than 6 'group by grouping sets', even APPLHEAPSZ in 60000, it still doesn't work.

When I did the testing, 5 'group by grouping sets' is the MAX.

Here is the script and error message:

SELECT
COALESCE(PRD_NM, PSG_NM, PG_NM, LOB_NM, 'All Products') AS Product
, (SELECT MONTH_NAME_DA FROM T02510B.DATA_DATE) AS Time
, COALESCE(S.SOURCE_NM, S.PARTNER_NM, S.GROUP_NM, 'All Sources') AS Source
, COALESCE(AB.AGE_BAND_NM, 'All Ages') AS Age
, COALESCE(C.CHANNEL_NM, C.TYPE_NM, C.GROUP_NM, 'All Channels' ) AS Channel
, COALESCE(PH.PROD_HOLDING_NM, 'All Product Holdings' ) As Prod_Holding
, COALESCE(R.ROLE_NM, R.GROUP_NM, 'All Roles') AS Role
, COALESCE(G.GEO_AREA_NM, G. SUB_REGION_NM, G.REGION_NM, 'All Geo Areas' ) AS Geo_Area
, COALESCE(C.PAYMENT_METHOD_DA, 'All Members') as Pay_Method
, COUNT(DISTINCT PRTY_ID_NO ) AS Customers
FROM T02510B.CustTxnFact F
JOIN
T02510B.Product P
ON P.Product = F.Product

JOIN
T02510B.Geo_Area G
ON G.Geo_Area = F.Geo_Area

JOIN
T02510B.Source S
ON S.Source = F.Source

JOIN
T02510B.Age_Band AB
ON AB.Age_Band = F.Age_Band

JOIN
T02510B.Channel C
ON C.Channel = F.Channel

JOIN
T02510B.Prod_Holding PH
ON PH.Prod_Holding = F.Prod_Holding

JOIN
T02510B.Role R
ON R.Role = F.Role

JOIN
T02510B.CARD_MEMBER C
ON
F.PAYMENT_METHOD = C.PAYMENT_METHOD_ID

GROUP BY
GROUPING SETS ( (), ( G.REGION_NM ), ( G. SUB_REGION_NM ), (G.GEO_AREA_NM) )
, GROUPING SETS ( (), ( LOB_NM ), ( PG_NM ), ( PSG_NM ), ( PRD_NM ) )
, GROUPING SETS ( (), ( R.GROUP_NM ), ( R.ROLE_NM ) )
, GROUPING SETS ( (), ( S.GROUP_NM ), ( S.PARTNER_NM ), ( S.SOURCE_NM ) )
, GROUPING SETS ( (), ( AB.AGE_BAND_NM ) )
, GROUPING SETS ( (), ( C.GROUP_NM ), ( C.TYPE_NM ), ( C.CHANNEL_NM ) )
, GROUPING SETS ( (), ( PH.PROD_HOLDING_NM ) )
, GROUPING SETS ( (), ( C.PAYMENT_METHOD_DA ))

fetch first 100 rows only;

My question is:
Whether 'group by grouping sets' has limit?
How to solve this problem?

Thanks,
Jennifer

Hi Marcus:

Please read my new problem.

Thanks,
Jennifer