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

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.) > Hi Jake, I am a little bit confused here. If you select a particular row with a "FOR UPDATE" cursor, (if you have I-SQL just try to update the row through a sample form created for that table, otherwise write a small program in ESQLC/4GL or even in SPL) and WAIT for a while before the actual update, you can see "tblsnum" (actually fragment "partnum" in hex) and "rowid" for that row in the output of "onstat -k" command (combined with "onstat -u" and "onstat -g ses" of course). Is that enough for you, or am I missing something? Kind regards, Mladen > > 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 ------+ > -- Mladen Jovanovski Phone: +389 2 244 1140 Mobile: +389 75 400 309 COSMOFON - Mobile Telecommunications Services - A.D. Skopje _______________________________________________________________ This e-mail (including any attachments) is confidential and may be protected by legal privilege. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete your copy from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. COSMOFON A.D. Skopje shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. sending to informix-list

Mladen, your innovative solution, selecting for update and waiting, followed by onstat -k, would also work to locate the rowid of a locked row.Fortunately, I don't have to do that; if I want to find the rowid of a selected row, I can simply say "select rowid,<other columns>..." My original question was: Is there an equivalent way to obtain other row information, like which fragment of the table contains the row I have selected. I now see the answer: No, there is no simple way. "Mladen Jovanovski" <mladen.jovanovski@cosmofon.com.mk>wrote in message news:<buqtp0$qga$1@terabinaries.xmission.com>... >I am a little bit confused here. > >If you select a particular row with a "FOR UPDATE" cursor, >(if you have I-SQL just try to update the row through a sample >form created for that table, otherwise write a small program >in ESQLC/4GL or even in SPL) and WAIT for a while before >the actual update, you can see "tblsnum" (actually fragment >"partnum" in hex) and "rowid" for that row in the output of >"onstat -k" command (combined with "onstat -u" and >"onstat -g ses" of course). > >Is that enough for you, or am I missing something? > >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 ------+