Questions on IDS 11.50 for Database consistency

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 ? 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 ? 3. Update Statistics is running ONLY for tables. Should we run it for procedures, functions and routines as well ? 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 ? 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 ? 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)

Hello. As far as I know, oncheck was regularly needed only before version 11, so it´s not your case anymore. Of course you could schedule, let´s say, once a month, a full oncheck, in order to make sure everything is going fine, just as expected. About update statistics, you didn´t mention your relase (11.50 .....?) But I can send you some official recommendations: https://www-304.ibm.com/support/docview.wss?uid=swg21137764 If you want to do it in automatic way, you could: a. enable AUS - have you tryed OAT web administration? If not, you should do it soon... it´s great. b. use Mr. Art Kagel dostats utility, packaged in utils2_ak (available in our sw repository). Obs: there is no problem running update statistics with your engine online, don´t worry. Hope this helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator BRIUG website administrator > To: ids@iiug.org > From: AchilleasAchilleos@semltd.com.cy > Subject: Questions on IDS 11.50 for Database consistency [27085] > Date: Wed, 9 May 2012 00:51:56 -0400 > > 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 ? > > 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 ? > > 3. Update Statistics is running ONLY for tables. Should we run it > for procedures, functions and routines as well ? > > 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 ? > > 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 ? > > 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. >

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