Re: Regarding INFORMIX Basic Details

Sunil Raja said: > Hi, > > Thanks for your suggestions, Had a view at onstat, onconfig file, > sysmaster > tables, systables,... > > Please clarify me the following questions : > > 1. How / Where can i find whether a table/index/... is analyzed or not ? > and > when was it analyzed last ( last date analyzed ) ? What do you mean analysed? > 2. How to analyze a particular table or index or entire schema ? ( Please > explain with example, as got confused with high, low and > medium parameters... ) Download the utils2_ak bundle from iiug.org, and use the dostats utility. > 3. How to run/execute a sql script and view the output in dbaccess ? dbaccess databasename scriptname.sql | more > 4. What are the ways to tune a sql query in INFORMIX ? Huh? > 5. What are the ways to tune a procedure in INFORMIX ? Huh? -- Bye now, Obnoxio "There were a myriad of problems which conspired to corrupt your reason and rob you of your common sense. Fear got the best of you, and in your panic you turned to the Labour Party. They promised you order, they promised you peace, and all they demanded in return was your silent, obedient consent."

Obnoxio The Clown wrote: > Sunil Raja said: >> Hi, >> >> Thanks for your suggestions, Had a view at onstat, onconfig file, >> sysmaster >> tables, systables,... >> >> Please clarify me the following questions : >> >> 1. How / Where can i find whether a table/index/... is analyzed or not ? >> and >> when was it analyzed last ( last date analyzed ) ? > > What do you mean analysed? It's the oracle term for update statistics. You can check the systables record for the table in new versions. In old versions you can only check the sysdistrib table, but this will only have records if you run UPDATE STATISTICS medium/high... (with histograms) > >> 2. How to analyze a particular table or index or entire schema ? ( Please >> explain with example, as got confused with high, low and >> medium parameters... ) > > Download the utils2_ak bundle from iiug.org, and use the dostats utility. > >> 3. How to run/execute a sql script and view the output in dbaccess ? > > dbaccess databasename scriptname.sql | more > >> 4. What are the ways to tune a sql query in INFORMIX ? More or less the same as any other database: Run it, check the query plan, understand if it is the right one an test others (using hints for example) > > Huh? > >> 5. What are the ways to tune a procedure in INFORMIX ? Tune the queries it runs... > > Huh? >

Obnoxio The Clown wrote: >> 5. What are the ways to tune a procedure in INFORMIX ? > > Huh? Most database products contains a profiler that shows which lines are executed, how many times, the amount of time spent executing that line, you know ... a tuning tool. I believe Informix does provide this capability too. <g> -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

Fernando Nunes wrote: >>> 5. What are the ways to tune a procedure in INFORMIX ? > > Tune the queries it runs... > >> Huh? Assuming the poster is from an Oracle background, as you did above, they are asking not about queries and DML but rather constructs such as loops. Each iteration may be insignificant but the composite may take a very long time to execute. Or it may be a single very fast SELECT followed by some very heavy recursive math .... Perhaps you can point them to the best tool available that is built into Informix. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

DA Morgan wrote: > Fernando Nunes wrote: > >>>> 5. What are the ways to tune a procedure in INFORMIX ? >> >> Tune the queries it runs... >> >>> Huh? > > Assuming the poster is from an Oracle background, as you did above, > they are asking not about queries and DML but rather constructs such > as loops. Each iteration may be insignificant but the composite may > take a very long time to execute. Or it may be a single very fast > SELECT followed by some very heavy recursive math .... > > Perhaps you can point them to the best tool available that is built > into Informix. You're back... I'm so happy... After our last interaction I was afraid the fun was gone... Usually in the Informix world, that kind of work is done in 4GL and not in SPL. Regards.

DA Morgan wrote: > Obnoxio The Clown wrote: > >>> 5. What are the ways to tune a procedure in INFORMIX ? >> >> Huh? > > Most database products contains a profiler that shows which lines > are executed, how many times, the amount of time spent executing > that line, you know ... a tuning tool. > > I believe Informix does provide this capability too. <g> Did you ever bother looking at the SPL statements? Do you need to tune them? You can trace them of course... One nice capability that SPL has is the ability to honor the user's role and the privileges it gives the user... <G>

Fernando Nunes wrote: > DA Morgan wrote: >> Fernando Nunes wrote: >> >>>>> 5. What are the ways to tune a procedure in INFORMIX ? >>> >>> Tune the queries it runs... >>> >>>> Huh? >> >> Assuming the poster is from an Oracle background, as you did above, >> they are asking not about queries and DML but rather constructs such >> as loops. Each iteration may be insignificant but the composite may >> take a very long time to execute. Or it may be a single very fast >> SELECT followed by some very heavy recursive math .... >> >> Perhaps you can point them to the best tool available that is built >> into Informix. > > You're back... I'm so happy... After our last interaction I was afraid > the fun was gone... > > Usually in the Informix world, that kind of work is done in 4GL and not > in SPL. > Regards. So your message to the OP is that a similar tool does not exist in Informix? I looked but couldn't find one. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

Fernando Nunes wrote: > DA Morgan wrote: >> Obnoxio The Clown wrote: >> >>>> 5. What are the ways to tune a procedure in INFORMIX ? >>> >>> Huh? >> >> Most database products contains a profiler that shows which lines >> are executed, how many times, the amount of time spent executing >> that line, you know ... a tuning tool. >> >> I believe Informix does provide this capability too. <g> > > Did you ever bother looking at the SPL statements? Do you need to tune > them? > You can trace them of course... > One nice capability that SPL has is the ability to honor the user's role > and the privileges it gives the user... <G> You are correct that some times tuning is not necessary. But when someone asks a question about tuning ... that generally indicates that they have a need to do so. BTW: Honoring the user's role and privilege is something built into each and every one of the top five commercial RDBMS products. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)