Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: How to split 'grouping sets' query

    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 ))
    ;

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you are in heap of trouble.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 10-16-03 at 03:05.

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    How about using a query for each different grouping set? That would be my interpretation of 'splitting' the query as the DBA suggests....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Oct 2003
    Posts
    15

    Thanks for your reply

    Originally posted by blom0344
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    15

    Thanks for your reply

    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

    Originally posted by grofaty
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    15

    Thanks for your reply

    Originally posted by Marcus_A
    I think you are in heap of trouble.
    Hi Marcus_A:

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

    Thanks,
    Jennifer

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

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

    Grofaty

  9. #9
    Join Date
    Oct 2003
    Posts
    15

    Thanks for your reply

    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

    Originally posted by grofaty
    Hi,

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

    Grofaty

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

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

    Thanks,
    Grofaty

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sorry Jennifer, my post was meant to be a very bad joke.

  12. #12
    Join Date
    Oct 2003
    Posts
    15
    The new HEAPSZ is 10240.

    Jennifer


    Originally posted by grofaty
    Hi,

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

    Thanks,
    Grofaty

  13. #13
    Join Date
    Oct 2003
    Posts
    15
    Thanks for all who replied my post.

    Jennifer

    Originally posted by Marcus_A
    Sorry Jennifer, my post was meant to be a very bad joke.

  14. #14
    Join Date
    Oct 2003
    Posts
    15

    New problem came out

    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



    Originally posted by grofaty
    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

  15. #15
    Join Date
    Oct 2003
    Posts
    15

    Need help again.

    Hi Marcus:

    Please read my new problem.

    Thanks,
    Jennifer


    Originally posted by Marcus_A
    Sorry Jennifer, my post was meant to be a very bad joke.

Posting Permissions

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