After the past couple of days, IBM has added more logs to the fire on the inadequacies of the Informix database product.
Over the past 6 months I have been involved in upgrading several environments from Informix 7.x to Informix 10.0. We went through the normal lifecycle process and did as much regression testing as possible for each application. Between standby and development environments we upgraded roughly eight environments with 20+ databases. The term database in Informix is a little different than Oracle. An Informix database would be similar to an Oracle schema…
Everything was fine until the largest OLTP database was upgraded in production. Although regression testing was completed, not every single possible transaction was tested. As it turns out one transaction that slipped through the cracks would come back to bite us right in the ass and would bring the database instance to its knees.
It turned out to be a complex view that has 15 tables and 18 CASE statements in the definition. When one of the values from the case statement was part of the WHERE clause, the Informix engine would rebuild the entire view into a temporary table and was also shown in the explain plan of the SQL. One of the 15 tables contains over a million rows so every time the problem query ran, it would rebuild the temporary table. The application server would time out but the SQL would continue to run on the database server. The user would get the timeout message and try the same process again which would cause multiple orphaned processes running on the database server and eventually bringing it to it knees. I should note that the same process on the Informix 7.x database would return a result set in under a second. (A good example of where a materialized view would work but it is not available in Informix :-)
I submitted this to IBM support as a problem and after trying a couple of suggestions that did not work, the case was escalated. I received a call from the new person handling the case and he was basically hinting at changing the logic of the process to not use the view or see if it was possible to rewrite the view. If not, he would have to try and build a test environment to reproduce the bug. After some discussion I offered to build a test case as time permits.
I don’t want to get down on Informix support for this because it has more to do with the install base of the Informix software. I believe if this database was in Oracle, we would not have experienced a “BUG” problem. That’s not to say we would not have had problems but any problem found, could be solved without it being a bug fix. The reason for this logic is due to the install base of Oracle compared to Informix. I would compare the upgrade to migrating from Oracle 8i (8.1.3) to Oracle 10g R2. This application in not a real complex system and any bug like symptoms this system would have encountered in 10g would have been found and fixed before we did the migration because Oracle has a larger install base than Informix. Someone else would have found the bug before our application encountered the problem.
Also, related to the install base, is the mass amount of support on the Web. I don’t know how many times I have used Ask Tom, Ixora, ORACLE-BASE, Oracle Technology Network, Metalink and other Web sites to find answers to my questions. Where do you go for Informix? If you dare, you can try comp.database.informix, and maybe, if you are lucky, you will not get a smart ass comment from Obnoxio The Clown. Although I must admit, I do enjoy his/her humor.
Over the past 6 months I have been involved in upgrading several environments from Informix 7.x to Informix 10.0. We went through the normal lifecycle process and did as much regression testing as possible for each application. Between standby and development environments we upgraded roughly eight environments with 20+ databases. The term database in Informix is a little different than Oracle. An Informix database would be similar to an Oracle schema…
Everything was fine until the largest OLTP database was upgraded in production. Although regression testing was completed, not every single possible transaction was tested. As it turns out one transaction that slipped through the cracks would come back to bite us right in the ass and would bring the database instance to its knees.
It turned out to be a complex view that has 15 tables and 18 CASE statements in the definition. When one of the values from the case statement was part of the WHERE clause, the Informix engine would rebuild the entire view into a temporary table and was also shown in the explain plan of the SQL. One of the 15 tables contains over a million rows so every time the problem query ran, it would rebuild the temporary table. The application server would time out but the SQL would continue to run on the database server. The user would get the timeout message and try the same process again which would cause multiple orphaned processes running on the database server and eventually bringing it to it knees. I should note that the same process on the Informix 7.x database would return a result set in under a second. (A good example of where a materialized view would work but it is not available in Informix :-)
I submitted this to IBM support as a problem and after trying a couple of suggestions that did not work, the case was escalated. I received a call from the new person handling the case and he was basically hinting at changing the logic of the process to not use the view or see if it was possible to rewrite the view. If not, he would have to try and build a test environment to reproduce the bug. After some discussion I offered to build a test case as time permits.
I don’t want to get down on Informix support for this because it has more to do with the install base of the Informix software. I believe if this database was in Oracle, we would not have experienced a “BUG” problem. That’s not to say we would not have had problems but any problem found, could be solved without it being a bug fix. The reason for this logic is due to the install base of Oracle compared to Informix. I would compare the upgrade to migrating from Oracle 8i (8.1.3) to Oracle 10g R2. This application in not a real complex system and any bug like symptoms this system would have encountered in 10g would have been found and fixed before we did the migration because Oracle has a larger install base than Informix. Someone else would have found the bug before our application encountered the problem.
Also, related to the install base, is the mass amount of support on the Web. I don’t know how many times I have used Ask Tom, Ixora, ORACLE-BASE, Oracle Technology Network, Metalink and other Web sites to find answers to my questions. Where do you go for Informix? If you dare, you can try comp.database.informix, and maybe, if you are lucky, you will not get a smart ass comment from Obnoxio The Clown. Although I must admit, I do enjoy his/her humor.
Comments