Prevent OPTIMIZE FOR 1 ROW CLAUSE.

[Julian Nesci]

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.

[CRPence]

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

Post by Julian Nesci
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?