RE: What could make insert fail?

If it only started failing recently, perhaps you are running out of locks or logs (the transaction is getting too big ?) -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of malcolm weallans Sent: Tuesday, November 30, 2004 6:17 PM To: informix-list@iiug.org Subject: What could make insert fail? I am trying to compile a full list of the reasons why an INSERT INTO tab1 SELECT ... FROM tab2,tab3, tab4, OUTER tab5 WHERE .....etc might fail. Let me paint you the scenario. We have a system with about 20 identically structured databases. For the past 4 or 5 weeks a weekly routine that extracts data from tables in the database and produces a summary table for use with 3rd party analysis tools has failed repeatedly on one and only one of the databases and the same database every week. Needless to say the script that runs this passes all errors to /dev/null. The initial conclusion was that it was a space problem and as a migration was imminent they left the problem until after the migration when there would be much more disk space available. But we did the migration last weekend and the problem has migrated as well. We migrated all of the data by unload to ascii, tables were recreated, and the datareloaded. The sequence of activities performed weekly is 0. DATABASE dbname 1. DROP TABLE tab1 2. CREATE TABLE tab1 3. CREATE INDEX .... on tab1 4. INSERT INTO tab1 SELECT etc 5. CLOSE DATABASE When I started investigating this morning I did an oncheck -pt dbname:tab1 and found that there were norows in the table but about 12 extents had been created. I also noticed that the indexes also had multiple extents. (extent size is not set anywhere, nor is LOCK MODE).