oncheck and locks

We need 24x7 availability and oncheck is locking tables up for too long. Can we : 1. Switch off the oncheck table locking ? 2. Run oncheck on a secondary replicating database ? 3. Run oncheck on the primary repliacting database and switch off replication for the duration, using the secondary as primary and then reverting back when oncheck is completed ? 4. Use any other method to circumvent the locking ? thanks Steve. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

evetsm@rocketmail.com wrote: > > We need 24x7 availability and oncheck is locking tables up for too > long. Can we : > > 1. Switch off the oncheck table locking ? > 2. Run oncheck on a secondary replicating database ? > 3. Run oncheck on the primary repliacting database and switch off > replication for the duration, using the secondary as primary and then > reverting back when oncheck is completed ? > 4. Use any other method to circumvent the locking ? I know it sounds like heresy, but I only run oncheck when I have detected a problem that I cannot isolate any other way and I cannot afford to just drop and rebuild ALL the indexes. Losing a production system for the four hours it takes to rebuild an index (or sometimes even 16 hours for all four) was better than running oncheck -cD for 6 hours followed by oncheck -cI for another 32 hours (over 8 hours per index) on a large (160.6 Million rows) table spanning 38 2GB chunks (27 data, 11 index). Art S. Kagel

I also wait until problems show up in the Index's or the Update Statistics shows that and Index is fragmented. I track the records/page average for each index and reindex when it drops more the N% (between 10% and 30% depending on the Index). We even keep a seperate DB just to help monitor this information. S.W. Art S. Kagel <kagel@bloomberg.net> wrote in message news:37DD2066.E29ACB9C@bloomberg.net... > evetsm@rocketmail.com wrote: > > > > We need 24x7 availability and oncheck is locking tables up for too > > long. Can we : > > > > 1. Switch off the oncheck table locking ? > > 2. Run oncheck on a secondary replicating database ? > > 3. Run oncheck on the primary repliacting database and switch off > > replication for the duration, using the secondary as primary and then > > reverting back when oncheck is completed ? > > 4. Use any other method to circumvent the locking ? > > I know it sounds like heresy, but I only run oncheck when I have detected > a problem that I cannot isolate any other way and I cannot afford to just > drop and rebuild ALL the indexes. Losing a production system for the four > hours it takes to rebuild an index (or sometimes even 16 hours for all > four) was better than running oncheck -cD for 6 hours followed by oncheck > -cI for another 32 hours (over 8 hours per index) on a large (160.6 > Million rows) table spanning 38 2GB chunks (27 data, 11 index). > > Art S. Kagel

In version 7.3 and version 9.2 the oncheck -cI will not hold any exclusive locks on the table as long as the table has row level locking. This should help the large 24x7 shops and hopefully in the future we can do oncheck -cD, but currently archecker will validate about 75% of what oncheck -cD will do. Hope this helps, ---jmiller John Miller "Art S. Kagel" wrote: > evetsm@rocketmail.com wrote: > > > > We need 24x7 availability and oncheck is locking tables up for too > > long. Can we : > > > > 1. Switch off the oncheck table locking ? > > 2. Run oncheck on a secondary replicating database ? > > 3. Run oncheck on the primary repliacting database and switch off > > replication for the duration, using the secondary as primary and then > > reverting back when oncheck is completed ? > > 4. Use any other method to circumvent the locking ? > > I know it sounds like heresy, but I only run oncheck when I have detected > a problem that I cannot isolate any other way and I cannot afford to just > drop and rebuild ALL the indexes. Losing a production system for the four > hours it takes to rebuild an index (or sometimes even 16 hours for all > four) was better than running oncheck -cD for 6 hours followed by oncheck > -cI for another 32 hours (over 8 hours per index) on a large (160.6 > Million rows) table spanning 38 2GB chunks (27 data, 11 index). > > Art S. Kagel

In article <7rifbi$9he$1@nnrp1.deja.com>, evetsm@rocketmail.com wrote: > > > We need 24x7 availability and oncheck is locking tables up for too > long. Can we : > > 1. Switch off the oncheck table locking ? No, but oncheck doesn't lock table exclusively. For the given moment of time oncheck locks index pages (cI) or data rows (or pages - if table has page level locking) - cD. > 2. Run oncheck on a secondary replicating database ? But this check will validate pages on the secondary system. If problem with disk pages exists on the primary server, I think there is too low probability of this error on the secondary server. Disk errors are physical, they doesn't replicated in logical records :))). And I'm not sure you'll be able to start oncheck on secondary system. For example, on SCO 5.02 & IDS 7.22UC4 it doesn't work on secondary server. > 3. Run oncheck on the primary repliacting database and switch off > replication for the duration, using the secondary as primary and then > reverting back when oncheck is completed ? Hm, if activity is low - you can do this. But you must think about time you needed to restore replication. And what are you going to do, if your server falls and replication is broken ? I think this way is not good idea. > 4. Use any other method to circumvent the locking ? I'm fully agree with Art - IMHO 'oncheck -cDI' is needed only if you found some errors related to the disk structure. If you want check your system daily - I recommend you create some number of sripts and executes them in parralel. Each script must check his own tables and indexes. This improves performance a little bit. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.