Re: Prevent OPTIMIZE FOR 1 ROW CLAUSE.

   Is it known that the app is adding the clause versus a .ini?  If the 
option is not already in the .ini file, perhaps just try one [for a 
value n other than one] and find out what happens.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/
c0007882.htm

   By default, the location of the DB2(R) CLI/ODBC configuration keyword 
file is in the sqllib directory on Window platforms, and in the 
sqllib/cfg directory of the database instance running the CLI/ODBC 
applications on UNIX(R) platforms. If the ODBC Driver Manager is used to 
configure a User Data Source on the Windows(R) platform, a db2cli.ini 
may be created in the user's home (profile) directory.
   The environment variable DB2CLIINIPATH can also be used to override 
the default and specify a different location for the file.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/
r0008803.htm

   OptimizeForNRows CLI/ODBC configuration keyword
    Keyword description:
     Append 'OPTIMIZE FOR n ROWS' clause to every select statement.
    db2cli.ini keyword syntax:
     OptimizeForNRows = integer
    Default setting:
     The clause is not appended.
    Equivalent statement attribute:
     SQL_ATTR_OPTIMIZE_FOR_NROWS
    Usage notes:
     This option will append the "OPTIMIZE FOR n ROWS" clause to every

select statement, where n is an integer larger than 0. If set to 0 (the 
default) this clause will not be appended.

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

Julian Nesci wrote:
> We're using DB2 UDB 8.2 FP 4 (8.1 FP 11) on Windows 2000 Servers.
> 
> I have a third party application whose code appends the
> OPTIMIZE FOR 1 ROW clause to every SELECT statement it issues to DB2,
> via ODBC.  This optimizer hint is causing problems when trying to
> tune some queries because it is occasionally forcing the use of
> non-optimal indexes.  As it's a package I don't have the luxury of
> removing the indexes I don't want (because it affects the support
> licence) or altering the code.
> 
> I know the db2cli.ini file can be used to append the
> OPTIMIZE FOR x ROWS clause to queries, although I've never done myself.
> I've not been able to find it if it can also be used to override or
> remove an existing OPTIMIZE FOR x ROWS clause which is already
> in the query?
> 

Hi,

We're using DB2 UDB 8.2 FP 4 (8.1 FP 11) on Windows 2000 Servers.

I have a third party application whose code appends the OPTIMIZE FOR 1 ROW 
clause to every SELECT statement it issues to DB2, via ODBC.  This optimizer 
hint is causing problems when trying to tune some queries because it is 
occasionally forcing the use of non-optimal indexes.  As it's a package I 
don't have the luxury of removing the indexes I don't want (because it 
affects the support licence) or altering the code.

I know the db2cli.ini file can be used to append the OPTIMIZE FOR x ROWS 
clause to queries, although I've never done myself.  I've not been able to 
find it if it can also be used to override or remove an existing OPTIMIZE 
FOR x ROWS clause which is already in the query?

Anyone know the answer to this?

Thanks in advance.

Cheers,
Julian.