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.