Results 1 to 7 of 7

Thread: DGTT - sort

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

    Unanswered: DGTT - sort

    Application consists of many SP's (sql stored procedures). In the SP, they:

    1) define DGTT
    2) insert into DGTT
    3) select from DGTT

    The SP's perform some additional processing which I've excluded.


    I'm looking for ways to reduce the number of sorts and soft overflows that occur during step #3 (select from DGTT).


    For example, step #3 has:

    HTML Code:
            SELECT
            .......                                          
            FROM SESSION.xxx
            order by a asc,                                                                      
                        b asc,                                                                       
                        c asc,                                                                        
                        d asc

    I want to leave sort related db/dbm cfg parameters unchanged at this time.


    Some options:

    - add 'order by a,b,c,d' to step #2 so that data is already sorted according to the 'order by' in step #3
    - create index on DGTT with columns (a,b,c,d) and collects statistics


    Not sure if adding 'order by' to step #2 will make much difference? Creating an index on DGTT should reduce/eliminate sorts and sort overflows, but I think create index on DGTT operation is logged (?) even if DGTT is defined as not logged and the cost will go up during index maintenance (step #2) and the index will need bufferpool memory.


    What do you think? What else can be done to minimize the number of sorts (overflows)?


    DB2 is v9.1, should be upgraded to v9.7 soon.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    - add 'order by a,b,c,d' to step #2 so that data is already sorted according to the 'order by' in step #3 , are you sure this will work?
    "Insert into t1 select * from t2 order by c; select * from t1 " is not the same as "insert into t1 select * from t2 ; select * from t1 order by c", because the formmer will not gurantee the data will be sorted by c...

    A additional index is the best way to avoid the data sort。
    by the way , why do you use the DGTT?

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    I don't know why they use DGTT. This application is not new and I don't think they will be willing to make any changes to any of the SP's other than considering adding/changing some indexes / config parameter changes.

    If this was a permanent table and I saw lots of sort overflows / long sort times, then I think adding an index may help. But I'm not sure about creating an index on DGTT... What's the impact? And I'm not sure if adding 'sort by' to step #2 will help with sorts / sort overflows that occur during step #3. I think db2 will see do a sort even if the data is presorted unless there is an index? Step #2 does a select from a permanent table that already has an index on columns (a,b,c,d) so adding 'order by' to step #2 should be ok. But will it be of any use to step #3? I'm not sure.

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    Although you presorted data in step #2, db2 will still sort data in step 3 if step 3 has a order by clause。If your remove the order by from step 3, then the data will be disordered whatever you have sorted data in step2 or not 。 So add sort in step #2 is useless。。。。
    If you saw many lot of sort overflows and will not do any changes to sp , maybe you can try to increase sort heap size .....
    Last edited by fengsun2; 02-20-12 at 00:52.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by fengsun2 View Post
    If you saw many lot of sort overflows and will not do any changes to sp , maybe you can try to increase sort heap size .....

    Assuming the development team agrees to make SP changes, what kind of changes would you recommend?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A DGTT is also logged per default, at least in V9.7. You can turn this off by using the NOT LOGGED clause. What is it in your case?

    I would assume that no logging occurs on indexes on a DGTT if the DGTT itself is not logged.

    p.s: If you want to have a specific sort order for the results of a query, you MUST have an ORDER BY clause in the query itself. Anything else won't guarantee the sort order and DB2 may return the rows in any sequence it likes. So you can only create the index and that's about it. Doing an order by in step 2 may have marginal benefits because the sort operator in step 3 may work better with pre-sorted data. But you can't do away with the sort operator in step 3, so that sorting in step 2 may only incur overhead bottom line.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    The DGTT in question is defined as NOT LOGGED. I could not find any information about whether or not the create index on DGTT is logged. The manual just mentions that I/U/D are not logged if DGTT is defined as NOT LOGGED. The create table itself is logged. So, I suspect that the create index is logged as well.

    Thank you.

Posting Permissions

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