Looking for guidelines setting ResultSet Type for db2

Rui Chen

Looking for guidelines setting ResultSet Type for db2

Hi folks,

We recently spotted something interesting and would like to see if someone had similar experience before, or any reference we could turn to.

When using ResultSetType = TYPE_SCROLL_INSENSITIVE vs. TYPE_FORWARD_ONLY (link more explanation on those two ResultSet attributes, used in prepareStatement(String sql, int resultSetType, int resultSetConcurrency) link), the same query picks up different access plans, leading to drastically different query time, 17 sec for TYPE_FORWARD_ONLY vs. 500ms for TYPE_SCROLL_INSENSITIVE, in our production-size db. All other jdbc attributes are kept the same. Query time difference becomes more obvious when we have larger filter range inside the query.

The access plan also looks quite different, at least visually, but i couldn't find much references to some of the components in the path (bunch of SYSIBM.TFSCROLL_LOOKUP, SCROLLDATATEMP, SCROLLDATATEMP, TFSCROLL_CORR). Also the STMTIDs are the same in two different access plan.

We could, but haven't tested the differences for more queries in our application. Question is, without digging much into db2 internals, nor exhaustively profiling all our queries response to different ResultSetType, could we get some guidelines on how to properly use ResultSetType?

Thanks for sharing your insights!

Edited By:
Rui Chen[Organization Members] @ Nov 16, 2017 - 02:35 PM (America/Eastern)

Rui Chen

RE: Looking for guidelines setting ResultSet Type for db2
(in response to Rui Chen)

Sample access plans generated from DEV env, with smaller data-size. But the point here is to show that different access plans were used, with ResultSetType being the only variable here.

In prod-size db, we noticed same behavior, meaning same query using different access plans, with ResultSetType being the only variable, which led to significantly different query time.

Attachments

  • Access Plan__TYPE_FORWARD_ONLY (3.5k)
  • Access Plan__TYPE_SCROLL_INSENSITIVE (2.5k)
Edited By:
Rui Chen[Organization Members] @ Nov 16, 2017 - 02:33 PM (America/Eastern)
Rui Chen[Organization Members] @ Nov 16, 2017 - 02:38 PM (America/Eastern)