RE: IDS 2000 9.21.UC4/Solaris/Sparc Problem accessing table - urgent

What does the output of oncheck -ce say? > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org] On Behalf Of Bobb > Sent: Friday, 13 May 2005 2:38 a.m. > To: informix-list@iiug.org > Subject: Re: IDS 2000 9.21.UC4/Solaris/Sparc Problem > accessing table - urgent > > In reply to the last couple of suggestions: > > 1. All other tables in the same db work fine. So the > hardware in now working. > > 2. Among the first things that I did was try to run oncheck > and it just sat and did nothing on the damaged table. > > I have been using informix since 1999 and have had previous > failures and done recoveries including unloading and loading > back the data, skipping over corrupt pages. However this is > the first time that no dbaccess or utitlity will give me a > reading on the table AND the online.log does *not* show any > errors for the table after numerous informix restarts. > > PS. We tried to do an ontape -s -L 0 and it just hung when > it came to the table. > > Bob > > siebrand@gmail.com wrote: > > > Hi Bob, > > > > I would recommend performing an oncheck against the instance. > > oncheck -cc > > oncheck -cID> > > > Cheers, > > > > Robert C B wrote: > > > >>We had a hardware controller glitch yesterday and the IDS engine > >>panic-ed and crashed. This history was reconstructed from > online.log. > >> > >>Informix came up after rebooting the OS, recovered from the > logs and > >>gave no error indications. (Lost the af... dump ) > >> > >>Of a total of about 50 tables in 2 major databases there is > one table > > > > > >>that appears to be unaccessible. > >> > >>1. A simple 'select count(*) from tabname;' sits around and gets no > > > > IO > > > >>(onstat -u). > >> > >>2. In dbaccess table/info/tabname/status does not > complete. At the > > > > > >>bottom of dbaccess page it shows 'Running.....' but does nothing > >> > >>3. dbaccess table/info/index will list the indices ok. > >> > >>4. dbaccess table/info/fragments just hangs and will not complete. > >> > >>5. oncheck on the table just just hangs around and does not get any > > > > IOs. > > > >>6. An attempt to drop the primary index just hung and did no IOs. > >> > >>7. Have restarted IDS several times and there are no error messages > > > > of > > > >>any kind related to this table in the online.log. The table still > > > > does > > > >>not function (as if some lock is being held in sysmaster??) > >> > >> > >>One of the temporarily failed disks contained a fragment of this > > > > table. > > > >> However there were over a dozen failed disks from other > >>tables/fragments and they now work fine including the dbspace for > >>physical log.. > >> > >>We need to be able to access the info in this table We > would really > >>prefer not to restore from the last backup which is about 2 weeks > > > > old. > > > >> > >>Help please > >> > >>Bob Bankay > > > > > > sending to informix-list

