IDS Forum

Re: Questions on IDS 11.50 for Database consistenc

Posted By: Art Kagel
Date: Wednesday, 9 May 2012, at 10:10 a.m.

In Response To: Questions on IDS 11.50 for Database consistency (Achilleas Achilleos)

See answers below:

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Wed, May 9, 2012 at 12:51 AM, Achilleas Achilleos <
AchilleasAchilleos@semltd.com.cy> wrote:

> Hello ,
>
> I have a couple of questions concerning "oncheck" and Update statistics .
>
> As far as database consistency and integrity checks are concerned, we
> perform :
>
> .. Onchecks on a weekly basis (oncheck -ccIxD -y <dbname> PLUS
> oncheck -cR and oncheck -ce)
>
> .. Update Statistics For Tables, also on a weekly basis (taking all
> considerations for dropping distributions,LOW,MEDIUM,HIGH).
>
> The questions are:
>
> 1. OnCheck runs while the instance is on-line. Any corruption found,
> it is fixed in this mode "oncheck -ccIxD -y <dbname >" or the instance must
> be in quiescent mode ?
>

You can runt this with the engine online.

>
> 2. The parameters given in 'oncheck' (-ccIxD -y ) are sufficient ?
> Now, they cover system catalog tables , index and data pages, reserved
> pages
> and extents. Is there anything else to consider ?
>

Sufficient yes. I tend not to include the -y to automatically fix things
simply because the index rebuilds that oncheck does are single threaded
where a manual drop and recreate can be done using parallel scanning and
sorting which is much faster also you can do the manual rebuild with the
ONLINE option so users are not affected (except by the lack of an index for
a bit).

>
> 3. Update Statistics is running ONLY for tables. Should we run it
> for procedures, functions and routines as well ?
>

Yes. The update stats on the tables will cause the procedure/function
query plans to become invalid causing the procs to recompile themselves the
first time they are used. However, if there are multiple concurrent users
several will start a compile at the same time of the same proc resulting in
lockout errors on the sysprocplan table (contains the compiled query
plans). Better to recompile them yourself as part of the update stats run
by updating stats on the procs.

>
> 4. If the answer in Q3 is yes, is it ok if we don't specify any
> procedure/function/routine names (that is, execute UPDATE STATISTICS FOR
> PROCEDURE; UPDATE STATISTICS FOR FUNCTION; UPDATE STATISTICS FOR ROUTINE;)
> ?
> In this way ALL procedures/functions/routines will be checked (both
> application and internal). Does it "harm" internal Informix procedures ?
>

That's fine. A single 'update statistics for procedure;' is sufficient.

>
> 5. Will there be a problem if we run UPDATE STATISTICS on the tables
> of a database while somebody is working on that same database ?
>

The only problem will be if some application has a prepared statement that
was prepared before the update stats run may encounter a -710 error (rare
in 11.50 but still possible). For the most part, most users will not know
the difference.

>
> Thanks
>
> Description: Description: CoopLogo1Description: Description: CoopLogo1
>
> Cooperative Computer Society (S.E.M) Ltd
>
> 1306 Nicosia
>
> P.O.B. 25037 CY
>
> Tel: +357 22 673 901
>
> Fax: +357 22 672 774
>
> Achilleas Achilleos
>
> Official A
>
> OS and Databases Management
>
> <mailto:AchilleasAchilleos@semltd.com.cy> AchilleasAchilleos@semltd.com.cy
>
> --Boundary_(ID_iVx6MPzEc9A4QmD03J3THg)
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--e89a8fb208361481dc04bf9b11b1

Messages In This Thread

• Questions on IDS 11.50 for Database consistency
  Achilleas Achilleos -- Wednesday, 9 May 2012, at 12:51 a.m.
  • RE: Questions on IDS 11.50 for Database consistenc
    Alexandre Marini -- Wednesday, 9 May 2012, at 8:03 a.m.
  • Re: Questions on IDS 11.50 for Database consistenc
    Art Kagel -- Wednesday, 9 May 2012, at 10:10 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.