Crashes? Could it be this???
On an application we have here at work, the original developers set things up to create a NEW table for each process that is run and the table has ONE row in it. We run 100's of processes per hour. So each hour we have 100's of new tables being created and deleted. We also have problems with this database crashing and becoming majorly corrupt. My GUT LEVEL instinct is that the ONE TABLE with ONE ROW per process is NOT a good thing..... Informix has spent a lot of time making the database bulletproof for ROW insertion/deletion/update. They also make sure that TABLE creation/deletion WORKS.... but my "instinct" would be that they probably DON'T TEST creation/deletion of complete tables on such a scale. I would like to convert this code to have ONE table and a row in it PER process. I think this could solve SOME of the crashes and corruption problems. Anybody have any ideas or experience on this? Or am I just barking up the wrong tree...... Thanks, --keith keith.l.morris@wcom.com
post the af-file. Nona >Subject: Crashes? Could it be this??? >From: Keith L Morris keith.l.morris@wcom.com >Date: 05.07.00 19:22 W. Europe Daylight Time >Message-id: <39636EA5.32794F72@wcom.com> > >On an application we have here at work, the original developers set >things up to create a NEW table for each process that is run and the >table has ONE row in it. > >We run 100's of processes per hour. So each hour we have 100's of new >tables being created and deleted. > >We also have problems with this database crashing and becoming majorly >corrupt. > >My GUT LEVEL instinct is that the ONE TABLE with ONE ROW per process is >NOT a good thing..... Informix has spent a lot of time making the >database bulletproof for ROW insertion/deletion/update. They also make >sure that TABLE creation/deletion WORKS.... but my "instinct" would be >that they probably DON'T TEST creation/deletion of complete tables on >such a scale. > >I would like to convert this code to have ONE table and a row in it >PER process. I think this could solve SOME of the crashes and >corruption problems. > >Anybody have any ideas or experience on this? Or am I just barking up >the wrong tree...... > >Thanks, > >--keith > >keith.l.morris@wcom.com > > > > > > > >
Without having any experience in this, I would say that you've definitely got the right tree. Stop barking and start biting! A create table statement inserts rows all over the place. At least the following tables in the database are affected systables syscolumns sysindexes(possibly) systabauth syscolauth sysconstraints (possibly) sysreferences (possibly) syschecks (possibly) sysdefaults (possibly) sysobjstate (possibly) Then, there's the instance. New extents have to allocated and supporting entries are needed in a number of sysmaster tables. The one row per process idea of yours will definitely be far better. Rudy Keith L Morris wrote: > On an application we have here at work, the original developers set > things up to create a NEW table for each process that is run and the > table has ONE row in it. > > We run 100's of processes per hour. So each hour we have 100's of new > tables being created and deleted. > > We also have problems with this database crashing and becoming majorly > corrupt. > > My GUT LEVEL instinct is that the ONE TABLE with ONE ROW per process is > NOT a good thing..... Informix has spent a lot of time making the > database bulletproof for ROW insertion/deletion/update. They also make > sure that TABLE creation/deletion WORKS.... but my "instinct" would be > that they probably DON'T TEST creation/deletion of complete tables on > such a scale. > > I would like to convert this code to have ONE table and a row in it > PER process. I think this could solve SOME of the crashes and > corruption problems. > > Anybody have any ideas or experience on this? Or am I just barking up > the wrong tree...... > > Thanks, > > --keith > > keith.l.morris@wcom.com
Are these permanent or temporary tables? I'd have thought that temporary table creation/cleanup would have been pretty bombproof. Perhaps using temporary tables would be a 'simple' change you could make to test your theory... Andy In article <39636EA5.32794F72@wcom.com>, Keith L Morris <keith.l.morris@wcom.com> writes >On an application we have here at work, the original developers set >things up to create a NEW table for each process that is run and the >table has ONE row in it. > >We run 100's of processes per hour. So each hour we have 100's of new >tables being created and deleted. > >We also have problems with this database crashing and becoming majorly >corrupt. > >My GUT LEVEL instinct is that the ONE TABLE with ONE ROW per process is >NOT a good thing..... Informix has spent a lot of time making the >database bulletproof for ROW insertion/deletion/update. They also make >sure that TABLE creation/deletion WORKS.... but my "instinct" would be >that they probably DON'T TEST creation/deletion of complete tables on >such a scale. > >I would like to convert this code to have ONE table and a row in it >PER process. I think this could solve SOME of the crashes and >corruption problems. > >Anybody have any ideas or experience on this? Or am I just barking up >the wrong tree...... > >Thanks, > >--keith > >keith.l.morris@wcom.com >