SEO

Hi All,

We are facing a very strange issue while using FOREACH. The environment
is as follows:

HP-UX 11.11
Informix 4GL 7.30.HC4P1
Informix Dynamic Server Version 9.30.HC2

4GL program is opening a cursor on table 'tab1' with certain conditions
in the where clause. When a FOREACH is applied to traverse through this
cursor, it is not returning all the rows which satisfy the conditions.
Due to this, the output of the program is not as expected. The
behaviour is also not consistent as sometimes it gives 'x' number of
records less and sometimes it gives 'y' number of records less.

Please note:
(1) The cursor declared is a plain cursor and not scroll cursor and
without any WITH HOLD clause.
(2) The isolation is set to dirty read before opening the cursor.

Kindly let us know if there is any resolution to this issue. Is this
some kind of bug in Informix 4GL tool for the version mentioned above.

Thanks.

Regards,
Ravi.

ravi,

it may be a bug in the server, 9.30.HC2 is now obsolete

get your engine onto 9.40 or 10 and see if the problem still
reproduces.



Ravi wrote:
> Hi All,
>
> We are facing a very strange issue while using FOREACH. The environment
> is as follows:
>
> HP-UX 11.11
> Informix 4GL 7.30.HC4P1
> Informix Dynamic Server Version 9.30.HC2
>
> 4GL program is opening a cursor on table 'tab1' with certain conditions
> in the where clause. When a FOREACH is applied to traverse through this
> cursor, it is not returning all the rows which satisfy the conditions.
> Due to this, the output of the program is not as expected. The
> behaviour is also not consistent as sometimes it gives 'x' number of
> records less and sometimes it gives 'y' number of records less.
>
> Please note:
> (1) The cursor declared is a plain cursor and not scroll cursor and
> without any WITH HOLD clause.
> (2) The isolation is set to dirty read before opening the cursor.
>
> Kindly let us know if there is any resolution to this issue. Is this
> some kind of bug in Informix 4GL tool for the version mentioned above.
>
> Thanks.
>
> Regards,
> Ravi.

Ravi wrote:
> Hi All,
>
> We are facing a very strange issue while using FOREACH. The environment
> is as follows:
>
> HP-UX 11.11
> Informix 4GL 7.30.HC4P1
> Informix Dynamic Server Version 9.30.HC2
>
> 4GL program is opening a cursor on table 'tab1' with certain conditions
> in the where clause. When a FOREACH is applied to traverse through this
> cursor, it is not returning all the rows which satisfy the conditions.
> Due to this, the output of the program is not as expected. The
> behaviour is also not consistent as sometimes it gives 'x' number of
> records less and sometimes it gives 'y' number of records less.
>
> Please note:
> (1) The cursor declared is a plain cursor and not scroll cursor and
> without any WITH HOLD clause.
> (2) The isolation is set to dirty read before opening the cursor.
>
> Kindly let us know if there is any resolution to this issue. Is this
> some kind of bug in Informix 4GL tool for the version mentioned above.
>
> Thanks.
>
> Regards,
> Ravi.
>


And the contents of the table are not changing while your program runs?

Regards.

Hi,

Update on this problem posted by me:

While reading from the table 'tab1', after some processing, we also
update one timestamp field in the same table 'tab1'. So in short the
program is something like this:

FOREACH cur1
...
<Do some processing here>
...
EXECUTE upd_stmnt_for_tab1
END FOREACH

Regards,
Ravi.

Fernando Nunes wrote:
> Ravi wrote:
> > Hi All,
> >
> > We are facing a very strange issue while using FOREACH. The environment
> > is as follows:
> >
> > HP-UX 11.11
> > Informix 4GL 7.30.HC4P1
> > Informix Dynamic Server Version 9.30.HC2
> >
> > 4GL program is opening a cursor on table 'tab1' with certain conditions
> > in the where clause. When a FOREACH is applied to traverse through this
> > cursor, it is not returning all the rows which satisfy the conditions.
> > Due to this, the output of the program is not as expected. The
> > behaviour is also not consistent as sometimes it gives 'x' number of
> > records less and sometimes it gives 'y' number of records less.
> >
> > Please note:
> > (1) The cursor declared is a plain cursor and not scroll cursor and
> > without any WITH HOLD clause.
> > (2) The isolation is set to dirty read before opening the cursor.
> >
> > Kindly let us know if there is any resolution to this issue. Is this
> > some kind of bug in Informix 4GL tool for the version mentioned above.
> >
> > Thanks.
> >
> > Regards,
> > Ravi.
> >
>
>
> And the contents of the table are not changing while your program runs?
>
> Regards.

you need to set isolation to commited read and put a begin work /
commit work around the foreach loop

SET ISOLATION

BEGIN WORK

FOREACH
EXECUTE UPDATE
END FOREACH

COMMIT WORK

If you don't have the begin and the commit then each update/insert will
be treated as a singleton transaction


