Hi,

We have a 4GL program that extracts data from a table using an
unload.  If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit.  We don't
need a particular version of the record, as long as we only get one
version.

I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.

The program has probably set DIRTY READ ISOLATION.  You want COMMITTED READ
or CURSOR STABILITY isolation and to SET LOCK MODE TO WAIT <nseconds> to
avoid getting lockout errors from brief transient locks.

Art

This would presuppose that the row is being rewritten to a different position
in the table for it to be picked up again, and that implies in-place alter has
happened to the table, and consequently a row rewrite is taking place?

Bones wrote:
On Oct 20, 1:00 pm, Andrew Clarke <acla... at (no spam) civica.com.au> wrote:
Hi,
We have a 4GL program that extracts data from a table using an
unload.  If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit.  We don't
need a particular version of the record, as long as we only get one
version.
I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.
The program has probably set DIRTY READ ISOLATION.  You want COMMITTED READ
or CURSOR STABILITY isolation and to SET LOCK MODE TO WAIT <nseconds> to
avoid getting lockout errors from brief transient locks.
Art
This would presuppose that the row is being rewritten to a different position
in the table for it to be picked up again, and that implies in-place alter has
happened to the table, and consequently a row rewrite is taking place?

The updates that happen to the table are straight updates.  As far as
I understand it, there is no re-write.  The code in the program where
the proble is happening is:
UNLOAD TO m_unl_file_name
    SELECT zzdb040.* FROM zzdb040
        WHERE z040_shno IN
            (SELECT i710_shno from irdb710
            WHERE i710_coy  = m_coy)
    UNION
    SELECT zzdb040.* FROM zzdb040
        WHERE z040_shno IN (SELECT i060_shno FROM irdb060)

Do any of the irdb710.i710_shno values also appear in irdb060.i060_shno?
  Although UNION eliminates duplicate rows, if the row has changed while
the UNLOAD is occurring, then it might explain why you see two different
values.

Have you considered locking the zzdb040 table in SHARE mode to prevent
others from updating it while the UNLOAD proceeds?  Or, as others have
suggested, use a more stringent isolation level - I think repeatable
read is likely to be the most reliable choice, but it will also apply a
lot of locks.

-=JL=-

Not necessarily, it would also happen if the query were using an index and
that index key was being modified by some users so that the row shows up
again as the unload follows the btree.

hold the phone; a plain unload doesn't follow indexes; do you have a WHERE
clause on the unload?

Yes,

Not necessarily, it would also happen if the query were using an index and
that index key was being modified by some users so that the row shows up
again as the unload follows the btree.

On Oct 20, 1:00 pm, Andrew Clarke <acla... at (no spam) civica.com.au> wrote:
Hi,
We have a 4GL program that extracts data from a table using an
unload. If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit. We don't
need a particular version of the record, as long as we only get one
version.
I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.
The program has probably set DIRTY READ ISOLATION. You want COMMITTED READ
or CURSOR STABILITY isolation and to SET LOCK MODE TO WAIT <nseconds> to
avoid getting lockout errors from brief transient locks.
Art
This would presuppose that the row is being rewritten to a different position
in the table for it to be picked up again, and that implies in-place alter has
happened to the table, and consequently a row rewrite is taking place?

The updates that happen to the table are straight updates. As far as
I understand it, there is no re-write. The code in the program where
the proble is happening is:
UNLOAD TO m_unl_file_name
SELECT zzdb040.* FROM zzdb040
WHERE z040_shno IN
(SELECT i710_shno from irdb710
WHERE i710_coy = m_coy)
UNION
SELECT zzdb040.* FROM zzdb040
WHERE z040_shno IN (SELECT i060_shno FROM irdb060)

On Oct 20, 3:04?pm, Andrew Clarke <acla... at (no spam) civica.com.au> wrote:
Not necessarily, it would also happen if the query were using an index
and that index key was being modified by some users so that the row
shows up again as the unload follows the btree.

hold the phone; a plain unload doesn't follow indexes; do you have a
WHERE clause on the unload?

Yes,

There is a "WHERE". See above where I posted the unload.

oh yeah, there it is. So row drift in the index is a good candidate. This

Hi,

We have a 4GL program that extracts data from a table using an
unload. If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit. We don't
need a particular version of the record, as long as we only get one
version.

I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.

Thanks for any suggestions.

We are using 4GL 7.31.FC1, Informix Dynamic Server 7.31.UD3 on Solaris
8.
_______________________________________________
Informix-list mailing list
Informix-list at (no spam) iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Hi,

We have a 4GL program that extracts data from a table using an
unload. If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit. We don't
need a particular version of the record, as long as we only get one
version.

I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.


The program has probably set DIRTY READ ISOLATION. You want COMMITTED
READ
or CURSOR STABILITY isolation and to SET LOCK MODE TO WAIT <nseconds> to
avoid getting lockout errors from brief transient locks.

Art

This would presuppose that the row is being rewritten to a different
position
in the table for it to be picked up again, and that implies in-place alter
has
happened to the table, and consequently a row rewrite is taking place?

Not necessarily, it would also happen if the query were using an index and
that index key was being modified by some users so that the row shows up
again as the unload follows the btree.

Hi,

We have a 4GL program that extracts data from a table using an
unload. If it encounters a record that is been edited it then creates
2 versions of this record, one pre-edit and one post edit. We don't
need a particular version of the record, as long as we only get one
version.

I have never heard nor seen this behaviour before and am at a loss as
to what causes it and how to solve the problem.


The program has probably set DIRTY READ ISOLATION. You want COMMITTED READ
or CURSOR STABILITY isolation and to SET LOCK MODE TO WAIT <nseconds> to
avoid getting lockout errors from brief transient locks.

Art

Not necessarily, it would also happen if the query were using an index and
that index key was being modified by some users so that the row shows up
again as the unload follows the btree.


Oh yeah, that. Which implies COMMITTED READ or CURSOR STABILITY may not be
enough...

Really, the safest way is to unload on a quiet db. REPEATABLE READ isn't much
short of LOCK TABLE, and you can guess the effect of that on other people.

At least LOCK TABLE will guarantee a consistent unload. With REPEATABLE READ,
you can toss a coin as to whether your unload process wins or loses in a
conflict with one other process. If there are many updaters out there, the
chances of everyone suffering rises accordingly.

Since you are on a 7.3 engine, the following won't help you much; I believe
the 11 engines have a mode where you can pretend you are not seeing any
updates, similar to Oracle's slant on this. However I'm not familiar with this
mode. Probably someone else can comment.