Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Lightbulb Unanswered: Query optimisation

    I have a queries which retrives data from a table containg 130 collumns and 80,000 rows into 14 tables. It takes about 3 to 4 hrs. Can any one suggest about how to reduce this time. Is there any property that can be set so that this happens in less time. In Oracle i heard that the some parameter called "undosegment" . Is there such property in DB2. Please help me.

  2. #2
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    Hello

    Tell me these things first,

    wht type of data?
    Is there any time varience in different time?
    Do you have enough space in bufferpool and wht is your os?
    Last edited by mathew.eldho; 03-21-07 at 06:09.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  3. #3
    Join Date
    Mar 2007
    Posts
    4

    It consists of smallInt, Float, varchar.

    1 .The data is mainly of numeric type except 5 or collumns. consists of smallInt, Num, Float,Decimal.

    2. what do you mean by time varience. sorry I am new to programming.

    3. My Os is Win Xp. How much min should be there in buffer pool.

  4. #4
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    Hey Satish,

    I guess you are from India and if you don't mind you can give call on 09890637850 after 3.5 hour.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would suggest that you take the query and run it through the Design Advisor. It will give you some suggestions on Indexes, MQTs etc., which you can then use to tune your system.

    Also, have you collected recent statistics?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2007
    Posts
    4
    Hi Stolze,
    For my table there are no Indexes and cant apply also as it is data produced from an experiment and my queries are written in stored procedure which simple
    "Insert into sample1 select 1,2,3 from source"
    "Insert into sample2 select 1,4,5 from source" similarly 14 queries.
    2 . What statistics should i check.

    My thought is to find for a property such as to stop log entery or some thing like that which can be made off to reduce the time of loading.
    Any other suggestions would be highly helpful.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What exactly do you try to do? Load data into some tables (as you say now) or retrieve it from there and do some sort of processing (as you said in the initial post)?

    I don't get the part "data produced from an experiment." DB2 doesn't care where the data comes from - as long as it is the data you want to process.

    For data retrieval: You can extract the single INSERT/UPDATE/DELETE/SELECT statements from the stored procedures and provide those as workload to the Design Advisor. Then you will get suggestions.

    As for statistics, have a look at your code and which tables are accessed there. Then collect statistics on those tables (hint: RUNSTATS command).

    For data loading: You could turn logging of with the ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY statement. (Note that this has certain implications on the recoverability of the database.) Also, using the LOAD statement would be your best approach to quickly load tables. Maybe you should prepare LOAD files and dump the content into the database using the LOAD command (available through the ADMIN_CMD procedure if I'm not mistaken).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2007
    Posts
    4

    Thank u

    Hi stolze,
    Thank u very much. The property activate not logged intially is helping me. I have made a test. I hope it works with my original query also.

    Thanks
    Satish

Posting Permissions

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