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?

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.

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.

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).

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).

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.