Enable or Apply Auto Update Statistics

Hi All, Here is email communication between Ben and myself. I hope this will help someone who really want to play with AUS feature. -------------------------------------------------------------------------------- ---------------------------------------------------------- Hi Ben, Can you please help me to enable the AUS at my test box? I configured the parameters on 09/07/2015, still all the records/commands state in aus_command.aus_cmd_state shows as “P”=> Pending Please correct me, if I am wrong, I am assuming AUS will be start its work once I enables scheduler task, AUS evaluation & refresh tasks and set the AUS parameters as per requirement. If this is correct, then I am still facing the aus_cmd_state in aus_command as P (Pending). Please suggest. Here are AUS related details on my test box: AUS parameters: AUS_STAT_MODE => 1 STATCHANGE => 0 USTLOW_SAMPLE => 0 AUS_AGE => 7 AUS_PDQ => 10 AUS_CHANGE => 0 AUS_AUTO_RULES => 1 AUS_SMALL_TABLES => 100 AUS evaluation & refresh tasks status SELECT tk_id, tk_name, tk_sequence, tk_next_execution tk_enable FROM ph_task WHERE tk_name like 'Auto Update Statistics%'; tk_id tk_name tk_sequence tk_next_execution tk_enable 54 Auto Update Statistics Evaluation 11 NULL t 55 Auto Update Statistics Refresh 4 NULL t 57 Auto Update Statistics Refresh 2 2 9/7/2015 16:48 t 59 Auto Update Statistics Refresh 3 2 9/7/2015 16:50 t 61 Auto Update Statistics Refresh 4 2 9/8/2015 9:37 t dbScheduler and dbWorker thread statistics onstat –g dbc => Print dbScheduler and dbWorker thread statistics We can get result if executed scheduler task (EXECUTE FUNCTION task("scheduler start");) Else get “Database Scheduler is Disabled.” Message for onstat –g dbc command. $onstat -g dbc IBM Informix Dynamic Server Version 11.70.FC8W1 -- On-Line -- Up 1 days 22:59:02 -- 163228788 KbytesWorker Thread(1) 1b3ac6c4c0 ===================================== Task: 1b3af61028 Task Name: post_alarm_message (20-471497) Task Type: TASK Task Execution: ph_dbs_alert WORKER PROFILE Total Jobs Executed 1434 Sensors Executed 67 Tasks Executed 1367 Purge Requests 1434 Rows Purged 0 Errors 0 Worker Thread(2) 1b3ac6c540 ===================================== Task: 1b3af614c0 Task Name: post_alarm_message (20-471498) Task Type: TASK Task Execution: ph_dbs_alert WORKER PROFILE Total Jobs Executed 1437 Sensors Executed 60 Tasks Executed 1377 Purge Requests 1437 Rows Purged 0 Errors 0 Scheduler Thread 1b39fb6e78 ===================================== Next Task 51 Next Task Waittime 2458 General Queue size 7 full 0 empty 7 Private Queue size 12 full 0 empty 12 PRIVATE QUEUE TASKS Total Jobs Executed 22 Sensors Executed 2 Tasks Executed 20 Purge Requests 22 Rows Purged 0 Errors 0 $ Please let me know, if you need any other details. Thank you. ------------------------------ Thanks & Regards, Pravin Bankar -------------------------------------------------------------------------------- ---------------------------------------------------------- Hi Pravin, If your commands are all pending it is because the refresh job(s) have not run or were not allowed enough time to do anything. Two things stand out here. 1. Your database scheduler should always be running. If it is not, it could be because you have a stop file ($INFORMXDIR/etc/sysadmin/stop). If you do, remove it. Another way to check it's running is 'onstat -g ath | grep dbScheduler'. 2. Your refresh task with tk_id=55 appears not be working (also the evaluation task). There is a bug where just setting tk_enable='t' without changing the times or days of the week does not fire the trigger that updates the next execution times. Try changing the days of the week or times of the jobs: this should set the tk_next_execution date to something in the future. The other dates are all in the past, no idea why. Again changing the days of the week or the time should solve this. Ben Thompson Principal DBA -------------------------------------------------------------------------------- ---------------------------------------------------------- Thanks a lot Ben for valuable information. Just want to clarify for 1st point: There is no stop file in $INFORMXDIR/etc/sysadmin/stop this directory; whereas dbScheduler showing as sleeping state for 'onstat -g ath | grep dbScheduler'. Will this be start running by below commands: EXECUTE FUNCTION task("scheduler shutdown"); EXECUTE FUNCTION task("scheduler start"); Thanks again for your guidance & valuable time. ------------------------------ Thanks & Regards, Pravin Bankar -------------------------------------------------------------------------------- ---------------------------------------------------------- If your commands are all pending it is because the refresh job(s) have not run or were not allowed enough time to do anything. Two things stand out here. 1. Your database scheduler should always be running. If it is not, it could be because you have a stop file ($INFORMXDIR/etc/sysadmin/stop). If you do, remove it. Another way to check it's running is 'onstat -g ath | grep dbScheduler'. 2. Your refresh task with tk_id=55 appears not be working (also the evaluation task). There is a bug where just setting tk_enable='t' without changing the times or days of the week does not fire the trigger that updates the next execution times. Try changing the days of the week or times of the jobs: this should set the tk_next_execution date to something in the future. The other dates are all in the past, no idea why. Again changing the days of the week or the time should solve this. Ben Thompson Principal DBA -------------------------------------------------------------------------------- --------------------------------------------------------- It's ok for it to be sleeping. You can try stopping and starting it. I am pretty sure that it will still be sleeping most of the time after that. Ben Thompson Principal DBA -------------------------------------------------------------------------------- --------------------------------------------------------- Sure Ben. Thanks a lot! Have a great time a head!!! ------------------------------ Thanks & Regards, Pravin Bankar -------------------------------------------------------------------------------- --------------------------------------------------------- Hello Ben, I have tried for stopping & starting scheduler task; but nothing happened. Also I tried for second point for updating the future time in tk_next_execution column by keeping 5 to 10 minutes difference for each Evaluation & Refresh task. Once again I stopped & started scheduler task after updating time & before they start to execute. They executed as per their time mentioned in tk_next_execution column, but once they completed the value became NULL for all the tasks