SEO

IDS 9.21.FC4
Solaris 8
24 x 7

We have a contractor provided Informix application. From time to time, we
get updates from the contractor. Of course, these updates typically include
table structure changes and stored procedure changes. We are aware of only
two choices:


1) permit users to remain connected while running UPDATE STATISTICS on
particular objects, which are identified from our error logs as users
encounter 710 errors (usually this quickly degenerates from targeting
specific objects, to just giving up and running UPDATE STATISTICS on the
whole database, while users are connected, which further inconveniences
users with 211 [sysprocplan] errors);

2) kick all users out first, then run UPDATE STATISTICS on the whole
database.


The problem is either method entails downtime for users. We typically play
this game every quarter, sometimes more.

The question is: how do other 24 x 7 shops maintain user access while making
structural database changes to their application?

Thank you.

DG



--
David Grove

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"I think not," said Descartes, and disappeared.

The contractor should provide a least of changes and dependent
entities. From that you can script the relevant update stats and
they should be very little effect on the users. If that is not
possible run update stats on all the procedures after the changes
have been made, that should remove the majority of the 710s

"David E. Grove" wrote:
>
> IDS 9.21.FC4
> Solaris 8
> 24 x 7
>
> We have a contractor provided Informix application. From time to time, we
> get updates from the contractor. Of course, these updates typically include
> table structure changes and stored procedure changes. We are aware of only
> two choices:
>
> 1) permit users to remain connected while running UPDATE STATISTICS on
> particular objects, which are identified from our error logs as users
> encounter 710 errors (usually this quickly degenerates from targeting
> specific objects, to just giving up and running UPDATE STATISTICS on the
> whole database, while users are connected, which further inconveniences
> users with 211 [sysprocplan] errors);
>
> 2) kick all users out first, then run UPDATE STATISTICS on the whole
> database.
>
> The problem is either method entails downtime for users. We typically play
> this game every quarter, sometimes more.
>
> The question is: how do other 24 x 7 shops maintain user access while making
> structural database changes to their application?
>
> Thank you.
>
> DG
>
> --
> David Grove
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> "I think not," said Descartes, and disappeared.

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

Thank you for your comments, Paul.

We try to do something like what you suggest. I'm not sure if we get an
explicit list of dependencies, or if we interogate the syscatalog tables to
derive a dependencies list, but we ultimately have a dependency list. Then
we run the UPDATE STATISTICS for the appropriate objects, but we still wind
up with 710s. I'm thinking it may be because we don't get the order
correct. Or maybe because users are in the app already when the UPDATE
STATISTICS executes. If we do not run targeted UPDATE STATISTICS, but just
run UPDATE STATISTICS on the whole enchilada after all the changes are made,
then the users get 211 errors. Either way-- problems for users. And, if we
just kick them all off, well, then, by definition, they can't use the app..

Hence, I wonder how other 24 x 7 shops change stored procedures and table
structure, while still being 24 x 7.

Doesn't it all eventually boil down to having to run UPDATE STATISTICS?

And if that is so, it seems that we have to either kick users out to run it,
or accept that, if users are in while UPDATE STATISTICS is running, they
will encounter errors (710s or 211s) and be unable to use certain portions
of the app until UPDATE STATISTICS completes.


DG



"Paul Watson" <paul@oninit.com> wrote in message
news:3F85D215.82D83CFC@oninit.com...
> The contractor should provide a least of changes and dependent
> entities. From that you can script the relevant update stats and
> they should be very little effect on the users. If that is not
> possible run update stats on all the procedures after the changes
> have been made, that should remove the majority of the 710s
>
> "David E. Grove" wrote:
> >
> > IDS 9.21.FC4
> > Solaris 8
> > 24 x 7
> >
> > We have a contractor provided Informix application. From time to time,
we
> > get updates from the contractor. Of course, these updates typically
include
> > table structure changes and stored procedure changes. We are aware of
only
> > two choices:
> >
> > 1) permit users to remain connected while running UPDATE STATISTICS on
> > particular objects, which are identified from our error logs as users
> > encounter 710 errors (usually this quickly degenerates from targeting
> > specific objects, to just giving up and running UPDATE STATISTICS on the
> > whole database, while users are connected, which further inconveniences
> > users with 211 [sysprocplan] errors);
> >
> > 2) kick all users out first, then run UPDATE STATISTICS on the whole
> > database.
> >
> > The problem is either method entails downtime for users. We typically
play
> > this game every quarter, sometimes more.
> >
> > The question is: how do other 24 x 7 shops maintain user access while
making
> > structural database changes to their application?
> >
> > Thank you.
> >
> > DG
> >
> > --
> > David Grove
> >
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > "I think not," said Descartes, and disappeared.
>
> --
> Paul Watson #
> Oninit Ltd # Growing old is mandatory
> Tel: +44 1436 672201 # Growing up is optional
> Fax: +44 1436 678693 #
> Mob: +44 7818 003457 #
> www.oninit.com #

