[Q] update statistics for newbie???

We have Informix Dymanic server on DELL LINUX server and Informix version is 10UC4. Due to DBA out of town for couple weeks I need temporary take care the database. I have some questions relate to "update statistics" need help. we run "update statistics" batch job to update around 1300 tables every night. It take two hours or longer to finish. My questions: 1. Do we need run "update statistics" evry night or not? 2. any way to make "update statistics" run faster? 3. if during "update statistics" and another batch job shutdown database, will it cause any problem or not? 4. another suggestion? Thanks.

aaa wrote: > We have Informix Dymanic server on DELL LINUX server and Informix version is > 10UC4. > > Due to DBA out of town for couple weeks I need temporary take care the database. > I have some questions relate to "update statistics" need help. > > we run "update statistics" batch job to update around 1300 tables every night. > It take two hours or longer to finish. > > My questions: > > 1. Do we need run "update statistics" evry night or not? > Maybe. Depends on how volatile your tables and keys are. > 2. any way to make "update statistics" run faster? > Maybe. That depends on how you are running it now. Are you running it at the database level, at the table level, running multiple statements per table to optimize runtime as recommended in the Performance Guide and in John Miller IIIs white paper on the subject (http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html)? Are you using my dostats utility? Are you using PSORT_NPROCS, PDQPRIORITY, and MGM memory for efficient sorting during the runs? > 3. if during "update statistics" and another batch job shutdown database, will > it cause any problem or not? > It could. Depends on what the udpate stats was doing at the time. The definitive fix would be to rerun the update stats after the server is back online. > 4. another suggestion? > Yes. Get my dostats utility. It automates the process using the recommendations in the manuals and white papers, adjusts the actual statements it issues depending on the server version to take best advantage of new features (see John's paper for details). Dostats has options (specifically -a/-A and -b/-B) which will minimize the number of tables which are updated each night. The package that contains dostats (utils2_ak) also contains drive_dostats a script that can run N copies of dostats against subsets of your tables so that several tables can be updated in parallel to minimize the run time if you have the hardware resources to make this efficient. Dostats is part of the package utils2_ak which you can download free from the International Informix Users Group (IIUG) web site's Software Repository (www.iiug.org/software) or from the Oninit web site (www.oninit.com). Dostats is used at hundreds of Informix sites worldwide and has become the standard for running update statistics. Art S. Kagel Oninit > > Thanks. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > >

One more question: Can I run "update statistics" while other users do "DDL" like insert, uopdate , delet? Or I need wait for No one there to run "update statistics"? Thanks. In article <mailman.928.1207839771.20610.informix-list@iiug.org>, Oninit says... > >aaa wrote: >> We have Informix Dymanic server on DELL LINUX server and Informix version is >> 10UC4. >> >>Due to DBA out of town for couple weeks I need temporary take care the database. >> I have some questions relate to "update statistics" need help. >> >>we run "update statistics" batch job to update around 1300 tables every night. >> It take two hours or longer to finish. >> >> My questions: >> >> 1. Do we need run "update statistics" evry night or not? >> > >Maybe. Depends on how volatile your tables and keys are. > >> 2. any way to make "update statistics" run faster? >> > >Maybe. That depends on how you are running it now. Are you running it >at the database level, at the table level, running multiple statements >per table to optimize runtime as recommended in the Performance Guide >and in John Miller IIIs white paper on the subject >(http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html)? >Are you using my dostats utility? Are you using PSORT_NPROCS, >PDQPRIORITY, and MGM memory for efficient sorting during the runs? > >>3. if during "update statistics" and another batch job shutdown database, will >> it cause any problem or not? >> > >It could. Depends on what the udpate stats was doing at the time. The >definitive fix would be to rerun the update stats after the server is >back online. > >> 4. another suggestion? >> > >Yes. Get my dostats utility. It automates the process using the >recommendations in the manuals and white papers, adjusts the actual >statements it issues depending on the server version to take best >advantage of new features (see John's paper for details). Dostats has >options (specifically -a/-A and -b/-B) which will minimize the number of >tables which are updated each night. The package that contains dostats >(utils2_ak) also contains drive_dostats a script that can run N copies >of dostats against subsets of your tables so that several tables can be >updated in parallel to minimize the run time if you have the hardware >resources to make this efficient. Dostats is part of the package >utils2_ak which you can download free from the International Informix >Users Group (IIUG) web site's Software Repository >(www.iiug.org/software) or from the Oninit web site (www.oninit.com). >Dostats is used at hundreds of Informix sites worldwide and has become >the standard for running update statistics. > >Art S. Kagel >Oninit > >> >> Thanks. >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> >> >> > >

aaa wrote: > One more question: > > Can I run "update statistics" while other users do "DDL" like insert, uopdate , > delet? Or I need wait for No one there to run "update statistics"? > That would actually qualify as DML (Data Manipulation Language) rather than DDL (Data Definition Language), but to answer your question: No problem. Update statistics only takes a momentary lock on individual records in the system catalog tables systables, syscolumns, sysindexes, and sysdistrib to update them. Data pages are read without locking. User sessions running with LOCK MODE WAIT <nsec>; will not even notice. Users running with LOCK MODE NOT WAIT; (the default) may experience occassional lock errors. The only other problem will be if there are prepared statements running against the tables being updated those statements may return a -710 error after the stats update completes if the new statistics invalidate the prepared statements' original query plan. Also the first execution of any stored procedure or function that references one of the updated tables will cause it to be recompiled. Sometimes this returns an error to the caller, but a second attempt to execute the routine will succeed (dostats recompiles all stored procedures when run against a full database at the end of the process to prevent this last problem). Art S. Kagel Oninit > Thanks. > > > > > In article <mailman.928.1207839771.20610.informix-list@iiug.org>, Oninit says... > >> aaa wrote: >> >>> We have Informix Dymanic server on DELL LINUX server and Informix version is >>> 10UC4. >>> >>> Due to DBA out of town for couple weeks I need temporary take care the database. >>> I have some questions relate to "update statistics" need help. >>> >>> we run "update statistics" batch job to update around 1300 tables every night. >>> It take two hours or longer to finish. >>> >>> My questions: >>> >>> 1. Do we need run "update statistics" evry night or not? >>> >>> >> Maybe. Depends on how volatile your tables and keys are. >> >> >>> 2. any way to make "update statistics" run faster? >>> >>> >> Maybe. That depends on how you are running it now. Are you running it >> at the database level, at the table level, running multiple statements >> per table to optimize runtime as recommended in the Performance Guide >> and in John Miller IIIs white paper on the subject >> (http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html)? >> Are you using my dostats utility? Are you using PSORT_NPROCS, >> PDQPRIORITY, and MGM memory for efficient sorting during the runs? >> >> >>> 3. if during "update statistics" and another batch job shutdown database, will >>> it cause any problem or not? >>> >>> >> It could. Depends on what the udpate stats was doing at the time. The >> definitive fix would be to rerun the update stats after the server is >> back online. >> >> >>> 4. another suggestion? >>> >>> >> Yes. Get my dostats utility. It automates the process using the >> recommendations in the manuals and white papers, adjusts the actual >> statements it issues depending on the server version to take best >> advantage of new features (see John's paper for details). Dostats has >> options (specifically -a/-A and -b/-B) which will minimize the number of >> tables which are updated each night. The package that contains dostats >> (utils2_ak) also contains drive_dostats a script that can run N copies >> of dostats against subsets of your tables so that several tables can be >> updated in parallel to minimize the run time if you have the hardware >> resources to make this efficient. Dostats is part of the package >> utils2_ak which you can download free from the International Informix >> Users Group (IIUG) web site's Software Repository >> (www.iiug.org/software) or from the Oninit web site (www.oninit.com). >> Dostats is used at hundreds of Informix sites worldwide and has become >> the standard for running update statistics. >> >> Art S. Kagel >> Oninit >> >> >>> Thanks. >>> >>> _______________________________________________ >>> Informix-list mailing list >>> Informix-list@iiug.org >>> http://www.iiug.org/mailman/listinfo/informix-list >>> >>> >>> >>> >> > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > >