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

"malcolm weallans" <malcolm.iiug@btopenworld.com> wrote in message news:1101839928.hGVdFoXbLhLwufL2tKkOng@teranews... long transaction?

In case long txs are the problem, the fact that the index is already there when the data is loaded would cause a considerable part of the log space used - and will slow down the load time ... In case you don't necessarily need the index at load time (e.g. to ensure uniqueness), consider creating it after the load completed (this will cause very few + small log entries only.) HTH, Andreas malcolm weallans wrote: > 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 data> reloaded. > > 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 no> rows 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). > > From the evidence I concluded that the insert had > started, had populated the database with a number of > rows, and then had failed and done a rollback. This > would rollback the insert but I assume it would not > rollback the additional extents. > > I decided to repeat the portion of script relating to > this particular database but with all outputs being > pass to an error file. And it worked. 67,000 rows of > about 700 bytes row size. > > The dbspace has about 2.6Gbytes of available disk > space. There are no unique indexes. The table has > two not null columns but the values being inserted > always have values in those two columns (they're the > primary keys from two tables). > > I will add logging lines to the script next week to > determine what errors occur, if any. I just wondered > if anybody had any bright ideas as to a possible > cause. > > regards > > Malcolm > sending to informix-list

malcolm weallans wrote: > 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 data> reloaded. Not sure what version you are running but if it is version 7.30 or higher I would suggest using a 1. RAW table to avoid a long transaction 2. locking the table while loading it to prevent you from running out of locks. In order to use a raw table you will need to create the index after the insert. See suggested steps below 0. DATABASE dbname 1. DROP TABLE tab1 2. CREATE RAW TABLE tab1 3. BEGIN WORK 4. LOCK TABLE tab1 in EXCLUSIVE MODE; 5. INSERT INTO tab1 SELECT etc 6. COMMIT WORK 7 ALTER TABLE tab1 TYPE ( STANDARD ) 8 CREATE INDEX ..... on tab1 9 CLOSE DATABASE Hope this helps, John > 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 no> rows 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). > > From the evidence I concluded that the insert had > started, had populated the database with a number of > rows, and then had failed and done a rollback. This > would rollback the insert but I assume it would not > rollback the additional extents. > > I decided to repeat the portion of script relating to > this particular database but with all outputs being > pass to an error file. And it worked. 67,000 rows of > about 700 bytes row size. > > The dbspace has about 2.6Gbytes of available disk > space. There are no unique indexes. The table has > two not null columns but the values being inserted > always have values in those two columns (they're the > primary keys from two tables). > > I will add logging lines to the script next week to > determine what errors occur, if any. I just wondered > if anybody had any bright ideas as to a possible > cause. > > regards > > Malcolm > sending to informix-list