Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Optimize for 1 row

    Hi All,

    I have to Optimize one DB2 Query which is taking lot of time to execute.
    So I am using the clause "Optimize For 1 Row" after a select statement. Below is the query:

    insert into Table1 (field1,field2)
    (select field1,field2 from Table2 where ((field4 in ('1','2')) and (field5 != 'ABC')) optimize for 1 row);


    The above query is returning in the below error

    ERROR:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0199N The use of the reserved word "OPTIMIZE" following "" is not valid.
    Expected tokens may include: ") UNION EXCEPT". SQLSTATE=42601


    If I give the below query without an insert statement, it is resulting in rows without any errors.

    SQL :
    select field1,field2 from Table2 where ((field4 in ('1','2')) and (field5 != 'ABC')) optimize for 1 row;

    Could you please help me in resolving the error how to use this select query including Optimize for 1 row with an insert statement

    Thanks,
    Krishna

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Generally, optimize for 1 row will turn off sequential prefetch (which preloads rows into the bufferpools before they are actually asked for) and does not actually affect the number of rows returned.

    Perhaps you want "fetch first 1 rows only"? If not, just leave off the optimize clause because it is not going to make much of a difference.

    Maybe you need an index on field4 (or better yet a composite index of (field4, field5)? if you already have an index on that column and it still performs poorly, then try this:

    insert into Table1 (field1,field2)
    (select field1,field2 from Table2 where ((field4 between '1' and '2') and (field5 != 'ABC')));

    or if the sub-select could possibly return multiple rows and you only want the first one, then:

    insert into Table1 (field1,field2)
    (select field1,field2 from Table2 where ((field4 between '1' and '2') and (field5 != 'ABC')) fetch first 1 rows only);
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2011
    Posts
    3

    Any Solution

    Hi Krishna,

    Did you happen to find any solution to this problem of yours. I am also currently facing the same issue.

    Thanks in advance
    Anurag

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's a syntax question. OPTIMIZE FOR n ROWS can be specified for a Select-statement but not in a Fullselect. An INSERT statement can only use a fullselect but not a SELECT statement. Thus, you cannot specify OPTIMIZE FOR in such an INSERT.

    The question is what you want to optimize there. Why do you think you need this clause?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2011
    Posts
    3
    Hi Stolze,

    Thanks for the reply. I will give you a brief background of the problem that led me to use OPTIMIZE in the query.

    -----------
    The environment i am working on is OLTP. There's a staging table T where data can vary from 0 to 1.5 million rows in a day.
    The table T is non partitioned ( tablespace is on admin node ). The average time to process 1000 records is few milliseconds which is fine until the records reach around 1 M mark. At 1M the optimizer some how stops using INDX Scan and starts TABLE Scan( 95% of the times).

    I decided to mark the table T as VOLATILE to force it to use index scan( as mentioned in publib boulder) but still the query plan shows a TABLE SCAN though the table is marked as volatile.
    Since this volatile stuff didn't work i decided to try OPTIMIZE with the idea that it may favour index scan and here is where i am stuck now.

    NOTE - The concerned query has FETCH FIRST 1000 rows in the select which is also not helping.

    Please let me know if you need more input.Any help will be appreciated.

    Thanks a lot.
    Anurag

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You might do well by posting the output from db2exfmt for the statement in question.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Anurag

    If this is a staging table and if the rows vary between 0 and n million a day, an option is to 'fix' the statistics. RUNSTATS on the table at about 1 m mark. After that, do not collect stats.c

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jun 2011
    Posts
    3
    Hi n_i,

    I have attached the query plan as well as the query in question. Let me know if you need further details.(opening it in notepad++ will be more presentable)


    @sathyaram_s

    This is what i have been doing. But i want to get rid of this dependency of fixing the statistics. I want to be in a position to generate a good plan irrespective of the amount of data in the table.

    Thanks
    Anurag
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anu.gemi View Post
    Hi n_i,

    I have attached the query plan as well as the query in question. Let me know if you need further details.(opening it in notepad++ will be more presentable)
    What you have attached is not db2exfmt output.

    Since you chose not to provide the actual explain output, nor the actual query, I can only wish you good luck in your optimization efforts.

Posting Permissions

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