Re: HELP! Upgrade to V10

There is some issue while creating a view. When the view is created using a table , and when the table is dropped the view created using particular table is permanently dropped. But in case of sql the view is only temporarly dropped , the view gets created automatically when the table is recreated. what would be the solution that can be done in informix as iam working in migrating sql to informix DB.. Thanks and regards Balaji Vinu On 10/3/07, Colin Dawson <cjd_1955@hotmail.com> wrote: > > > There is one critical step missing from a V7 to V9 (prob 10 as well) in > that you should drop/recreate ALL stored procedures. It is not listed in the > migration guide but UK Tech Support said it should be performed as SP's are > handled differently. > > > Regards > > Colin > > There are 10 types of people in the world, those that understand binary > and those that don't > > > > From: art.kagel@gmail.com > > Subject: Re: Fwd: HELP! Upgrade to V10 > > Date: Tue, 2 Oct 2007 16:54:18 +0000 > > To: informix-list@iiug.org > > > > On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > > > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > > > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. > Followed the > > > > > > > migration guide and have updated stats. Performance stinks. > The > > > > > > > database has many synonyms to a remote server, the link is LAN > (100 > > > > > > > Mbs), the migration guide didn't mention synonyms > specifically, any > > > > > > > view on whether it is worth dropping/recreating them?? > > > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > > > > > Update statistics?> > > > > > > > > _______________________________________________ > > > > > > Informix-list mailing list > > > > > > Informix-l...@iiug.org > > > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > > > > Neil > > > > > > > > Thanks, Statistics have been updated (two or three times!) > > > > > > > > Keith > > > > > > > Have you performed "update statistics drop distributions" before > > > > rebuilding the distributions? > > > > > > > What is your update statistics strategy? (i.e. simple "update > > > > statistics" to complex "do_stats")? > > > > > > > _______________________________________________ > > > > Informix-list mailing list > > > > Informix-l...@iiug.org > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > > TBP > > > > > > Thanks for responding > > > Yes, did a drop distrib before rebuild. > > > Strategy is low on each table and medium for the lead column of each > > > index. Pragmatism of old, slow disks and the limited conversion window > > > vs update stats requirements of V10. I will instigate a more thorogh > > > set of stats on a rolling basis. Our queries use some large temp > > > tables and I understand there is a useful Environmental Variable (next > bounce). > > > We have narrowed some of our issues down to Stored Procedures. > > > Dropping and recreating a couple has helped. Are there known issues > > > with bringing SPs forward from 7 to 10? I updated stats on > > > sysprocedures as per Migration Guide but nothing on sysprocplan, which > > > has been ginving a 211:154 ISAM error occasionally. > > > PMR with Marco. > > > > > > Keith > > > > Updating stats on sysprocedures is fine, but irrelevant to your > > problem. You have to recompile all of your stored procedures when you > > update statistics: > > UPDATE STATISTICS FOR PROCEDURE procname;> > > > Certainly a more aggressive update stats protocol. like that > > implemented by dostats, will help a bit, but I think you've found the > > major problem in the SPL. Every procedure prepares a query plan at > > creation/recompilation time in sysprocplan and that plan is out-of- > > date. Since the compiled query plan was created by 7 the IDS 10 > > optimizer may not be recognizing that the query plan is NG so it's not > > forcing a recompile on first execution. This is another detail that > > dostats takes care of for you, BTW. > > > > Art S. Kagel > > > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > > ------------------------------ > Get free emoticon packs and customisation from Windows Live. Pimp My Live!<http://www.pimpmylive.co.uk/>; > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- no me "NO" life know me "KNOW" life

