Hi,
I have a complex query that accesses very big tables in a db2 64bit v.8.1.1.128 running on AIX; the query has been fully optimized, and it is adequately supported by idexes. If I run the query from command line or command editor, it takes about six seconds: this is a very good result from my point of view.
The problem is that in the real things the same query is launched from a J2EE application running under WebSphere, using Ibm DB2 JDBC Universal Driver. No matter what the driver level is (I made several runs with different driver levels, from 3.4.65 to 4.2.73) as well as WebSphere version (6.1 and 7 were tested); in this environments it takes about 120 secs, so surely in this case some index is not used.
The difference between the run under the command editor and under websphere is that in this latter case the query is submitted by means of a prepared statement. The where clause contains a part like this:

where aDataField between ? and ?

Googling around I have found some similar problem arisen in other dbms (no db2); some of these problems were solved by casting the question mark parameters with the correct type, but this way I didn't obtain any difference.
In another case the problem was solved using the
LITERAL_PARAMETERS=true
jdbc driver parameter. I know that using this parameter almost all the advantages of using prepared statements are lost, but I would try this solution.
So, my question is: does anybody knows if it is possible to specify such type of parameter for the IBM DB2 Jdbc Universal Driver? Looking at the documentation, as well as the was administrative console custom property page of the corresponding websphere data source, the parameter LITERAL_PARAMETERS seems to not exists.
Any other observation about similar experiences will be welcome as well.
Thanks!

Sergio

When you use literals instead of parameter markers (prepared) it does give DB2 some additional information to optimize the query. However, before you abandon the prepared statement, I would make sure you run a proper runstats:

runstats on xxxxxxxxxxxxxxxxx with distribution on key columns and detailed indexes all

If that does not help, you could try and alter the table to volatile. If still having problems, then just submit that particular query with literals (execute immediate).

Marcus,
Thanks for your reply. Unfortunately runstats didn't help.
for several reasons I would like to not modify both application code and Db structure, so I would like to know if you are aware of something like LITERAL_PARAMETERS for the db2 jdbc driver; it should cause to operate the parameter substitution before sending the sql to the dbms, rather than send the parameters separately

That would probably require a change to a WAS config parameter and not jdbc.