Not sure why this thread is separate from the main thread?? Anyway, there is no problem running oncheck -ce -- it lists all the chunks and the contents of the chunks. In the case of the problem table is lists the table extents and the extents for the 2 indices that we have on it. Note these are in 8 dbspaces. Note is does not acutally list data, rather just the directory structure/layout in the various chunks. the option oncheck -cdI tablename is supposed to do minor repairs and make the table consistent. This option does not work. Any option that includes i or I does not work. The option oncheck -cD works and indicates that all the table fragments are fine. I accessed sysindices and deleted the entries for the indices on the table. However, dbaccess still finds the 2 indices and tries to use them and fails when trying to access the indices. Does anyone know of a directive on a select to *not* use an index? Bob Murray Wood (IList) wrote: > What does the output of oncheck -ce say? > > > >>-----Original Message----- >>From: owner-informix-list@iiug.org >>[mailto:owner-informix-list@iiug.org] On Behalf Of Bobb >>Sent: Friday, 13 May 2005 2:38 a.m. >>To: informix-list@iiug.org >>Subject: Re: IDS 2000 9.21.UC4/Solaris/Sparc Problem >>accessing table - urgent >> >>In reply to the last couple of suggestions: >> >>1. All other tables in the same db work fine. So the >>hardware in now working. >> >>2. Among the first things that I did was try to run oncheck >>and it just sat and did nothing on the damaged table. >> >>I have been using informix since 1999 and have had previous >>failures and done recoveries including unloading and loading >>back the data, skipping over corrupt pages. However this is >>the first time that no dbaccess or utitlity will give me a >>reading on the table AND the online.log does *not* show any >>errors for the table after numerous informix restarts. >> >>PS. We tried to do an ontape -s -L 0 and it just hung when >>it came to the table. >> >>Bob >> >>siebrand@gmail.com wrote: >> >> >>>Hi Bob, >>> >>>I would recommend performing an oncheck against the instance. >>>oncheck -cc >>>oncheck -cID>>> >>>Cheers, >>> >>>Robert C B wrote: >>> >>> >>>>We had a hardware controller glitch yesterday and the IDS engine >>>>panic-ed and crashed. This history was reconstructed from >> >>online.log. >> >>>>Informix came up after rebooting the OS, recovered from the >> >>logs and >> >>>>gave no error indications. (Lost the af... dump ) >>>> >>>>Of a total of about 50 tables in 2 major databases there is >> >>one table >> >>> >>>>that appears to be unaccessible. >>>> >>>>1. A simple 'select count(*) from tabname;' sits around and gets no >>> >>>IO >>> >>> >>>>(onstat -u). >>>> >>>>2. In dbaccess table/info/tabname/status does not >> >>complete. At the >> >>> >>>>bottom of dbaccess page it shows 'Running.....' but does nothing >>>> >>>>3. dbaccess table/info/index will list the indices ok. >>>> >>>>4. dbaccess table/info/fragments just hangs and will not complete. >>>> >>>>5. oncheck on the table just just hangs around and does not get any >>> >>>IOs. >>> >>> >>>>6. An attempt to drop the primary index just hung and did no IOs. >>>> >>>>7. Have restarted IDS several times and there are no error messages >>> >>>of >>> >>> >>>>any kind related to this table in the online.log. The table still >>> >>>does >>> >>> >>>>not function (as if some lock is being held in sysmaster??) >>>> >>>> >>>>One of the temporarily failed disks contained a fragment of this >>> >>>table. >>> >>> >>>> However there were over a dozen failed disks from other >>>>tables/fragments and they now work fine including the dbspace for >>>>physical log.. >>>> >>>>We need to be able to access the info in this table We >> >>would really >> >>>>prefer not to restore from the last backup which is about 2 weeks >>> >>>old. >>> >>> >>>>Help please >>>> >>>>Bob Bankay >>> >>> >> > > > sending to informix-list

See "Optimizer Directives" in the SQL syntax manual: SELECT {+AVOID_INDEX(<tabname> <indexname>[, <tabname> <indexname>])} e.g. SELECT {+AVOID_INDEX(customer ix_custcomp)} FROM customer ... NOTE: there's a space NOT a dot between tabname and indexname. Thanks, Informix. Consistency is what we like!

Thanks Malcom, The syntax worked but still no joy. It just hung there not doing IOs however burning some CPU. I was hoping to unload/drop/recreate/reload the data. Additional discovery late last night: I had not test oncheck -cc because it seemed we had a problem with a specific table and not some meta data/structure. I was *wrong*. Apparently dbname:sysindexes got corrupted. As to why it only affects a single table and there are no complaints about it except with oncheck -cc is mysterious. BTW, oncheck hangs at the point where it reports the error. Here is the -cc output: galileo% oncheck -cc Validating database sysmaster Validating systables for database sysmaster Validating syscolumns for database sysmaster Validating sysindexes for database sysmaster <snip> Validating database sah Validating systables for database sah ERROR: No sysindexes records found. There should be 1 records.\\n ^CInterrupt received ... The check has been aborted. *The oncheck was aborted after being in a loop for about 20 minutes. Not sure why this error message. I did a count(*) on sysindexes and got 103 entries, and all the data seems okay. Note: the dbspace/chunk where these db:sys--- tables reside really go clobbered on the day when the controller failed. But they seemed to recover and did not give any error messages. Any more ideas? Bob B malc_p@btinternet.com wrote: > See "Optimizer Directives" in the SQL syntax manual: > > SELECT {+AVOID_INDEX(<tabname> <indexname>[, <tabname> <indexname>])} > > e.g. > SELECT {+AVOID_INDEX(customer ix_custcomp)} > FROM customer > ... > > > NOTE: there's a space NOT a dot between tabname and indexname. Thanks, > Informix. Consistency is what we like! >

Murray, oncheck -ce just lists the chunks and their symbolic link addresses.They all seemed okay. However oncheck -cc failed indicating a problem with dbname:sysindexes that it contained no entries. With dbaccess I found 103 entries in sysindexes and then listed a large part of the data and it was fine. So I am not sure why oncheck failed. BTW, it also went into a busy loop and did not further IOs. Lastly the chunk where all the dbname:sys*** tables are located did show up about 6 times in the online.log show IO error during the problem period. Bob Murray Wood (IList) wrote: > What does the output of oncheck -ce say? > > > >>-----Original Message----- >>From: owner-informix-list@iiug.org >>[mailto:owner-informix-list@iiug.org] On Behalf Of Bobb >>Sent: Friday, 13 May 2005 2:38 a.m. >>To: informix-list@iiug.org >>Subject: Re: IDS 2000 9.21.UC4/Solaris/Sparc Problem >>accessing table - urgent >> >>In reply to the last couple of suggestions: >> >>1. All other tables in the same db work fine. So the >>hardware in now working. >> >>2. Among the first things that I did was try to run oncheck >>and it just sat and did nothing on the damaged table. >> >>I have been using informix since 1999 and have had previous >>failures and done recoveries including unloading and loading >>back the data, skipping over corrupt pages. However this is >>the first time that no dbaccess or utitlity will give me a >>reading on the table AND the online.log does *not* show any >>errors for the table after numerous informix restarts. >> >>PS. We tried to do an ontape -s -L 0 and it just hung when >>it came to the table. >> >>Bob >> >>siebrand@gmail.com wrote: >> >> >>>Hi Bob, >>> >>>I would recommend performing an oncheck against the instance. >>>oncheck -cc >>>oncheck -cID>>> >>>Cheers, >>> >>>Robert C B wrote: >>> >>> >>>>We had a hardware controller glitch yesterday and the IDS engine >>>>panic-ed and crashed. This history was reconstructed from >> >>online.log. >> >>>>Informix came up after rebooting the OS, recovered from the >> >>logs and >> >>>>gave no error indications. (Lost the af... dump ) >>>> >>>>Of a total of about 50 tables in 2 major databases there is >> >>one table >> >>> >>>>that appears to be unaccessible. >>>> >>>>1. A simple 'select count(*) from tabname;' sits around and gets no >>> >>>IO >>> >>> >>>>(onstat -u). >>>> >>>>2. In dbaccess table/info/tabname/status does not >> >>complete. At the >> >>> >>>>bottom of dbaccess page it shows 'Running.....' but does nothing >>>> >>>>3. dbaccess table/info/index will list the indices ok. >>>> >>>>4. dbaccess table/info/fragments just hangs and will not complete. >>>> >>>>5. oncheck on the table just just hangs around and does not get any >>> >>>IOs. >>> >>> >>>>6. An attempt to drop the primary index just hung and did no IOs. >>>> >>>>7. Have restarted IDS several times and there are no error messages >>> >>>of >>> >>> >>>>any kind related to this table in the online.log. The table still >>> >>>does >>> >>> >>>>not function (as if some lock is being held in sysmaster??) >>>> >>>> >>>>One of the temporarily failed disks contained a fragment of this >>> >>>table. >>> >>> >>>> However there were over a dozen failed disks from other >>>>tables/fragments and they now work fine including the dbspace for >>>>physical log.. >>>> >>>>We need to be able to access the info in this table We >> >>would really >> >>>>prefer not to restore from the last backup which is about 2 weeks >>> >>>old. >>> >>> >>>>Help please >>>> >>>>Bob Bankay >>> >>> >> > > > sending to informix-list