dostats command (was - RE: Invalid procedures.)

Art, Does your dostats command execute the UPDATE STATISTICS on procedures? Since it is not listed in the help, I assume not. Thanks again for this utility. -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Thursday, July 01, 1999 9:23 AM To: informix-list@iiug.org Subject: Re: Invalid procedures. David Casas Rosado wrote: > > Hi all. > > I'm running informix 7.22 on NT4.0 > Some or our developers create new procedures on their users > and this procedures becomes to invalid status. > (It appears some bad entries on the procedure) > > I.e: > create procedure....... > > By the moment, the solution is drop this invalid procedures > and re-create again. > > Anybody knows how can i know what procedures are invalids? > I know that on Oracle i can run a query to the user where > the status procedure's are INVALID. > it this possible on Informix? > > Also, anybody knows why this procedures becomes to invalid > status? What is the error message/code you are seeing. You may just have to reoptimize the procedure. This can happen if you alter the table or its fragmentation or if you run UPDATE STATISTICS on the table. You can fix these problems by running UPDATE STATISTICS FOR PROCEDURE procname; which will cause the optimizer to develop a new query plan for the procedure. You should do this as part of you UPDATE STATISTICS scripts. Art S. Kagel

"Schaeflein, Paul" wrote: > > Art, > > Does your dostats command execute the UPDATE STATISTICS on procedures? > Since it is not listed in the help, I assume not. You apparently have an older version. Stored Procedure support was added in source revision 1.21, features version 4.3 I believe. The current version is source revision 1.25, Features version 4.4. Get an update, a few nice new features like this one and some bug fixes. Also the utils2_ak package includes updated versions of most of its other utilities as well. For example at some point dbdelete.ec was vastly improved in speed you may not have that release; myschema.ec has many new features, several severe bug fixes, includes improved dbschema emulation, added dbschema style table info comment before table definitions which include a CORRECT calculation of index size which will not be fixed in dbschema and dbexport until IDS 7.32, many flags are toggles and can be included in an environment variable (MYSCHEMA) so you can set your own default behavior, and I completed the dbexport/dbimport support (with some help); dbcopy.ec now handles tables with any number of columns without aborting. > Thanks again for this utility. You're welcome. Art S. Kagel > > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Sent: Thursday, July 01, 1999 9:23 AM > To: informix-list@iiug.org > Subject: Re: Invalid procedures. > > David Casas Rosado wrote: > > > > Hi all. > > > > I'm running informix 7.22 on NT4.0 > > Some or our developers create new procedures on their > users > > and this procedures becomes to invalid status. > > (It appears some bad entries on the procedure) > > > > I.e: > > create procedure....... > > > > By the moment, the solution is drop this invalid > procedures > > and re-create again. > > > > Anybody knows how can i know what procedures are invalids? > > I know that on Oracle i can run a query to the user where > > the status procedure's are INVALID. > > it this possible on Informix? > > > > Also, anybody knows why this procedures becomes to invalid > > status? > > What is the error message/code you are seeing. You may just have to > reoptimize the procedure. This can happen if you alter the table or > its fragmentation or if you run UPDATE STATISTICS on the table. You > can fix these problems by running UPDATE STATISTICS FOR PROCEDURE procname; > which will cause the optimizer to develop a new query plan > for the procedure. You should do this as part of you UPDATE STATISTICS > scripts. > > Art S. Kagel