Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Prepared statement not using indexes

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by simper View Post
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •