Upgrade 7.31 to 10.0 with no update statistics
Poster asked whether a 7.31-to-10.0 upgrade could be done on a near-24x7 Verizon system without running full UPDATE STATISTICS during the outage, deferring them to later in the week with the app running. Respondents said this is workable and shared practice: run UPDATE STATISTICS DROP DISTRIBUTIONS before shutdown (Art Kagel suggested dostats -L -d to avoid resource/filesystem overload), do onchecks and a final archive, then afterwards use 'medium ... distributions only' plus a 'low' pass, with PDQPRIORITY set. Warnings included in-place altered tables (fix by dummy updates or unload/rebuild), /tmp and rootdbs/physical log space on large systems, a long apparent hang mid-upgrade, and following the Migration Guide with a sandbox rehearsal. No follow-up from the poster on the outcome.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have an application that can not cope with much downtime and plan to upgrade to 10.0 without a full running statistics on the upgrade night, instead run them with the application active later in the week (then cycle the app processes to take in the new stats). We will be running update stats against system tables/sysmasters as per upgrade documentation. Does anyone have experience of something similar and did you have any problem or issues ? IBM Informix says we can do this but we/they are not as 100% on this as we would like. The system we are playing with is as critical as it gets within Verizon at the moment so all input/experiences is welcome! Geoff Poole Verizon
We have done this recently. Before you shutdown for the upgrade, run update stats drop distributions. You will have run a full set of onchecks and done a final archive - the upgrade will fail if there is any error. Become familiar with the Migration Guide and take care to do all the steps relevant to you. MW -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of geoff.poole.... Sent: Friday, 6 January 2006 8:59 a.m. To: ids@iiug.org Subject: Upgrade 7.31 to 10.0 with no update statistics [6174] We have an application that can not cope with much downtime and plan to upgrade to 10.0 without a full running statistics on the upgrade night, instead run them with the application active later in the week (then cycle the app processes to take in the new stats). We will be running update stats against system tables/sysmasters as per upgrade documentation. Does anyone have experience of something similar and did you have any problem or issues ? IBM Informix says we can do this but we/they are not as 100% on this as we would like. The system we are playing with is as critical as it gets within Verizon at the moment so all input/experiences is welcome! Geoff Poole Verizon **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
I don't know at what level you plan on running update statistics but you may want to do "update statistics medium for table <tabname> distributions only;" for each table. Using the "distributions only" phrase restricts update stats to only rebuild the table distribution data set in the sysdistrib table. This is usually pretty fast. I then run "update statistics low;" on the database so it will clear any deleted index entries. I also set pdqpriority. This does not generate parallel scan threads but I believe it will allow access to DS memory. Thanks, Bob Kruse MCI -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of geoff.poole.... Sent: Thursday, January 05, 2006 12:59 PM To: ids@iiug.org Subject: Upgrade 7.31 to 10.0 with no update statistics [6174] We have an application that can not cope with much downtime and plan to upgrade to 10.0 without a full running statistics on the upgrade night, instead run them with the application active later in the week (then cycle the app processes to take in the new stats). We will be running update stats against system tables/sysmasters as per upgrade documentation. Does anyone have experience of something similar and did you have any problem or issues ? IBM Informix says we can do this but we/they are not as 100% on this as we would like. The system we are playing with is as critical as it gets within Verizon at the moment so all input/experiences is welcome! Geoff Poole Verizon **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
Just to let you know, we upgraded from 9.40 (RH Linux AS3) and there was a frightening 30-min period in the middle of the upgrade where NOTHING happens! No abnormal cpu activity and no disk activity (~150GB db). Then it just took off again and completed the upgrade. And I agree with MW; pay attention to that Migration Guide. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com 586.741.8991 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of geoff.poole.... Sent: Thursday, January 05, 2006 2:59 PM To: ids@iiug.org Subject: Upgrade 7.31 to 10.0 with no update statistics [6174] We have an application that can not cope with much downtime and plan to upgrade to 10.0 without a full running statistics on the upgrade night, instead run them with the application active later in the week (then cycle the app processes to take in the new stats). We will be running update stats against system tables/sysmasters as per upgrade documentation. Does anyone have experience of something similar and did you have any problem or issues ? IBM Informix says we can do this but we/they are not as 100% on this as we would like. The system we are playing with is as critical as it gets within Verizon at the moment so all input/experiences is welcome! Geoff Poole Verizon ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
On Thu, 2006-01-05 at 14:59 -0500, geoff.poole.... wrote: > We have an application that can not cope with much downtime and plan to > upgrade to 10.0 without a full running statistics on the upgrade night, > instead run them with the application active later in the week (then cycle > the app processes to take in the new stats). We will be running update > stats against system tables/sysmasters as per upgrade documentation. > > Does anyone have experience of something similar and did you have any > problem or issues ? IBM Informix says we can do this but we/they are not as > 100% on this as we would like. > > The system we are playing with is as critical as it gets within Verizon at > the moment so all input/experiences is welcome! In our migration from 7.3 to 9.3 we ran into a problem with inplace alters. We had unload, drop and rebuild the tables with that had inplace alters before migration could proceed. You may want to verify whether this issue is still valid for your migration. I'll also add my voice to the chorus: Be familiar with the migration guide. Have your scenarios ready. Do a rehearsal on a sandbox instance. These were invaluable to us. DT -- Dave Thacker, Senior Systems Administrator Omni Hotels Reservation Center V: 402.952.6535 F: 402-334-8013 M: 402-981-4613 (24/7)
Hi, We were prepared for the inplace altered tables by our reseller (when updating to 9.4, should be the same for 10.0). I ran a job running over every table doing an "update table set column = column where 1=1;" in many transactions (like 1000 rows in one Transaction) with a short program. Informix will then reconstruct each row internally to be continuous. I cannot really remember if it was a problem only when upgrading from 9.x or also from 7.x. On each db, execute "update statistics drop distributions". We had a very large DB to migrate and there was a problem that filesystem ran full in /tmp at the first try. That was a problem because we had very many tables (>200000) and all the table headers were reconfigured to support large chunks (>2GB). Sorry, I cannot remember how we fixed this. I think it had to do something with space in rootdbs and physical log. Also possible: tempdbs. Read the migration notes from 9.40. I think these can be of help, because they were addressing the problem of chunk size migration. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dave Thacker Sent: Friday, January 06, 2006 5:22 PM To: ids@iiug.org Subject: Re: Upgrade 7.31 to 10.0 with no update statistics [6178] On Thu, 2006-01-05 at 14:59 -0500, geoff.poole.... wrote: > We have an application that can not cope with much downtime and plan > to upgrade to 10.0 without a full running statistics on the upgrade > night, instead run them with the application active later in the week > (then cycle the app processes to take in the new stats). We will be > running update stats against system tables/sysmasters as per upgrade documentation. > > Does anyone have experience of something similar and did you have any > problem or issues ? IBM Informix says we can do this but we/they are > not as 100% on this as we would like. > > The system we are playing with is as critical as it gets within > Verizon at the moment so all input/experiences is welcome! In our migration from 7.3 to 9.3 we ran into a problem with inplace alters. We had unload, drop and rebuild the tables with that had inplace alters before migration could proceed. You may want to verify whether this issue is still valid for your migration. I'll also add my voice to the chorus: Be familiar with the migration guide. Have your scenarios ready. Do a rehearsal on a sandbox instance. These were invaluable to us. DT -- Dave Thacker, Senior Systems Administrator Omni Hotels Reservation Center V: 402.952.6535 F: 402-334-8013 M: 402-981-4613 (24/7) **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
You can perform the DROP DISTRIBUTIONS without overloading server or filesystem resources using 'dostats -L -d <databasename>' Art S. Kagel ----- Original Message ----- From: Marcus Haarmann <ids@iiug.org> At: 1/ 7 7:32 Hi, We were prepared for the inplace altered tables by our reseller (when updating to 9.4, should be the same for 10.0). I ran a job running over every table doing an "update table set column = column where 1=1;" in many transactions (like 1000 rows in one Transaction) with a short program. Informix will then reconstruct each row internally to be continuous. I cannot really remember if it was a problem only when upgrading from 9.x or also from 7.x. On each db, execute "update statistics drop distributions". We had a very large DB to migrate and there was a problem that filesystem ran full in /tmp at the first try. That was a problem because we had very many tables (>200000) and all the table headers were reconfigured to support large chunks (>2GB). Sorry, I cannot remember how we fixed this. I think it had to do something with space in rootdbs and physical log. Also possible: tempdbs. Read the migration notes from 9.40. I think these can be of help, because they were addressing the problem of chunk size migration. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Dave Thacker Sent: Friday, January 06, 2006 5:22 PM To: ids@iiug.org Subject: Re: Upgrade 7.31 to 10.0 with no update statistics [6178] On Thu, 2006-01-05 at 14:59 -0500, geoff.poole.... wrote: > We have an application that can not cope with much downtime and plan > to upgrade to 10.0 without a full running statistics on the upgrade > night, instead run them with the application active later in the week > (then cycle the app processes to take in the new stats). We will be > running update stats against system tables/sysmasters as per upgrade documentation. > > Does anyone have experience of something similar and did you have any > problem or issues ? IBM Informix says we can do this but we/they are > not as 100% on this as we would like. > > The system we are playing with is as critical as it gets within > Verizon at the moment so all input/experiences is welcome! In our migration from 7.3 to 9.3 we ran into a problem with inplace alters. We had unload, drop and rebuild the tables with that had inplace alters before migration could proceed. You may want to verify whether this issue is still valid for your migration. I'll also add my voice to the chorus: Be familiar with the migration guide. Have your scenarios ready. Do a rehearsal on a sandbox instance. These were invaluable to us. DT -- Dave Thacker, Senior Systems Administrator Omni Hotels Reservation Center V: 402.952.6535 F: 402-334-8013 M: 402-981-4613 (24/7) **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.