Informix Bug? 1206

I am experiencing an informix bug. I don't if it is a real bug. I expected it to be a bug. Because, there is not definition of the problem I experienced. Sometimes a stored procedures gives an error. Error 1206, But it shouldn't give this error. During investigation of this error, I backup the original stored procedure and re-created it using a differect name. When I run the re- created SPL, I saw that it is run successfully. Vow. Is it possible that a SPL under a different name is error free while as the original program gives an error. Then I drop and re-created the original program again by using it's original SPL name. What I have expected is occured. The re-created program run without giving an error. Does anyone has ever heard a bug like that? Any solution? Idea? Regards, Yener Sent via Deja.com http://www.deja.com/ Before you buy.

This post is akin to: Sometimes my windows is crashing. I rename a file and I have no problems. I name the file back and still no problems. What is making Windows crash? Please post more information. If you think the name of the procedure is causing the problem, a name would be a good thing (if it is named MDY I can already tell you what is wrong). A 1206 has to do with dates. IS your procedure doing any date calculations? Possibly in the SQL it runs as well. Got any version numbers for the engine? yener@my-deja.com wrote: : I am experiencing an informix bug. I don't if it is a real bug. I : expected it to be a bug. Because, there is not definition of the : problem I experienced. : Sometimes a stored procedures gives an error. Error 1206, But it : shouldn't give this error. : During investigation of this error, I backup the original stored : procedure and re-created it using a differect name. When I run the re- : created SPL, I saw that it is run successfully. Vow. Is it possible : that a SPL under a different name is error free while as the original : program gives an error. : Then I drop and re-created the original program again by using it's : original SPL name. What I have expected is occured. The re-created : program run without giving an error. : Does anyone has ever heard a bug like that? Any solution? Idea? : Regards, : Yener : Sent via Deja.com http://www.deja.com/ : Before you buy. -- Rob Wilson rwilson@ntsource.com

Next time it happens try running UPDATE STATISTICS FOR PROCEDURE ...; Sounds like something that the procedure accesses has been altered but the engine did not detect it and auto-update the query plan. It happens to some procedures and not others depending on the code the optimizer MAY or MAY NOT detect that the procedure needs to be recompiled. Art S. Kagel yener@my-deja.com wrote: > > I am experiencing an informix bug. I don't if it is a real bug. I > expected it to be a bug. Because, there is not definition of the > problem I experienced. > > Sometimes a stored procedures gives an error. Error 1206, But it > shouldn't give this error. > > During investigation of this error, I backup the original stored > procedure and re-created it using a differect name. When I run the re- > created SPL, I saw that it is run successfully. Vow. Is it possible > that a SPL under a different name is error free while as the original > program gives an error. > > Then I drop and re-created the original program again by using it's > original SPL name. What I have expected is occured. The re-created > program run without giving an error. > > Does anyone has ever heard a bug like that? Any solution? Idea? > > Regards, > > Yener > > Sent via Deja.com http://www.deja.com/ > Before you buy.

Pre 7.30.UC10/7.31.UC4? date literals within SPL (and fragmentation expressions, and triggers...) use the DBDATE/DBCENTURY of the session running the SP rather than those of the session that created it. check your environment! -- Ciao, Marco ______________________________________________________________________________ Marco Greco <marco@informix.com> Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm "Art S. Kagel" wrote: > > Next time it happens try running UPDATE STATISTICS FOR PROCEDURE ...; > Sounds like something that the procedure accesses has been altered > but the engine did not detect it and auto-update the query plan. It > happens to some procedures and not others depending on the code the > optimizer MAY or MAY NOT detect that the procedure needs to be > recompiled. > > Art S. Kagel > > yener@my-deja.com wrote: > > > > I am experiencing an informix bug. I don't if it is a real bug. I > > expected it to be a bug. Because, there is not definition of the > > problem I experienced. > > > > Sometimes a stored procedures gives an error. Error 1206, But it > > shouldn't give this error. > > > > During investigation of this error, I backup the original stored > > procedure and re-created it using a differect name. When I run the re- > > created SPL, I saw that it is run successfully. Vow. Is it possible > > that a SPL under a different name is error free while as the original > > program gives an error. > > > > Then I drop and re-created the original program again by using it's > > original SPL name. What I have expected is occured. The re-created > > program run without giving an error. > > > > Does anyone has ever heard a bug like that? Any solution? Idea? > > > > Regards, > > > > Yener > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy.

