Re: unload creating duplicate versions of edited records

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

On Oct 20, 1:00 pm, Andrew Clarke <acla...@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) Hope this helps a bit.

Bones wrote: > On Oct 20, 1:00 pm, Andrew Clarke <acla...@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=-

On Oct 20, 2:31 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > Bones wrote: > > On Oct 20, 1:00 pm, Andrew Clarke <acla...@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=- No, none of the i710_shno values also appear in irdb060.i060_shno, so this is not the problem. I had thought of SHARE mode, unfortunately the table does need to be available for update while been unloaded. I have set the isolation level to "COMMITTED READ", assuming the level it currently it is set to is "ISOLATION" read. I did think that "COMMITTED READ" was the default and we have no code changing the the ISOLATION level, but from the discussion it seems likely that this may be the problem. Thanks.

Approximately how many rows are there in the zzdb040 table, and approximately how many rows pop out of the UNION? I can think of a work-around where you firstly select all the rows into a temp table under REPEATABLE READ or LOCK TABLE and then apply the SELECT to the temp table. The idea is to do the quickest possible burst of locking and then release your locks so you have a clean snapshot, but timing depends on how big the table is. The method would rely on your SELECT INTO TEMP completing before any other process's LOCK timeout fires. Another trick might be to 1/ create a temp table to receive the rows; simplest way is SELECT zzdb040.* FROM zzdb040 where 1=0 into temp thingy with no log (check my syntax is correct) 2/ put a unique index on the temp table which is unique according to the criteria you know identifies a particular row 3/ select the source rows in a FOREACH loop and insert into the temp, being careful to ignore duplicate violations. If you feel fancy and want to have the latest value of a row, you could detect the duplicate violation and issue an UPDATE on the temp table row instead. 4/ finally unload from the temp table.