assertion failure during 'alter table'

We have Online ver 7.24. Yes, I know we are WAY behind the times, but we cannot upgrade at the moment. Today one of the programmers tried to add a VARCHAR field to an existing table. When they ran the script it caused an assertion failure and the database hung. I was not able to shut it down. I had to kill the oninit processes, clear up the semaphores, shared memory segments, etc. It was ugly. Once I figured out it was happening because of the alter statement I did some testing. I found several other tables with the same problem. I also found that if I unloaded the table and reloaded it the problem went away. Since we have five online instances and thousands of tables, this solution isn't a good one. We have been using VARCHAR for about 2 years without a problem. It started suddenly. There have been no changes to our system, and in fact the same thing happens on three different computers (production, test and backup). Has anyone had this experience before? We are running on a Siemens-Pyramid box using their flavor of Unix (DC/OSx 1.1) Sent via Deja.com http://www.deja.com/

We have experienced in the past a known problem with 7.24 where ALTER TABLE could cause the database server to abend. Different to your case - it didn't 'hang' - it crashed, asset failed etc. The workaround in this case was to "update statistics low for table <table_to_be_altered> drop distributions;" before the ALTER TABLE. Might be worth a try. Brett Randall jblatz2@my-deja.com wrote: > > We have Online ver 7.24. Yes, I know we are WAY behind the times, but > we cannot upgrade at the moment. > > Today one of the programmers tried to add a VARCHAR field to an existing > table. When they ran the script it caused an assertion failure and the > database hung. I was not able to shut it down. I had to kill the > oninit processes, clear up the semaphores, shared memory segments, etc. > It was ugly. > > Once I figured out it was happening because of the alter statement I did > some testing. I found several other tables with the same problem. I > also found that if I unloaded the table and reloaded it the problem went > away. > > Since we have five online instances and thousands of tables, this > solution isn't a good one. > > We have been using VARCHAR for about 2 years without a problem. It > started suddenly. There have been no changes to our system, and in fact > the same thing happens on three different computers (production, test > and backup). > > Has anyone had this experience before? > > We are running on a Siemens-Pyramid box using their flavor of Unix > (DC/OSx 1.1) > > Sent via Deja.com > http://www.deja.com/

Brett - I will try this, but we update statistics on all tables every night. However I'll try it anyway. Did this happen for any ALTER statement or only when adding a VARCHAR field, which is the case here. Jane In article <3A793CEF.7791C9E0@hotmail.com>, Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote: > We have experienced in the past a known problem with 7.24 where ALTER > TABLE could cause the database server to abend. Different to your case > - it didn't 'hang' - it crashed, asset failed etc. > > The workaround in this case was to "update statistics low for table > <table_to_be_altered> drop distributions;" before the ALTER TABLE. > > Might be worth a try. > > Brett Randall > > jblatz2@my-deja.com wrote: > > > > We have Online ver 7.24. Yes, I know we are WAY behind the times, but > > we cannot upgrade at the moment. > > > > Today one of the programmers tried to add a VARCHAR field to an existing > > table. When they ran the script it caused an assertion failure and the > > database hung. I was not able to shut it down. I had to kill the > > oninit processes, clear up the semaphores, shared memory segments, etc. > > It was ugly. > > > > Once I figured out it was happening because of the alter statement I did > > some testing. I found several other tables with the same problem. I > > also found that if I unloaded the table and reloaded it the problem went > > away. > > > > Since we have five online instances and thousands of tables, this > > solution isn't a good one. > > > > We have been using VARCHAR for about 2 years without a problem. It > > started suddenly. There have been no changes to our system, and in fact > > the same thing happens on three different computers (production, test > > and backup). > > > > Has anyone had this experience before? > > > > We are running on a Siemens-Pyramid box using their flavor of Unix > > (DC/OSx 1.1) > > > > Sent via Deja.com > > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/