Well, I don't think that the name of the SPL caused the problem. My stored procedure justs selects some records from a join of a table group and returns the result back to the caller. I thought what Ark posted later is the most approxiate action to be done when it happen again. Then we will see the effect of running update statistics. I should have remembered it before. I don't think that the SPL itself doing some bad calculation causing it to give error code 1206. Yener. In article <uOgi5.175$D11.625@newsfeed.slurp.net>, rwilson@ntsource.com (Rob Wilson) wrote: > > This post is akin to: Sometimes my windows is crashing. I rename a file > and I have no problems. I name the file back and still no problems. > What is making Windows crash? > > Please post more information. If you think the name of the procedure is > causing the problem, a name would be a good thing (if it is named MDY I > can already tell you what is wrong). A 1206 has to do with dates. IS > your procedure doing any date calculations? Possibly in the SQL it runs > as well. > > Got any version numbers for the engine? > > yener@my-deja.com wrote: > : I am experiencing an informix bug. I don't if it is a real bug. I > : expected it to be a bug. Because, there is not definition of the > : problem I experienced. > > : Sometimes a stored procedures gives an error. Error 1206, But it > : shouldn't give this error. > > : During investigation of this error, I backup the original stored > : procedure and re-created it using a differect name. When I run the re- > : created SPL, I saw that it is run successfully. Vow. Is it possible > : that a SPL under a different name is error free while as the original > : program gives an error. > > : Then I drop and re-created the original program again by using it's > : original SPL name. What I have expected is occured. The re-created > : program run without giving an error. > > : Does anyone has ever heard a bug like that? Any solution? Idea? > > : Regards, > > : Yener > > : Sent via Deja.com http://www.deja.com/ > : Before you buy. > > -- > Rob Wilson > rwilson@ntsource.com > > Sent via Deja.com http://www.deja.com/ Before you buy.

yener@my-deja.com wrote: > > Well, I don't think that the name of the SPL caused the problem. > My stored procedure justs selects some records from a join of a table > group and returns the result back to the caller. > > I thought what Ark posted later is the most approxiate action to be That's either Art or A.S.K. to you. ;-) Art S. Kagel > done when it happen again. Then we will see the effect of running > update statistics. I should have remembered it before.> > I don't think that the SPL itself doing some bad calculation causing it > to give error code 1206. > > Yener. > > In article <uOgi5.175$D11.625@newsfeed.slurp.net>, > rwilson@ntsource.com (Rob Wilson) wrote: > > > > This post is akin to: Sometimes my windows is crashing. I rename a > file > > and I have no problems. I name the file back and still no problems. > > What is making Windows crash? > > > > Please post more information. If you think the name of the procedure > is > > causing the problem, a name would be a good thing (if it is named MDY > I > > can already tell you what is wrong). A 1206 has to do with dates. IS > > your procedure doing any date calculations? Possibly in the SQL it > runs > > as well. > > > > Got any version numbers for the engine? > > > > yener@my-deja.com wrote: > > : I am experiencing an informix bug. I don't if it is a real bug. I > > : expected it to be a bug. Because, there is not definition of the > > : problem I experienced. > > > > : Sometimes a stored procedures gives an error. Error 1206, But it > > : shouldn't give this error. > > > > : During investigation of this error, I backup the original stored > > : procedure and re-created it using a differect name. When I run the > re- > > : created SPL, I saw that it is run successfully. Vow. Is it possible > > : that a SPL under a different name is error free while as the > original > > : program gives an error. > > > > : Then I drop and re-created the original program again by using it's > > : original SPL name. What I have expected is occured. The re-created > > : program run without giving an error. > > > > : Does anyone has ever heard a bug like that? Any solution? Idea? > > > > : Regards, > > > > : Yener > > > > : Sent via Deja.com http://www.deja.com/ > > : Before you buy. > > > > -- > > Rob Wilson > > rwilson@ntsource.com > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.

rwilson@ntsource.com (Rob Wilson) wrote: > >This post is akin to: Sometimes my windows is crashing. I rename a file >and I have no problems. I name the file back and still no problems. >What is making Windows crash? Windows Haiku: Yesterday it worked. Today it is not working. Windows is like that. ----------------------------------------------------------- Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free! http://www.keen.com

