Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: db2 access paths different with different tools

    Does anyone have any ideas as to why the same sql statement would take different paths depending on the tool? (DB2 ZOS v9 Conversion mode)

    Example, we have a query, we can run it in SPUFI, DB2 Connect, Toad, Foxy SQL, DB2 Data Studio, and with all of these it uses one access path which happens to be the most Efficient.

    If we run that same query within the application, Java with Websphere, it chooses a different path.

    Any thoughts?

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Muhahahaha! Websfear ...

    DB2 Data Studio uses Java aswell. Dont know the others.
    If the Application in Websphere uses a Prepared Statement (select tabschema, tabname from syscat.tables where tbspace=?), then this statement might be cached and using an older Access plan.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Websphere Application Server has a lot of options (properties) that can effect blocking, isolation level, etc, that could affect the access path of the statement. Some of the defaults used by WAS are different than what most other applications/tools would use.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    settings

    Any settings that you know of that would influence a different access path, and also cause it to fetch 27 times versus one fetch. The query we are using does do fetch first 26 rows, but it seems to be doing them one at a time when you go thru websphere. All other tools, get it using one fetch. (same query).

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check to see if the WAS application uses blocking or not blocking cursor (that should show up in the explain plan). Java programmers often use statements with unnecessary parameters that may prevent blocking (e.g. scrollable or updatable cursors).

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ltaylor View Post
    Any settings that you know of that would influence a different access path, and also cause it to fetch 27 times versus one fetch. The query we are using does do fetch first 26 rows, but it seems to be doing them one at a time when you go thru websphere. All other tools, get it using one fetch. (same query).
    As I mentioned above, WAS has properties that will affect blocking, and hence number of rows fetched at one time from the server to the client. I don't know what they are, but I know they exist in WAS.
    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
  •