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

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

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

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?

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

You might do well by posting the output from db2exfmt for the statement in question.

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

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

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.