update statistics after 9.4 to 10 migration
Question: after migrating a database from IDS 9.4 to IDS 10, is it enough to run "update statistics low drop distributions", or must update statistics be run twice? Answers: version 10 needs a completely new set of distributions, so you first drop the old ones (later 10.x versions support the fast "drop distributions only" form, which drops without gathering), then run a separate update statistics (low or high) to build new stats. Art Kagel added the usual HIGH/MEDIUM/LOW per-column-and-index routine plus re-running update statistics on SPL procedures to recompile them, and pointed to his dostats utility (utils2_ak, IIUG repository/Oninit) to automate it. The poster considered the question answered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone. I have read up on this but I am still a little unclear on what
needs to be done regarding update statistics after migration a database from
IDS 9.4 to IDS 10.
After I bring up the old 9.4 database in the new version 10 environment is it
necessary to do:
update statistics low drop distributions; followed by
update statistics low;
Or would simply doing
update statistics low drop distributions; suffice?
I know this sounds odd but I read where it is necessary to run the update
statistics 2 times as above. Is that true? If so why would it not suffice to
just run update statistics low drop distributions; ?
Also, if the database is relatively small is there any good reason NOT to run
update statistics high; ?
Thanks, Will
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
WILL LANDSTROM
Sent: Wednesday, February 04, 2009 3:06 PM
To: ids@iiug.org
Subject: update statistics after 9.4 to 10 migration [14750]
Hi everyone. I have read up on this but I am still a little unclear on
what
needs to be done regarding update statistics after migration a database
from
IDS 9.4 to IDS 10.
After I bring up the old 9.4 database in the new version 10 environment
is it
necessary to do:
update statistics low drop distributions; followed by
update statistics low;
Or would simply doing
update statistics low drop distributions; suffice?
<<< snipped >>>
Will,
Version 10 needs a brand new set of distributions when you first
migrate. You don't mention which exact version of 10 you are migrating
to, but later "flavors" of 10 have the "update statistics low drop
distributions only", where the "only" keyword means that the statement
*just* drops the current set of distributions, without actually
gathering any new stats -- this runs very fast! Then you could run the
regular "update stats low" to gather the new version 10 stats and
distributions. BTW, I see no reason that you could not just run an
"update stats high" for the whole thing, instead of the "update stats
low", if you have the resources and the time. However, even in this
case, you do still need to run the "update stats low drop distributions
[only]" the very first time after migration.
HTH,
Paul M.
Paul. This answered my question perfectly. Thanks! Will
Paul. This answered my question perfectly. Thanks! Will
You have to drop the existing data distributions separately from any
commands to recreate the stats (hence the LOW DROP DISTRIBUTIONS and Paul's
suggestion to add the ONLY clause). Then you have to go through the normal
suite of HIGH ... DISTRIBUTIONS ONLY on the initial columns of each index
and MEDIUM on all other columns and LOW on each full index key. Also you
should run UPDATE STATISTICS on all of your SPL procedures and functions to
recompile them, or as some have suggested, drop and recreate them all.
You can just get and use my dostats utility which performs all of the needed
operations automatically. Dostats is included in the package utils2_ak in
the IIUG Software Repository (www.iiug.org/software) or from the Oninit web
site (www.oninit.com/utils).
Art
On Wed, Feb 4, 2009 at 5:06 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote:
> Hi everyone. I have read up on this but I am still a little unclear on what
> needs to be done regarding update statistics after migration a database
> from
> IDS 9.4 to IDS 10.
>
> After I bring up the old 9.4 database in the new version 10 environment is
> it
> necessary to do:
>
> update statistics low drop distributions; followed by>
> update statistics low;>
> Or would simply doing
>
> update statistics low drop distributions; suffice?>
> I know this sounds odd but I read where it is necessary to run the update
> statistics 2 times as above. Is that true? If so why would it not suffice
> to
> just run update statistics low drop distributions; ?
>
> Also, if the database is relatively small is there any good reason NOT to
> run
> update statistics high; ?>
> Thanks, Will
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--00163646d07e1f72d704622143b7
Hi Art. I will definitely check out your utilities. Thanks a lot.