On Oct 4, 2:12 am, "balaji vinu" <balavin...@gmail.com> wrote: > There is some issue while creating a view. > When the view is created using a table , and when the table is dropped the > view created using particular table is permanently dropped. > > But in case of sql the view is only temporarly dropped , the view gets > created automatically when the table is recreated. > > what would be the solution that can be done in informix as iam working in > migrating sql to informix DB.. > > Thanks and regards > Balaji Vinu > > On 10/3/07, Colin Dawson <cjd_1...@hotmail.com> wrote: > > > > > > > There is one critical step missing from a V7 to V9 (prob 10 as well) in > > that you should drop/recreate ALL stored procedures. It is not listed in the > > migration guide but UK Tech Support said it should be performed as SP's are > > handled differently. > > > Regards > > > Colin > > > There are 10 types of people in the world, those that understand binary > > and those that don't > > > > From: art.ka...@gmail.com > > > Subject: Re: Fwd: HELP! Upgrade to V10 > > > Date: Tue, 2 Oct 2007 16:54:18 +0000 > > > To: informix-l...@iiug.org > > > > On Oct 2, 8:45 am, "Keith Simmons" <smile...@googlemail.com> wrote: > > > > On 02/10/2007, TBP <TheBigPota...@nothere.co.uk> wrote: > > > > > > On 2 Oct, 10:01, "Keith Simmons" <smile...@googlemail.com> wrote: > > > > > > On 02/10/2007, Neil Truby <neil.tr...@ardenta.com> wrote: > > > > > > > > "Keith Simmons" <smile...@googlemail.com> wrote in message > > > > > > >news:mailman.173.1191312549.26742.informix-list@iiug.org... > > > > > > > > Was IDS7.31 FD6 on Solaris 5.8, upgraded to IDS 10 FC5. > > Followed the > > > > > > > > migration guide and have updated stats. Performance stinks. > > The > > > > > > > > database has many synonyms to a remote server, the link is LAN > > (100 > > > > > > > > Mbs), the migration guide didn't mention synonyms > > specifically, any > > > > > > > > view on whether it is worth dropping/recreating them?? > > > > > > > > Any other thoughts on this migration? I need help quickly!! > > > > > > > > Update statistics?> > > > > > > > _______________________________________________ > > > > > > > Informix-list mailing list > > > > > > > Informix-l...@iiug.org > > > > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > > > Neil > > > > > > > Thanks, Statistics have been updated (two or three times!) > > > > > > > Keith > > > > > > Have you performed "update statistics drop distributions" before > > > > > rebuilding the distributions? > > > > > > What is your update statistics strategy? (i.e. simple "update > > > > > statistics" to complex "do_stats")? > > > > > > _______________________________________________ > > > > > Informix-list mailing list > > > > > Informix-l...@iiug.org > > > > >http://www.iiug.org/mailman/listinfo/informix-list > > > > > TBP > > > > > Thanks for responding > > > > Yes, did a drop distrib before rebuild. > > > > Strategy is low on each table and medium for the lead column of each > > > > index. Pragmatism of old, slow disks and the limited conversion window > > > > vs update stats requirements of V10. I will instigate a more thorogh > > > > set of stats on a rolling basis. Our queries use some large temp > > > > tables and I understand there is a useful Environmental Variable (next > > bounce). > > > > We have narrowed some of our issues down to Stored Procedures. > > > > Dropping and recreating a couple has helped. Are there known issues > > > > with bringing SPs forward from 7 to 10? I updated stats on > > > > sysprocedures as per Migration Guide but nothing on sysprocplan, which > > > > has been ginving a 211:154 ISAM error occasionally. > > > > PMR with Marco. > > > > > Keith > > > > Updating stats on sysprocedures is fine, but irrelevant to your > > > problem. You have to recompile all of your stored procedures when you > > > update statistics: > > > UPDATE STATISTICS FOR PROCEDURE procname;> > > > Certainly a more aggressive update stats protocol. like that > > > implemented by dostats, will help a bit, but I think you've found the > > > major problem in the SPL. Every procedure prepares a query plan at > > > creation/recompilation time in sysprocplan and that plan is out-of- > > > date. Since the compiled query plan was created by 7 the IDS 10 > > > optimizer may not be recognizing that the query plan is NG so it's not > > > forcing a recompile on first execution. This is another detail that > > > dostats takes care of for you, BTW. > > > > Art S. Kagel > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > > ------------------------------ > > Get free emoticon packs and customisation from Windows Live. Pimp My Live!<http://www.pimpmylive.co.uk/>; > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > -- > no me "NO" life know me "KNOW" life Recreate the view when you create the table. Art S. Kagel