SE on DG-UX 5.4 R 2.01

Subject INFORMIX Problems The serious problems we are having with our Informix is on a Data General AViiON AV4605 running DG/UX Version 5.4R2.01. Below are listed the INFORMIX products we have with their release numbers. SQL 4.11.UC2 SE 5.01.UD2 R4GL 4.11.UC2 The problems we have seen are 1. A select statement which ran fine decided to go wild. With SET EXPLAIN ON the estimated cost of rows on the select was several hundred thousand. The database only had about 20,000 rows in it. The SQLEXEC did not die when the program was killed and carried on running wild until killed. After running an UPDATE STATISTICS the problem totally disappeared. After BCHECKing all the tables there were no corruptions. 2. An R4GL program that LOCKED EXCLUSIVE all the tables it used and only inserted into one of those tables crashed with Lock errors even though the table was locked. The crash occurred after 5,000 plus records were inserted. The program would crash but again the SQLEXEC would carry on running grabbing processing time and leaving all the tables in a LOCKED state. After killing the SQLEXEC the table was always then corrupted badly. By taking the insert out of a BEGIN work allowed the program to work OK . This problem could also be demonstrated by simply DELETING many rows from a table within ISQL. The number of UNIX locks was put up to 30,000 and the problem has gone away - for the moment. 3. A standard select statement of a few hundred rows was working fine. It then started to go wild. It does an order by but started to create 200 MB sort tables and eventually crashes on lack of disc space. The number of rows it selects is about 400 and the select is based on an 8 table select with one table having a self join. The order by has 7 char columns and one integer. This problem can be demonstrated within ISQL. If the number of tables selected is reduced to 5 plus one self join and the order by reduced to 6 columns the select will work within ISQL. But when run from within an R4GL program it will run but having processed all the rows will then continue to run until again a file gets so large that the disc space blows out. By running a DBEXPORT and DBIMPORT the problem may go away. However on one occasion this worked with in ISQL until an unrelated DELETE of a row from a different table altogether was run. When the SELECT was run again it again went wild. BCHECKing shows no errors on any tables in the database. 4. On more than one occasion we have gone into ISQL to run various SELECTS, UNLOADS and DROP tables. Despite never starting a BEGIN WORK upon leaving ISQL it thought it was in a transaction and asked us to COMMIT or ROLLBACK. I hope some of these problems will be familiar to someone so we can get them quickly solved. It is my personal opinion that we have contention between our UNIX and INFORMIX or a poor port of INFORMIX. This particular application runs very well in our London office on an AV4605 and AV4625 running DG-UX 5.4.2, and runs fine in New York on SUN Systems. Pls help.... Pls email reply to jesskung@solomon.technet.sg.... PS : We had upgraded from version 5.00 for SE to 5.01. For the front end, we hhad upgraded from 4.10 to 4.11. However, problems still persists. Thanks in advance. Regards, Jessica