|
The Buffer Pool
Change
Control for DB2 Access Paths
By Craig S. Mullins
An
important aspect of efficient DB2 operation is the on-going analysis and
management of DB2 access path changes. Whether you are implementing changes
into your DB2 applications, upgrading to a new version of DB2, or simply
trying to achieve optimum performance for existing application plan and
packages, an exhaustive and thorough BIND management process is a necessity.
However,
many organizations are not doing everything possible to keep access paths
up-to-date with the current state of their data. There are several reasons
why the acknowledged “best practice” of REOG/RUNSTATS/REBIND is not followed
religiously. In this month’s column I will examine some of the issues
involved in managing access path changes. Furthermore, we’ll look at some
methods for introducing a better change control discipline for the DB2 access
path generation process.
Some
History
On
the mainframe, change has traditionally been strictly controlled. But one
exception has been DB2 access paths.
Think
about it. In a mainframe shop everything we do is tightly controlled. If we
make even a minor change to an application program, that program is
thoroughly tested before it ever reaches a production environment. The
program progresses through unit testing, QA testing,
volume testing, and so on. As developers, we do a good job of testing a
change to minimize the risk that the change might have unintended
consequences. We do the same type of due diligence with most other changes in
the mainframe world. Database changes are planned and thoroughly tested.
System software (e.g. CICS, WebSphere, etc.), including subsystem and DB2
changes, are all subject to strict change control procedures. This is done to
minimize disruption to the production work being conducted by our business
folks.
But
there is one exception to this tight change control environment: Binds and
Rebinds are typically done in the production environments without the benefit
of oversight or prior testing. This lack of change control results in
unpredictable performance impacts. In most shops, programs are moved to
production and bound there. Indeed, we are at the mercy of the DB2 optimizer,
which generates access paths on the fly when we Bind or Rebind our programs.
Any issues with inefficient access paths are then dealt with in a reactive
mode. That is, problems are addressed after the fact.
One
of the biggest reasons for not implementing strict change control processes
for access paths is the lack of built-in methods for ensuring access path
change control discipline. Let’s face it, manually evaluating thousands of
packages and tens of thousands of SQL statements can be quite impractical.
But there are things that can be done to help alleviate this problem. This
article will address some of those things.
BIND
Parameters
There
are many parameters and values that must be chosen from and specified when
you bind a DB2 application program. The vast array of options at our disposal
can render the whole process extremely confusing – especially if you don’t
bind on a daily basis. And even if you do, some of the options still might be
confusing if you rarely have to change them. You know what I’m talking about,
parameters like ACQUIRE, RELEASE, VALIDATE, and DEGREE.
It
is not the intent of this article to delve into the myriad bind options and
give you advice on which to use when. There are many articles and books, as
well as the IBM DB2 manuals that you can use to guide you along that path.
Suffice it to say, that there are some standard parameters and values that
should be chosen “most of the time” in certain situations. As such, a wise
DBA group will set up canned routines for the programmers to use for
compiling and binding their applications. Choices such as: “CICS
transaction”, “DB2 batch”, or “analytical query” can be presented to the
developer and then, based on which of the various types of programs and environments
that are available, the canned script can choose the proper bind options.
Doing so can greatly diminish the problems that can be encountered when the
“wrong” parameters or values are chosen at bind time.
This
same process can be put in place for production binding to ensure that the
appropriate parameters and values are chosen. This is especially useful when
the binds are not done by a DBA, but are automated in production or done by a
less-experienced change control clerk.
Of
course, there should always be a method for over-riding the “standard” values
for special situations, although these overrides should not be available to
anyone other than a well-trained individual (DBA or otherwise).
I
want to make one small exception here regarding advice on bind parameters,
and that is the EXPLAIN parameter. In production, always bind your plans and
packages specifying EXPLAIN YES. Failing to do so means that access paths
will be generated, but you will not know what they are. This is akin to
blinding yourself to what DB2 is doing and is not advisable.
Approaches
to Access Path Management
OK,
so we know that Bind and Rebind are important components in assuring optimal
application performance. It is the bind process that determines exactly how
your DB2 data is accessed in your application programs. As such, it is
critically important that you develop an appropriate strategy for when and
how to Rebind your programs.
There
are several common approaches taken by DB2 users. By far, the best approach
is to Rebind your applications over time as the
data changes. This approach involves some form of regular maintenance that
keeps DB2 statistics up to date and formulates new access paths as data
volumes and patterns change. More on this in a moment.
Other
approaches include binding only when a new version of DB2 is installed, or
perhaps more ambitious, whenever new PTFs are applied to DB2. Another
approach is to rebind automatically after a regular period of time, whether
it is days, weeks, months, or whatever period of time you deem significant.
This approach can work if the period of time is wisely chosen based on the
application data – but it still can pose significant administrative issues.
The
final approach is from the “if it ain’t broke don’t
fix it” school of thought. This approach is the worst of the several
approaches discussed here. The biggest problem with this approach is that you
are penalizing EVERY program in your subsystem for fear that a program or two
may have a few degraded access paths. This results in potentially many
programs having sub-optimal performance because the optimizer never gets a
chance to create better access paths as the data changes.
Of
course, the possibility of degraded performance is real – and that is why
this approach has been adopted at some sites. The problem is being able to
find which statements may be worse. The ideal situation would be to be able
to review the access path changes before hand to determine if they are better
or worse. But DB2 itself does not provide any systematic method of
administering access paths that way. There are third party tools that can
help you achieve this though.
Anyway,
let’s go back to the best approach again, and that is to perform regular
Rebinds as your data changes. This involves what has become known as the
three Rs. This means regularly reorganizing the
data to ensure that it is optimally structured. That is followed by RUNSTATS
to be sure that the reorganized state of the data is reflected in the DB2
Catalog. Finally, we follow that up with Rebinds of the application programs
that access the data structures that have been reorganized and RUNSTATed (if you’ll allow me to turn that into a verb).
At
any rate, your goal should be to keep your access paths up-to-date with the
current state of your data. Failing to do this means that DB2 is accessing
data based upon false assumptions. DB2 is unlikely to make the same access
path choice as your data grows – and as patterns within the data change.
By
Rebinding you can generally improve the overall performance of your
applications because the access paths will be better designed based on an
accurate view of the data. Additionally, as DB2 changes are made (via new
releases or PTFs) optimizer improvements and new access techniques can be
incorporated into the access paths. That is, if you never Rebind, not only
are you forgoing better access paths due to data changes but you are also
forgoing better access paths due to changes to DB2 itself.
Of
course, adopting the Three R’s approach can pose additional questions. For
example, when should you reorganize? In order to properly determine when a
REORG is needed you’ll have to look at statistics. This means looking at
either RUNSTATS or Real-Time Statistics (RTS). So, perhaps it should be at
least 4 R’s – in other words:
- RUNSTATS or RTS
- REORG
- RUNSTATS
- REBIND
Now
it is true that some folks don’t rely on statistics to schedule a REORG.
Instead, they just build the JCL to REORG their database objects when they
create the object. So they create a table space then build the REORG job and
schedule it to run monthly, or quarterly, or on some regular basis. This is
better than no REORG at all, but it is probably not the best approach because
you are most likely either reorganizing too soon (in which case you waste the
CPU cycles to do the REORG) or you are reorganizing too late (in which case
performance is suffering for a period of time before the REORG runs). Better
to base your REORGs off of statistics and thresholds using either RUNSTATS or
RTS.
Statistics
are the fuel that makes the optimizer function properly. Without accurate
statistics there is little hope that the optimizer will formulate the best
access path to retrieve your data. If the optimizer doesn’t have accurate information
on the size, organization, and particulars of your data then it will be
creating access paths based on either default or inaccurate statistics.
Incorrect statistics will probably cause bad choices to be made – such as
choosing a merge-scan join when a nested loop join would be better, or
failure to invoke sequential prefetch, or using the
wrong index – or no index at all. And the problem of inaccurate statistics is
pervasive. There are shops out there that never, or rarely, run RUNSTATS to
gather up-to-date statistics. Make sure yours is not one of those shops!
OK,
then when should you run RUNSTATS? One answer is "As frequently as
possible based on how often your data changes.” This means that you will need
to know a thing or two about your data growth patterns. To properly determine
a schedule for statistics you need to know things about your data: what is
its make-up, how is it used, how fast does it grow,
and how often does it change? These patterns will differ for every table
space in your system.
Next
we need to decide when to Rebind? The best answer for this is when statistics
have changed significantly enough to change access paths. When we know that
data has significantly changed it makes sense to Rebind after the RUNSTATS
completes. But the trick is determining exactly when we have a “significant”
change in our data. Without an automated method of comparing and contrasting
statistics (or even better yet, access paths) coming up with an answer in a
manual way can be time-consuming and error-prone – especially when we get
into the thousands of programs.
And
we always have to be alert for a rogue access path – that is, when the
optimizer formulates a new access path that performs worse than the previous
access path. This can happen for a variety of reasons. Of course, number one
is that the optimizer, though good, is not perfect. So mistakes can happen.
Other factors can cause degraded access paths, too. The access paths for
volatile tables depend on when you run the RUNSTATS. Volatile tables are
those that start out empty, get rows added to them during processing, and are
emptied out at the end of the day. And, of course, if the catalog or
statistics are not accurate we can get problems, too.
So
adopting the Three, err, I mean, Four R’s approach implies that you will have
to develop a methodology for reviewing your access paths and taking care of
any “potential” problem access paths. Tackling this can be a difficult
mountain to climb.
Indeed,
the Four R’s probably needs to become the Five R’s because we need to review
the access paths after rebinding to make sure that there are no rogue access
paths. So, we start off with a RUNSTATS (or use RTS) to determine when to
REORG. After reorganizing we should run RUNSTATS again, followed by a REBIND.
Then we need that fifth R – which is to review the access paths generated by
the REBIND. As we mentioned, the optimizer can make mistakes. And, of course,
so can you. Users don't call you when performance is better (or the same).
But if performance gets worse, you can bet on getting a call from irate
users.
So
we need to put in place best practices whereby we test Bind results to
compare the before and after impact of the optimizer’s choices.
The
Plan Tables
A
lot of information is contained in the PLAN_TABLE. (Actually, in the
multiple PLAN_TABLEs.) After the optimizer creates the access paths and
populates the PLAN_TABLE with data representing those access paths, we need
to examine the results to determine if everything is OK.
Many
questions can be answered by analyzing the results of EXPLAIN – questions
like:
- if we are joining what
type of join is used (NLJ, MS, Hybrid),
- was an index used, and
if so how many columns matched,
- are we doing a scan,
and if so what type of scan (full or page range)
- is prefetch
being used, and if so what type (sequential, list)
- was a hint used
- was parallelism used,
and if so what degree and type (I/O, CPU, Sysplex)
- was a sort required,
and if so why (Join, Unique, Group By, Order By)
- what type of locking
is required
And that just covers the main PLAN_TABLE. The EXPLAIN
option also populates two optional tables, if they exist:
- DSN_STATEMNT_TABLE
which contains DB2’s estimate of the processing cost for an SQL
statement
- DSN_FUNCTION_TABLE
which contains information about function resolution
And,
with DB2 V8 there are even more PLAN_TABLEs that are available when you are
using Visual Explain.
Of
course, for any of this information to be returned you have to have bound
specifying EXPLAIN(YES). Any change to any of these
items between Rebinds means a change in access path – which can be positive,
or a potential problem. Over time, performance analysts can determine which
changes are good and which might be problematic – but it takes experience
(and perhaps some luck) to do this correctly. Using a tool that automates the
process can also make the task much easier and more accurate.
So,
how do you determine what access paths have changed? Sometimes the program
has changed, too – which can make it challenging to find the exact SQL
statements to compare. When just the access paths change it will be easier to
compare them and spot the changes, but there is still a wealth of data that
needs to be analyzed to do this justice.
And
when you are talking about thousands of programs being rebound, do you really
have the time to review every access path to make sure it is fine? This
question alone causes many folks to go back to the “Let It Ride” mentality –
which is too bad, because it is an inferior approach, especially when there
are products that can help.
Version
Migration Issues
OK,
let’s switch gears and talk about an impending event that many of us are
still facing, namely migrating from DB2 V7 to V8. First of all, let’s be
clear, you do not have to Rebind all of your packages and plans
when you move to V8. But it is a really good idea to do so, and
most of you will probably Rebind most, if not all, of your programs when you
get to V8. Why?
First
of all, there are optimizer and performance improvements that you won’t get
without a Rebind. And there will be degraded program performance that will
occur when you get to V8 that Rebind can fix. And for some of you, there will
even be REBINDs that you just will not be able to avoid. Let’s examine each
of these issues briefly.
First
of all, what is the “degraded performance” issue? The problem occurs
when DB2 turns off fast column processing. DB2 V3 introduced a function
called an SPROC. An SPROC, or SELECT procedure, enables fast column
processing. Essentially, this enhancement examines SELECT statements that are
executed repeatedly and builds an internal procedure that moves all the
columns in one move rather than one column at a time. You have no external
control over when or if DB2 uses them. And the more columns that are
specified on a SELECT, the greater the performance gain could be.
How
does this all tie into Version 8? If a plan or package is using an SPROC in
V7, the SPROC is using 31 bit code. When you attempt to run that same plan or
package in V8 without rebinding it first, it needs to be in 64 bit. It isn't,
so DB2 disables the procedure. The only way you can re-enable the SELECT
procedure is by rebinding the program. Until you do that rebind, and if the
plan or package uses an SPROC, your application's performance will be
degraded. Do the rebind, and you should see a performance improvement. Along
those lines, the IBM redbook titled “DB2 UDB for
z/OS Version 8 Performance Topics” specifically warns of this problem, cites
the potential for CPU increases of up to 10% and recommends global rebinds.
And
what about those Rebinds that cannot be avoided. Well, DB2 V8 will autobind any plans and
packages that were bound prior to DB2 Version 2 Release 3. So you might
experience an execution delay the first time such plans are loaded unless you
rebind them yourself. And DB2 might change the
access path due to the autobind, potentially
resulting in a more efficient access path – or a more inefficient access
path.
Such
actions might become more common in future DB2 versions. In several
conference presentations, folks at IBM have suggested that in the future DB2
may autobind any plan or package that was last
bound on an “out of service” version of DB2. What might that mean for DB2 V9?
Right now, only V7 and V8 are in service, so think about that when you are
considering your rebind approach.
And
there are still more reasons to Rebind when moving to V8. DB2 V8 in NFM uses
a different format for its DBDs, packages and plans. Before it can use a DBD,
plan or package from an older DB2, it must first be expanded to the new
Version 8 format. This causes more overhead. What should you do? Here is the
advice right out of the afore-mentioned redbook:
After you have entered new-function mode, we recommend
that you plan to rebind all of your plans and packages. DB2 will then store
the plans and packages in the DB2 catalog in the new format. DB2 will no
longer need to expand the plans/packages each time it needs to use them.
Summary
Forward-thinking
organizations should adopt a liberal Bind / Rebind process to ensure optimal
access paths based on up to date statistics. Keeping abreast of data changes
and making sure that your programs are optimized for the current state of the
data is the best approach. This means regular executions of RUNSTATS, REORG,
and Rebind. If you are worried about rogue access paths, consider investing
in a third party tool that can assist with access path changes management
issues.
Failing
to keep your access paths aligned with your data is a sure recipe for
declining DB2 application performance.
From IDUG Solutions Journal, October 2006.
© 2006 Craig S. Mullins,
All rights reserved.
Home.
|
