Creation of missing AUS related tables in sysadmin

Hi All, I want to implement the AUS (Auto Update Stats) feature for my Informix production instance. But before implementing on production box, I need to test it at the test instance. When I gone through AUS related documentation & referred the sysadmin database then found the below AUS related tables are missing (not present) from sysadmin database. AUS related tables: 1) aus_command, 2) aus_cmd_info, 3) aus_cmd_list, 4) aus_cmd_comp, 5) aus_work_dist, 6) aus_work_info, 7) aus_work_icols I have tried to re-create sysadmin database using below two ways, but AUS related missing table not get created: A) Reference: http://www.iiug.org/forums/ids/index.cgi/read/19053 Login as userid Informix dbaccess sysadmin EXECUTE FUNCTION task("scheduler shutdown"); -- Stop the scheduler threads EXECUTE FUNCTION admin("reset sysadmin"); -- Drop & recreate sysadmin EXECUTE FUNCTION task("scheduler start"); B) Reference: http://www-01.ibm.com/support/docview.wss?uid=swg21420189 Question: How do you rebuild sysadmin database manually without having to restart IDS? Answer: Run these steps to rebuild sysadmin database manually: cd $INFORMIXDIR/etc/sysadmin; dbaccess - db_uninstall.sql; dbaccess - db_create.sql; dbaccess sysadmin db_install.sql; dbaccess sysadmin sch_tasks.sql; dbaccess sysadmin sch_aus.sql; dbaccess sysadmin sch_sqlcap.sql; dbaccess sysadmin start.sql; I also came across the below AUS related SPLs (PROCEDURES & FUNCTIONS). When I executed aus_setup_table() function I able to get the AUS related tables. But I need to know, is it correct way to get the missing AUS related tables? Is any other function(s) needs to execute? Also what are the parameters I need to use for each functions? AUS related SPLs: 1) aus_cleanup_table 2) aus_create_cmd_dist 3) aus_enable_refresh 4) aus_evaluate_stats 5) aus_evaluator 6) aus_evaluator 7) aus_evaluator 8) aus_evaluator_dbs 9) aus_evaluator_downgrade 10) aus_evaluator_upgrade 11) aus_get_exclusive_access 12) aus_get_realtime 13) aus_load_dbs_data 14) aus_refresh_downggrade 15) aus_refresh_stats 16) aus_refresh_stats 17) aus_refresh_stats_orig 18) aus_refresh_upgrade 19) aus_rel_exclusive_access 20) aus_setup_mon_table_profile 21) aus_setup_table The main purpose is to use AUS feature & apply on Informix instances. Any help will be really appreciated. Thanks in advance. ~ Pravin Bankar pravinebankar@gmail.com

Hi Pravin, It's a good approach to check the pre-requistes first but have you tried running the evaluator task on your test system? I am pretty sure that most of the tables and views you mention are only created when the task is run for the first time. Ben.

Thank you Ben for your response. Can you please confirm, you are referring 'evaluator task'; do you mean $INFORMIXDIR/etc/sysadmin/sch_aus.sql script or one of below SPLs: aus_evaluate_stats aus_evaluator aus_evaluator aus_evaluator aus_evaluator_dbs aus_evaluator_downgrade aus_evaluator_upgrade Or any other script/FUNCTION? ~Pravin Bankar pravinebankar@gmail.com

Hi Pravin, I have blogged on this subject twice:: https://informixdba.wordpress.com/2013/05/31/working-with-auto-update-stats/ https://informixdba.wordpress.com/2015/04/30/experience-with-auto-update-statist ics-aus/ These provide some additional information to the manual on the subject. However neither blog gives the bit of information you are looking for. To see the scheduler jobs related to Auto Update Statistics: database sysadmin; select tk_name, tk_enable from ph_task where tk_name like 'Auto UpdateStatistics %'; Then to run the evaluator: execute function exectask('Auto Update Statistics Evaluation'); You can also use OAT to do this. The evaluator doesn't update any statistics but it will place a list of commands to run in the aus_command table and create the views. You run the refresh task to actually do the work. Ben.

Thanks a lot Ben! I already gone through your blogs & those helped me a lot. I think below points will help me for my current issue: ------------------------------------------------------------------------ To see the scheduler jobs related to Auto Update Statistics: database sysadmin; select tk_name, tk_enable from ph_task where tk_name like 'Auto UpdateStatistics %'; Then to run the evaluator: execute function exectask('Auto Update Statistics Evaluation');------------------------------------------------------------------------ If I face any other queries, will contact you. Thank you! ~Pravin Bankar pravinebankar@gmail.com

Sorry for late response but I was busy with other priority projects. Thanks a lot Ben for your timely guidance on AUS. The exectask() function helped me to get the missing AUS related tables. execute function exectask('Auto Update Statistics Evaluation'); Thanks & Regards, Pravin Bankar