It happens to me so often, I include it within my SPL source code file. Example file "my_procedure.sql". --rollback work; begin work; drop procedure my_procedure; create procedure my_procedure... end procedure commit work; update statistics for procedure my_procedure; "Art S. Kagel" wrote: > Next time it happens try running UPDATE STATISTICS FOR PROCEDURE ...; > Sounds like something that the procedure accesses has been altered > but the engine did not detect it and auto-update the query plan. It > happens to some procedures and not others depending on the code the > optimizer MAY or MAY NOT detect that the procedure needs to be > recompiled. > > Art S. Kagel > > yener@my-deja.com wrote: > > > > I am experiencing an informix bug. I don't if it is a real bug. I > > expected it to be a bug. Because, there is not definition of the > > problem I experienced. > > > > Sometimes a stored procedures gives an error. Error 1206, But it > > shouldn't give this error. > > > > During investigation of this error, I backup the original stored > > procedure and re-created it using a differect name. When I run the re- > > created SPL, I saw that it is run successfully. Vow. Is it possible > > that a SPL under a different name is error free while as the original > > program gives an error. > > > > Then I drop and re-created the original program again by using it's > > original SPL name. What I have expected is occured. The re-created > > program run without giving an error. > > > > Does anyone has ever heard a bug like that? Any solution? Idea? > > > > Regards, > > > > Yener > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy.

Art, It happenned again. When I try to update statistics for procedure, it runs without giving an error. How can I prevent my system to give such nonsense errors. Update statistics for all stored procedures daily basis? Thanks in advance. Yener. In article <3989B83F.EBE1ACD6@bloomberg.net>, kagel@bloomberg.net wrote: > Next time it happens try running UPDATE STATISTICS FOR PROCEDURE ...; > Sounds like something that the procedure accesses has been altered > but the engine did not detect it and auto-update the query plan. It > happens to some procedures and not others depending on the code the > optimizer MAY or MAY NOT detect that the procedure needs to be > recompiled. > > Art S. Kagel > > yener@my-deja.com wrote: > > > > I am experiencing an informix bug. I don't if it is a real bug. I > > expected it to be a bug. Because, there is not definition of the > > problem I experienced. > > > > Sometimes a stored procedures gives an error. Error 1206, But it > > shouldn't give this error. > > > > During investigation of this error, I backup the original stored > > procedure and re-created it using a differect name. When I run the re- > > created SPL, I saw that it is run successfully. Vow. Is it possible > > that a SPL under a different name is error free while as the original > > program gives an error. > > > > Then I drop and re-created the original program again by using it's > > original SPL name. What I have expected is occured. The re-created > > program run without giving an error. > > > > Does anyone has ever heard a bug like that? Any solution? Idea? > > > > Regards, > > > > Yener > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

As Paul suggests besides ALTER TABLE causing an SP to need to be recompiled sometimes UPDATE STATISTICS will cause this. So if you are not using my dostats utility to updates stats (as it already does this for you) add an UPDATE STATISTICS FOR PROCEDURE .... for each stored procedure you have atthe end of your periodic update statistics processing. Art S. Kagel yener@my-deja.com wrote: > > Art, > > It happenned again. When I try to update statistics for procedure, it > runs without giving an error. > > How can I prevent my system to give such nonsense errors. Update > statistics for all stored procedures daily basis? > > Thanks in advance. > > Yener. > > In article <3989B83F.EBE1ACD6@bloomberg.net>, > kagel@bloomberg.net wrote: > > Next time it happens try running UPDATE STATISTICS FOR PROCEDURE ...; > > Sounds like something that the procedure accesses has been altered > > but the engine did not detect it and auto-update the query plan. It > > happens to some procedures and not others depending on the code the > > optimizer MAY or MAY NOT detect that the procedure needs to be > > recompiled. > > > > Art S. Kagel > > > > yener@my-deja.com wrote: > > > > > > I am experiencing an informix bug. I don't if it is a real bug. I > > > expected it to be a bug. Because, there is not definition of the > > > problem I experienced. > > > > > > Sometimes a stored procedures gives an error. Error 1206, But it > > > shouldn't give this error. > > > > > > During investigation of this error, I backup the original stored > > > procedure and re-created it using a differect name. When I run the > re- > > > created SPL, I saw that it is run successfully. Vow. Is it possible > > > that a SPL under a different name is error free while as the > original > > > program gives an error. > > > > > > Then I drop and re-created the original program again by using it's > > > original SPL name. What I have expected is occured. The re-created > > > program run without giving an error. > > > > > > Does anyone has ever heard a bug like that? Any solution? Idea? > > > > > > Regards, > > > > > > Yener > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.