Ravi wrote:
> Hi,
>
> Update on this problem posted by me:
>
> While reading from the table 'tab1', after some processing, we also
> update one timestamp field in the same table 'tab1'. So in short the
> program is something like this:
>
> FOREACH cur1
> ...
> <Do some processing here>
> ...
> EXECUTE upd_stmnt_for_tab1
> END FOREACH
>
> Regards,
> Ravi.
>
> Fernando Nunes wrote:
> > Ravi wrote:
> > > Hi All,
> > >
> > > We are facing a very strange issue while using FOREACH. The environment
> > > is as follows:
> > >
> > > HP-UX 11.11
> > > Informix 4GL 7.30.HC4P1
> > > Informix Dynamic Server Version 9.30.HC2
> > >
> > > 4GL program is opening a cursor on table 'tab1' with certain conditions
> > > in the where clause. When a FOREACH is applied to traverse through this
> > > cursor, it is not returning all the rows which satisfy the conditions.
> > > Due to this, the output of the program is not as expected. The
> > > behaviour is also not consistent as sometimes it gives 'x' number of
> > > records less and sometimes it gives 'y' number of records less.
> > >
> > > Please note:
> > > (1) The cursor declared is a plain cursor and not scroll cursor and
> > > without any WITH HOLD clause.
> > > (2) The isolation is set to dirty read before opening the cursor.
> > >
> > > Kindly let us know if there is any resolution to this issue. Is this
> > > some kind of bug in Informix 4GL tool for the version mentioned above.
> > >
> > > Thanks.
> > >
> > > Regards,
> > > Ravi.
> > >
> >
> >
> > And the contents of the table are not changing while your program runs?
> >
> > Regards.

Hi All,

We seem to found a solution to this problem. And again it seems to be a
bug/defect with IDS 9.30.HC2.

We had an index on the timestamp field which we were updating in the
FOREACH loop. Now multiple instances of this program were
simultaneously running to process different set of records. Since each
instance of the program was updating the timestamp field for its set,
the index on timestamp field was also being updated. It seems that with
this update of the indexes through different threads, the records
somehow were getting locked and were being skipped when SELECT
statement was executed (although it is to be noted that the SELECT
statements were trying to select some other set of records and not the
ones being updated).

Can somebody throw some light on this behaviour ?

Regards,
Ravi.

Ravi,

I suspect you'll find that your update moves the row to a different
part of the index which the second query has already read and moved
beyond.

I'd be intersted to understand what you are trying to do, what the
start data is, what the 2 processes do and what the expected data will
look like at the end

maybe then we can help you come up with a suitable alternative

Hi All,

Has somebody faced the issue described briefly above in thread number 6
with IDS 9.30.HC2? Is this some kind of defect in IDS version 9.30.HC2?

I'll try to explain the problem in detail:

(1) We have a table tab1 with one index ix1 on fields (timestamp, flag)
and one index ix2 on field (station).
(2) We have 10 instances of a program A (a1, a2, ..., a10) running
simultaneously each querying for different station code. Each instance
selects from tab1 as:

select key_field, <some_other_fields> from tab1 /* key_field is a
serial key for tab1 */
where station = s1 /* the values for a1,a2,...,a10 will
be s1,s2,...,s10 */
and timestamp is null
and flag = 'N'

(3) The program proceeds further with a FOREACH cursor as follows:

DECLARE cur1 cursor for the above select query
PREPARE upd_stmt_for_tab1 AS "update tab1 set timestamp = ? where
key_field = ?"
FOREACH cur1 INTO var_key_field, <list of some other variables>
...
<Do some processing here>
...
EXECUTE upd_stmnt_for_tab1 using <a_not_null_timestamp_value>,
var_key_field
END FOREACH

(4) From the snippet above, it can be seen that tab1 is getting updated
(internally index ix1 will also get updated as the field timestamp is
being updated).

(5) Now say we have 1000 (one thousand) records satisfying the select
cursor for each station s1, s2, ...., s10.
After the program completes, the timestamp field should be updated for
all these 10,000 (ten thousand) records. But when we query on the
database, we find few records whose timestamp are not getting updated.
Now this number of records not getting updated varies with each run of
the program. Please note that we restore the data back to its original
shape before proceeding with each run.

(6) When we analysed the problem, we found that the select cursor
itself is not returning 1000 records for each station. For some
stations, it was returning less than 1000 records. Then we suspected
that the index ix1 which is getting refreshed on each update might be a
cause of this problem. We dropped the index ix1 and after that the
program started behaving correctly. It returned 1000 rows for each of
the station.

(7) Again to re-assure our suspicion, we created index ix1 again,
restored the data and then ran the program again. As expected, the
program failed. This confirmed our suspicion that the index infact is
creating the problem. Please note that the table is not fragmented and
the total number of records in table tab1 are around 650,000.

I hope the above description clarifies the issue. Kindly let me know if
someone needs any further explanation of the issue.

Would like to know if someone from the Informix community has faced
this problem and can confirm that this is indeed a defect/bug with IDS
9.30.HC2 ?

Regards,
Ravi.

Hi All,

Can somebody please comment on this post ? It seems a genuine defect in
IDS 9.30.HC2.

Regards,
Ravi.