Re: Corrupt indexes on a system catalog

In article <34B2B3A7.31FB90D3@garpac.com>, Jacob Salomon <jake@garpac.com> writes >AIX 3.1.4 >OnLine 7.11.UC1 >Level: Not for the faint of heart. > >Hi Family. > >I have a biiig problem with a client system down. I am bcc'ing the tech >support person but I think he needs some help. > >Several times a day, the alarm program kicks in with a complaint about a >corrupt index and the af file tells me to run the appropriate oncheck >command. When this succeeds, it looks OK. But it fails alot. One of the >af messages got me suspicious and decided to run oncheck against >systables, syscolumns and sysindexes. Here is a sample session: > >43p-v3$ oncheck -cDI garpacv2:systables> >Validating indexes for garpacv2:informix.systables... > Index tabname > Index tabid >ERROR:Key value mismatch between data row and btree item >Rowid 0xa21 contains key value: >Key: 4463: >Btree item contains rowid 0xa21, key value: >Key: 4399: > >Index tabid is bad. OK to repair it? y >Error recreating index. >ISAM error: non-exclusive access.> >TBLspace data check for garpacv2:informix.systables > > >Now, this non-exlusive access is strange - I (informix) am the only one >on the system. I even got this when I ran oncheck in quiescent mode. > >In another window I ran onstat -k before the oncheck and while the >oncheck is awaiting my "y". The lock list before the oncheck is, of >course, empty - my control. Here is the result when I ran it while the >prompt was up: > >43p:/home/garpac/jake[234]> onstat -k > >INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:03:53 -- 26616 >Kbytes > >Locks >address wtlist owner lklist type tblsnum rowid key#/bsiz >30257f60 0 4000dae8 0 HDR+S 100002 209 0 >3025887c 0 4000dae8 30257f60 HDR+S 300002 0 0 > 2 active, 80000 total, 32768 hash buckets > >As I recall, tblspace 100002 is table sysdatabases and rowid 209 refers >to the sysdatabases entry for my database (garpacv2). tblspace 300002 is >systables from my database - I checked. > Yes, you have a shared lock in the database (to stop anyone from dropping it whilst you are acessing it) and a shared lock on systables. i.e. you are reading systables. >So, I am holding a lock on a table (systables), yet when I go to do >something to the table I have locked I seem to be blocked my my own >lock. > >43p:/home/garpac/jake[235]> onstat -u > >INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:08:38 -- 26616 >Kbytes > >Userthreads >address flags sessid user tty wait tout locks nreads nwrites >4000a010 ---P--D 0 informix - 0 0 0 129 283 >4000a444 ---P--F 0 informix - 0 0 0 0 0 >4000a878 ---P--F 0 informix - 0 0 0 0 0 >4000acac ---P--B 8 informix - 0 0 0 0 0 What is session 8 doing? This is not an oninit since it would have a sessionid of 0. >4000b0e0 ---P--D 0 informix - 0 0 0 0 0 >4000dae8 ---P--- 145 informix 4 0 0 2 0 0 >4000ebb8 Y------ 145 informix 4 40173a48 0 2 0 0 > 7 active, 128 total, 19 maximum concurrent > >Those last 2 sessions are my oncheck. So what does my session look >like? Sorry you asked... > >43p:/home/garpac/jake[236]> onstat -g ses 145 > >INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:09:48 -- 26616 >Kbytes > >session #RSAM total used >id user tty pid hostname threads memory memory >145 informix 4 194282 43p 2 81920 41636 > >tid name rstcb flags curstk status >321 oncheck 4000dae8 ---P--- 3064 sleeping(Forever) >322 oncheckm 4000ebb8 Y------ 5068 cond wait(sm_read) > >Memory pools count 1 >name class addr totalsize freesize #allocfrag #freefrag >145 V 40326010 81920 40284 132 11 > >name free used name free used >overhead 0 100 scb 48 40 >opentable 0 6856 filetable 28 1348 >misc 728 376 log 32768 8336 >temprec 0 2444 blob 0 392 >ralloc 0 4096 gentcb 0 376 >ostcb 0 2268 net 68 3788 >tbutility 0 32 sqscb 0 7392 >rdahead 0 344 scan_desc 544 0 >hashfiletab 0 552 osenv 48 772 >oncheck 6052 68 sqtcb 0 1848 >fragman 0 208 > >Sess SQL Current Iso Lock SQL ISAM F.E. >Id Stmt type Database Lvl Mode ERR ERR Vers >145 UNKNOWN garpacv2 CR Not Wait 0 0 7.11 > >Obviously no SQL statament - I can't run SQL in quiescent mode. What >other info can I include here? Oh yes, the transaction list, such as it >can be under quiescent mode: > >43p:/home/garpac/jake[238]> onstat -x > >INFORMIX-OnLine Version 7.11.UC1 -- Quiescent -- Up 20:20:56 -- 26616 >Kbytes > >Transactions >address flags userthread locks log begin isolation retrys coordinator >4002c010 A---- 4000a010 0 0 COMMIT 0 >4002c134 A---- 4000a444 0 0 COMMIT 0 >4002c258 A---- 4000a878 0 0 COMMIT 0 >4002c37c A---- 4000acac 0 0 COMMIT 0 >4002c4a0 A---- 4000b0e0 0 0 COMMIT 0 >4002ca54 A---- 4000dae8 2 0 COMMIT 0 > 6 active, 128 total, 15 maximum concurrent > >Note the last transaction in the list - the userthread corresponds to >the next-to-last thread listed in onstat -u. > Yes, the oncheck thread is holdng two locks. Everything seems ok. What does onstat -g ath show? Try bouncing the engine to make sure no-one is connected, even remotely as informix. >What else can I look at? > >Bottom line: HOW CAN I CORRECT a CORRUPTED INDEX ON S SYSTEM CATALOG? > >Thanks for any help you can offer. (If you even read this you are quite >a person!) -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care