The Scheduler and Update Statistics

IDS 11.5.FC5, AIX 5.3 I am about to start my investigation into the new AUS subsystem with 11.50 and had a few questions I would like to ask. Is anyone currently using the AUS facility to generate and run update statistics? If yes, does the statistic scripts generated by this tool follow the same guideslines as noted within the performance manual and/as noted on the following webpage: http://www-01.ibm.com/support/docview.wss?uid=swg21137764 If you are using it, have you modified its scripts to add RESOLUTION and CONFIDENCE information? If you are not using it, please let me know why. I just started at my current contract last week and do not currently have the ability to review data within the sysadmin database; however, I do know the client is still using command line scripts to perform update statistics at this time. Thanks in advance. Clifton _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/

Yes it works, no it does not follow the full guidelines - close but not complete. Note that the latest version of dostats can generate an update statistics procedure and schedule it to run using the 11.50 task scheduler! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Oct 12, 2009 at 11:37 AM, Clifton Bean <clifton_bean@hotmail.com>wrote: > IDS 11.5.FC5, AIX 5.3 > > I am about to start my investigation into the new AUS subsystem with 11.50 > and > had a few questions I would like to ask. > > Is anyone currently using the AUS facility to generate and run update > statistics? If yes, does the statistic scripts generated by this tool > follow > the same guideslines as noted within the performance manual and/as noted on > the following webpage: > > http://www-01.ibm.com/support/docview.wss?uid=swg21137764 > > If you are using it, have you modified its scripts to add RESOLUTION and > CONFIDENCE information? > > If you are not using it, please let me know why. > > I just started at my current contract last week and do not currently have > the > ability to review data within the sysadmin database; however, I do know the > client is still using command line scripts to perform update statistics at > this time. > > Thanks in advance. > Clifton > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bda28905d200475bf696d

Clifton: Few comments on AUS: 1. AUS has two modes set by the parameter AUS_AUTO_RULES If this is set to 0 then it will repeat only the update stats command previously run. The system catalogs have been updated to track allot of new information about the running of update stats including the confidence and resolution. If this mode is set to 1 then it will repeat the exact commands (i.e. same as mode 0), but it will ensure that the minimum documented recommendations are met. If the user/DBA has specifies higher or the same statistics than the documentation requires AUS will follow the users request. If the user omits or is lower than what is recommended then it will automatically add or upgrade the command to the documented amount. 2. If you have modified the confidences or resolution, AUS will pick this up and build the commands with the confidence and resolution. In fact every command will list the resolution and confidence. If you use the new sample_size parameter then it will be added also. EXAMPLE: UPDATE STATISTICS HIGH FOR TABLE sysutils:syscolumns ( tabid,extended_id ) RESOLUTION 0.500 DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE sysutils:syscolumns (colno)RESOLUTION 2.000 0.950 DISTRIBUTIONS ONLY; UPDATE STATISTICS LOW FOR TABLE sysutils:syscolumns 3. AUS does not do procedures, but a recent change has improved the compilation of stored procedures. Now; when you compile stored procedures, the are compiled in a separate transaction. So you do not have. to wait for the users transaction to commit before see the re-optimized version. More importantly locks are not held for possibly long period of time because IDS waits for the users transaction to commit. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/12/2009 08:37:05 AM: > [image removed] > > The Scheduler and Update Statistics [17481] > > Clifton Bean > > to: > > ids > > 10/12/2009 08:39 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > IDS 11.5.FC5, AIX 5.3 > > I am about to start my investigation into the new AUS subsystem with11.50 and > had a few questions I would like to ask. > > Is anyone currently using the AUS facility to generate and run update > statistics? If yes, does the statistic scripts generated by this tool follow > the same guideslines as noted within the performance manual and/as noted on > the following webpage: > > http://www-01.ibm.com/support/docview.wss?uid=swg21137764 > > If you are using it, have you modified its scripts to add RESOLUTION and > CONFIDENCE information? > > If you are not using it, please let me know why. > > I just started at my current contract last week and do not currentlyhave the > ability to review data within the sysadmin database; however, I do know the > client is still using command line scripts to perform update statistics at > this time. > > Thanks in advance. > Clifton > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >