Re: Select the tablespace of the fragment containing the selected row

Hi again, Family. Thanks Mark and Art, for your imaginative solutions. If I am ever again confronted by this situation I will lean toward Mark's solution, as that's easier on the users. To recap, Art's solution was to: > o Set ONDBSPACEDOWN 0 (and restart if that's a change) > o Mark all the other dbspaces down > o Unload the table getting rows only from the dbspace that's still > online. > o Mark the dbspaces back up > > Or inversely, only mark the suspect dbspace down and unload. If the > data's fine then by association the bad data must be coming from the > suspect dbspace. Mark's solution, easier on the users because most of the data is still available while I do this, is to: > Have you tried ALTER FRAGMENT ON table DETACH dbspace new_table. This > will move the data out from a single fragment to a new table. Both of these have the feel of major surgery on the database. It points up the need for a feature request to be entered with Informix - the ability to identify the fragment of DBSpace containing the row I am retrieving. This is similar in principle to getting the rowid in a non-partitioned table. (For all I know this is already an available feature but I'm stuck with this old version, 7.23.) I (Jacob Salomon) wrote in message news:<38b6a68e.0401081002.450cae7f@posting.google.com>... > Greetings, Family. > > I have a situation where I need to select a subset of the rows of a > fragmented table - the rows, say, DBSpace#6. If it is fragmented by > expression it's easy; just specify "WHERE <fragment condition>". > Similarly, for any row I randomly select, I can check for the > condition of each fragment in turn until I get a match. But if it > is fragmented by round-robin, how do I select the rows that reside > only in the specified DBSpace? Or if selecting by other means, how > do I retrieve the name of the row's DBSpace? > > I have the eerie feeling I have done this before but I can't recall > how. Very frustrating! > > My motivation here is to isolate some corruption that may have been > cause by a hardware glitch. I need to prove if it is table-wide or > only in one dbspace. +------------ Jacob Salomon JSalomon@bn.com -- -------------- -----+ | Man does not live by words alone, despite the fact that sometimes| | he has to eat them. | +------------------------------------------- Adlai Stevenson ------+

Jacob Salomon wrote: > > Hi again, Family. > > Thanks Mark and Art, for your imaginative solutions. If I am ever > again confronted by this situation I will lean toward Mark's solution, > as that's easier on the users. > > To recap, Art's solution was to: > > > o Set ONDBSPACEDOWN 0 (and restart if that's a change) > > o Mark all the other dbspaces down > > o Unload the table getting rows only from the dbspace that's still > > online. > > o Mark the dbspaces back up > > > > Or inversely, only mark the suspect dbspace down and unload. If the > > data's fine then by association the bad data must be coming from the > > suspect dbspace. > > Mark's solution, easier on the users because most of the data is still > available while I do this, is to: > > > Have you tried ALTER FRAGMENT ON table DETACH dbspace new_table. This > > will move the data out from a single fragment to a new table. > > Both of these have the feel of major surgery on the database. It > points up the need for a feature request to be entered with Informix - > the ability to identify the fragment of DBSpace containing the row I > am retrieving. This is similar in principle to getting the rowid in a > non-partitioned table. (For all I know this is already an available > feature but I'm stuck with this old version, 7.23.) > > I (Jacob Salomon) wrote in message news:<38b6a68e.0401081002.450cae7f@posting.google.com>... > > Greetings, Family. > > > > I have a situation where I need to select a subset of the rows of a > > fragmented table - the rows, say, DBSpace#6. If it is fragmented by > > expression it's easy; just specify "WHERE <fragment condition>". > > Similarly, for any row I randomly select, I can check for the > > condition of each fragment in turn until I get a match. But if it > > is fragmented by round-robin, how do I select the rows that reside > > only in the specified DBSpace? Or if selecting by other means, how > > do I retrieve the name of the row's DBSpace? > > > > I have the eerie feeling I have done this before but I can't recall > > how. Very frustrating! > > > > My motivation here is to isolate some corruption that may have been > > cause by a hardware glitch. I need to prove if it is table-wide or > > only in one dbspace. > > +------------ Jacob Salomon JSalomon@bn.com -- -------------- -----+ > | Man does not live by words alone, despite the fact that sometimes| > | he has to eat them. | > +------------------------------------------- Adlai Stevenson ------+ Depending on your setup details and version you must consider that ALTER TABLE .. DETACH has some nice lil features: On V9.21.UC3/4 and on V9.40.UC1/2 the structure of your detached (new) table will not have any constraints anymore, which are not coded in the constraints system catalog table - like ...NOT NULL I wrote a small Perl script to over come this, which was not a big thing. The real disadvantage is (we run ANSI mode) you put each & every Byte of your new table thru the logical log. Sort of a show stopper if your fragmentation was only in place to overcome the 16 mio rows per fragment limit....... If you frags are huge, I would consider the more complicated looking ways of downing all frags, which you do not want to investigate. Another, third - but not better alternative might be to create a dump of the datapages using oncheck -?D? (not too sure, but see what oncheck -- gives you. You must have plenty of empty space in filesystem or an intelligent filter, because those dumps tend to get *very* big. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe