There seems to be a LOT of confusion out there in DB2-land
about the CURRENTDATA parameter, so I thought it might be helpful to blog about
the subject.
So first things first: CURRENTDATA is a BIND parameter that
can be specified when you bind or rebind your plans and packages. There are two
options that can be specified: YES and NO. The default is NO.
But what does it do?
Well, the manuals tell us that CURRENTDATA indicates whether
data currency is required for read-only and ambiguous cursors when the
ISOLATION(CS) option is used. But that is not very clear... except that
CURRENTDATA really only applies with an isolation level of cursor stability.
The general idea here is to enable the developer to exert a
level of control by choosing between high performance or up-to-date data. Sure,
in a perfect world, everybody would choose both. But in the real world we deal
with tradeoffs and the CURRENTDATA parameter allows us to tell DB2 which of
these options to prefer.
CURRENTDATA (NO)
The default, CURRENTDATA(NO), is the performance choice, and
probably the best choice for most applications. When NO is chosen, you are
telling DB2 that completely up-to-date data currency is not required for
read-only and ambiguous cursors. That means that DB2 can perform block fetching
for distributed, ambiguous cursors.
And that is probably what you want. However, if you are not
using pristine coding practices, and your program tries attempts to DELETE WHERE
CURRENT OF against an ambiguous cursor, DB2 will return a negative SQLCODE.
What is an Ambiguous Cursor?
At this point you might be asking “What is an ambiguous
cursor?” Generally speaking, an ambiguous cursor is one where DB2 cannot tell if your program
needs to use the cursor for updating or deleting. You can avoid ambiguous
cursors by coding the FOR READ ONLY and FOR UPDATE OF clause on your cursors to
inform DB2 of the program’s future intent for the data being accessed.
What about
CURRENTDATA(YES)? So far, we have discussed only the NO option, but you can
also specify YES. Doing so indicates that the data should be current for
read-only and ambiguous cursors. That means DB2 will acquire page (or row)
locks to ensure the currency of the data and block fetching for distributed,
ambiguous cursors will be curtailed.
Summary
Hopefully this short blog post helps to clear up a muddy
topic. Of course, there is a lot more to DB2 locking and performance and I do
not want to make it seem like this is the only thing you need to know in terms
of the tradeoffs between data currency and performance.
But at least one parameter might be a little bit clearer to
you today than it was yesterday…
No comments:
Post a Comment