Re: Strange behaviour of FOREACH (INFORMIX-4GL Version 7.30.HC4P1)

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