2 replies Latest Post - ‏2012-01-17T10:47:31Z by jguillaumes
1 Post

Pinned topic Prepared statement not using indexes

‏2011-10-21T12:50:39Z |
I have a complex query that accesses very big tables in a db2 64bit v. 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 very good from my point of view.

The problem arises when 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 environment 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 experience will be welcome as well.

Updated on 2012-01-17T10:47:31Z at 2012-01-17T10:47:31Z by jguillaumes
  • SystemAdmin
    1731 Posts

    Re: Prepared statement not using indexes

    ‏2012-01-17T03:01:51Z  in response to SIdwscreen
    This is a problem that is usually caused by the default filter factors not being good enough
    have you tried adding in the end of the query - optimize for 10 rows?
    that should tell the optimizer to favor index access
    it might solve your problem

    the other solution would be to put this statement in a separate program (so it will use it's own package) and have that package bound with reopt vars
  • jguillaumes
    8 Posts

    Re: Prepared statement not using indexes

    ‏2012-01-17T10:47:31Z  in response to SIdwscreen
    Hi Sergio.

    I understand you are using dynamic SQL (as being used from a JEE application), so the "different packages" solution won't work for you...

    The problem you described is very familiar to me, even I use a very different environment (precompiled SQL in a PLI program running on zOS). In our case it happens when that "column between ? and ?" predicate tricks the DB2 optimize to use a certain index which is not relevant for the actual query with the actual values for the parameter markers. It usually happens when you are processing a query made from user-entered parameters with optional filter fields; in this case, if the filter for "column" has not been entered, it translates in a "column between zero and infinite" search, and if the optimizer has selected the index based on "column" it translates to a sequential scan over all the index (or, in the worst page, all the tablespace).

    The solution is to break the query in two depending on the presence of that certain filter. If that filter is not present, use a query WITHOUT that predicate, otherwise use it as is. That way the DB2 optimize will not be fooled to select a suboptimal strategy.

    Of course, being SQL dynamic you could opt for building the whole query dynamically, specifying just the predicates relevant to the data your user has filled in the form. In that way the optimizer will "nail" the query using the best strategy every time. The disavantage in this case is that if you plan to use pureQuery in the future you will have a hard work building a test set for every posible generated query. Your mileage may vary...

    I hope this helps.