SEO

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.

Thanks much.

+------------ Jacob Salomon JSalomon@bn.com -- -------------- -----+
| Man does not live by words alone, despite the fact that sometimes|
| he has to eat them. |
+------------------------------------------- Adlai Stevenson ------+

On Thu, 08 Jan 2004 13:02:35 -0500, Jacob Salomon wrote:

> 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.

Hi Jake,

Try:

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.

Art S. Kagel


> Thanks much.
>
> +------------ Jacob Salomon JSalomon@bn.com -- -------------- -----+ | Man
> does not live by words alone, despite the fact that sometimes| | he has to
> eat them. |
> +------------------------------------------- Adlai Stevenson ------+

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