Jane The key was not the "update statistics" but rather the "drop distributions" bit. In our case it had nothing to do with varchars - we had none of these in the database in question. Sounds like your problem is something different. Regards Brett jblatz2@my-deja.com wrote: > > Brett - I will try this, but we update statistics on all tables every > night. However I'll try it anyway. > > Did this happen for any ALTER statement or only when adding a VARCHAR > field, which is the case here. > > Jane > > In article <3A793CEF.7791C9E0@hotmail.com>, > Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote: > > We have experienced in the past a known problem with 7.24 where ALTER > > TABLE could cause the database server to abend. Different to your > case > > - it didn't 'hang' - it crashed, asset failed etc. > > > > The workaround in this case was to "update statistics low for table > > <table_to_be_altered> drop distributions;" before the ALTER TABLE. > > > > Might be worth a try. > > > > Brett Randall > > > > jblatz2@my-deja.com wrote: > > > > > > We have Online ver 7.24. Yes, I know we are WAY behind the times, > but > > > we cannot upgrade at the moment. > > > > > > Today one of the programmers tried to add a VARCHAR field to an > existing > > > table. When they ran the script it caused an assertion failure and > the > > > database hung. I was not able to shut it down. I had to kill the > > > oninit processes, clear up the semaphores, shared memory segments, > etc. > > > It was ugly. > > > > > > Once I figured out it was happening because of the alter statement I > did > > > some testing. I found several other tables with the same problem. > I > > > also found that if I unloaded the table and reloaded it the problem > went > > > away. > > > > > > Since we have five online instances and thousands of tables, this > > > solution isn't a good one. > > > > > > We have been using VARCHAR for about 2 years without a problem. It > > > started suddenly. There have been no changes to our system, and in > fact > > > the same thing happens on three different computers (production, > test > > > and backup). > > > > > > Has anyone had this experience before? > > > > > > We are running on a Siemens-Pyramid box using their flavor of Unix > > > (DC/OSx 1.1) > > > > > > Sent via Deja.com > > > http://www.deja.com/ > > > > Sent via Deja.com > http://www.deja.com/

As it turns out this is a "known bug". At least it's known to Informix. Apparently when a DB is converted from version 5.x to 7.2x there can be problems with some, not all, tables when trying to add new columns of VARCHAR, BYTE and TEXT. We just got lucky and picked one of the problem tables. I can export and import the entire DB, which will take much too long, or just avoid new VARCHAR fields unless I can try it on our test system first. Don't you just love it when they tell you it's a "known bug" and your system has crashed! In article <3A7A727C.A31FC13A@hotmail.com>, Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote: > Jane > > The key was not the "update statistics" but rather the "drop > distributions" bit. In our case it had nothing to do with varchars - we > had none of these in the database in question. > > Sounds like your problem is something different. > > Regards > > Brett > > jblatz2@my-deja.com wrote: > > > > Brett - I will try this, but we update statistics on all tables every > > night. However I'll try it anyway. > > > > Did this happen for any ALTER statement or only when adding a VARCHAR > > field, which is the case here. > > > > Jane > > > > In article <3A793CEF.7791C9E0@hotmail.com>, > > Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote: > > > We have experienced in the past a known problem with 7.24 where ALTER > > > TABLE could cause the database server to abend. Different to your > > case > > > - it didn't 'hang' - it crashed, asset failed etc. > > > > > > The workaround in this case was to "update statistics low for table > > > <table_to_be_altered> drop distributions;" before the ALTER TABLE. > > > > > > Might be worth a try. > > > > > > Brett Randall > > > > > > jblatz2@my-deja.com wrote: > > > > > > > > We have Online ver 7.24. Yes, I know we are WAY behind the times, > > but > > > > we cannot upgrade at the moment. > > > > > > > > Today one of the programmers tried to add a VARCHAR field to an > > existing > > > > table. When they ran the script it caused an assertion failure and > > the > > > > database hung. I was not able to shut it down. I had to kill the > > > > oninit processes, clear up the semaphores, shared memory segments, > > etc. > > > > It was ugly. > > > > > > > > Once I figured out it was happening because of the alter statement I > > did > > > > some testing. I found several other tables with the same problem. > > I > > > > also found that if I unloaded the table and reloaded it the problem > > went > > > > away. > > > > > > > > Since we have five online instances and thousands of tables, this > > > > solution isn't a good one. > > > > > > > > We have been using VARCHAR for about 2 years without a problem. It > > > > started suddenly. There have been no changes to our system, and in > > fact > > > > the same thing happens on three different computers (production, > > test > > > > and backup). > > > > > > > > Has anyone had this experience before? > > > > > > > > We are running on a Siemens-Pyramid box using their flavor of Unix > > > > (DC/OSx 1.1) > > > > > > > > Sent via Deja.com > > > > http://www.deja.com/ > > > > > > > Sent via Deja.com > > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/