If you are doing table structure changes it's very unlikely it
will not effect a live system, so maybe you just to schedule this in.
But if you change a table and run update stats on the relevant SPL
and no user accesses the table during the process then you shouldn't
get 710s

"David E. Grove" wrote:
>
> Thank you for your comments, Paul.
>
> We try to do something like what you suggest. I'm not sure if we get an
> explicit list of dependencies, or if we interogate the syscatalog tables to
> derive a dependencies list, but we ultimately have a dependency list. Then
> we run the UPDATE STATISTICS for the appropriate objects, but we still wind
> up with 710s. I'm thinking it may be because we don't get the order
> correct. Or maybe because users are in the app already when the UPDATE
> STATISTICS executes. If we do not run targeted UPDATE STATISTICS, but just
> run UPDATE STATISTICS on the whole enchilada after all the changes are made,
> then the users get 211 errors. Either way-- problems for users. And, if we
> just kick them all off, well, then, by definition, they can't use the app..
>
> Hence, I wonder how other 24 x 7 shops change stored procedures and table
> structure, while still being 24 x 7.
>
> Doesn't it all eventually boil down to having to run UPDATE STATISTICS?
>
> And if that is so, it seems that we have to either kick users out to run it,
> or accept that, if users are in while UPDATE STATISTICS is running, they
> will encounter errors (710s or 211s) and be unable to use certain portions
> of the app until UPDATE STATISTICS completes.
>
> DG
>
> "Paul Watson" <paul@oninit.com> wrote in message
> news:3F85D215.82D83CFC@oninit.com...
> > The contractor should provide a least of changes and dependent
> > entities. From that you can script the relevant update stats and
> > they should be very little effect on the users. If that is not
> > possible run update stats on all the procedures after the changes
> > have been made, that should remove the majority of the 710s
> >
> > "David E. Grove" wrote:
> > >
> > > IDS 9.21.FC4
> > > Solaris 8
> > > 24 x 7
> > >
> > > We have a contractor provided Informix application. From time to time,
> we
> > > get updates from the contractor. Of course, these updates typically
> include
> > > table structure changes and stored procedure changes. We are aware of
> only
> > > two choices:
> > >
> > > 1) permit users to remain connected while running UPDATE STATISTICS on
> > > particular objects, which are identified from our error logs as users
> > > encounter 710 errors (usually this quickly degenerates from targeting
> > > specific objects, to just giving up and running UPDATE STATISTICS on the
> > > whole database, while users are connected, which further inconveniences
> > > users with 211 [sysprocplan] errors);
> > >
> > > 2) kick all users out first, then run UPDATE STATISTICS on the whole
> > > database.
> > >
> > > The problem is either method entails downtime for users. We typically
> play
> > > this game every quarter, sometimes more.
> > >
> > > The question is: how do other 24 x 7 shops maintain user access while
> making
> > > structural database changes to their application?
> > >
> > > Thank you.
> > >
> > > DG
> > >
> > > --
> > > David Grove
> > >
> >
> > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > > "I think not," said Descartes, and disappeared.
> >
> > --
> > Paul Watson #
> > Oninit Ltd # Growing old is mandatory
> > Tel: +44 1436 672201 # Growing up is optional
> > Fax: +44 1436 678693 #
> > Mob: +44 7818 003457 #
> > www.oninit.com #

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #