Automated Update Statistics and FORCE

Hi everyone. AUS seems to append FORCE to all UPDATE STATISTICS statements saved in sysadmin:aus_command.aus_cmd_exe on some systems and not others. Why is that so, and how can it be enabled or disabled? We have a site where an exceptional table is excluded by altering STATCHANGE to a large percentage. I will raise a case with IBM if still necessary, and share any answers. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Hi Doug, There are two versions of the AUS Evaluator (and also the AUS Refresh job). The original one is written in SPL and in sysadmin:ph_task table its tk_execute value is aus_evaluator . This one doesn't add the FORCE keyword to statements. At some point (maybe with 14.10) a new version was introduced, implemented as a C UDR. It is functionally slightly different and uses the FORCE keywork liberally. Its tk_execute value is aus_evaluate_stats . I would be interested to hear if this is why you see differences. There is a procedure to call to switch between them. Let me know if you need it and I will dig it out. I am not sure why the FORCE keyword was added. We have observed that without it tables with STATCHANGE 0 do not always see statistics and distributions updated even when there have been some changes: maybe this is a bug. Ben. ------------------------------ Benjamin Thompson ------------------------------

Thanks, Ben - that's exactly it. I had forgotten that we executed "aus_evaluator_downgrade" at the site where it's not using FORCE two years ago for the opposite reason: it was missing out some tables before, and worked fine afterwards. That setting change survived subsequent IDS 14 upgrades. The details are as follows: -- Determine which AUS UDRs are in use SELECT tk_name, tk_execute FROM sysadmin:ph_task WHERE tk_name MATCHES 'Auto Update Statistics *'; /* Output on system with FORCE absent: Auto Update Statistics Evaluation aus_evaluator Auto Update Statistics Refresh aus_refresh_stats Output on system with FORCE present: Auto Update Statistics Evaluation aus_evaluate_stats Auto Update Statistics Refresh aus_refresh_stats */ -- Provided procedures controlling which are used: CREATE FUNCTION aus_evaluator_downgrade() RETURNING INTEGER UPDATE ph_task SET (tk_execute) = ( "aus_evaluator") WHERE tk_name = 'Auto Update Statistics Evaluation'; RETURN 0; END FUNCTION; CREATE FUNCTION aus_evaluator_upgrade() RETURNING INTEGER UPDATE ph_task SET (tk_execute) = ( "aus_evaluate_stats") WHERE tk_name = 'Auto Update Statistics Evaluation'; RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_refresh_downgrade() RETURNING INTEGER UPDATE ph_task SET ( tk_execute, tk_enable, tk_frequency ) = ( "aus_refresh_stats_orig", 'f', INTERVAL ( 1 ) SECOND TO SECOND ) WHERE tk_name = 'Auto Update Statistics Refresh'; RETURN 0; END FUNCTION; CREATE FUNCTION informix.aus_refresh_upgrade() RETURNING INTEGER UPDATE ph_task SET ( tk_execute, tk_enable ) = ( "aus_refresh_stats", 't' ) WHERE tk_name = 'Auto Update Statistics Refresh'; UPDATE ph_task SET ( tk_frequency ) = ( INTERVAL ( 1 ) DAY TO DAY ) WHERE tk_name = 'Auto Update Statistics Refresh' AND tk_frequency < INTERVAL ( 1 ) DAY TO DAY; RETURN 0; END FUNCTION; I will raise an RFE for this: INSERT INTO ph_threshold VALUES ( 0, 'AUS_FORCE', 'Auto Update Statistics Evaluation', 'T', 'STRING','Append FORCE to all statements?' ); Informix HQ would need that added to the AUS configuration screen, and UDRs would need modifying appropriately. Note that Art's "dostats" has FORCE absent by default, but it can be added with option "--force-run", so no problem there. ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Hi Doug, There are two versions of the AUS Evaluator (and also the AUS Refresh job). The original one is written in SPL and in sysadmin:ph_task table its tk_execute value is aus_evaluator . This one doesn't add the FORCE keyword to statements. At some point (maybe with 14.10) a new version was introduced, implemented as a C UDR. It is functionally slightly different and uses the FORCE keywork liberally. Its tk_execute value is aus_evaluate_stats . I would be interested to hear if this is why you see differences. There is a procedure to call to switch between them. Let me know if you need it and I will dig it out. I am not sure why the FORCE keyword was added. We have observed that without it tables with STATCHANGE 0 do not always see statistics and distributions updated even when there have been some changes: maybe this is a bug. Ben. ------------------------------ Benjamin Thompson ------------------------------

The RFE is there now: https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-666 Please vote for it! ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Voted. Thanks for the SQL, Doug. ------------------------------ Davorin Kremenjas ------------------------------

Hi, Also annoying that the 2 evaluators and how to switch between them is not documented. Voted! Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------

Adding FORCE "under the covers" seems silly. And having to call a UDR to disable is awful. I want to be able to use FORCE at my discretion, certainly on giant tables that will obviously affect the performance and total runtime the overall runtime of the upd stats job. Am I missing something? Thanks! Mark ------------------------------ Thanks! Mark Scranton The Mark Scranton Group mark@markscranton.com ------------------------------

Mark: You are not missing anything. This is the reason why I recommend to my clients that they disable AUS altogether, set AUTO_STAT and STATCHANGE (as well as the statchange levels for highly active and highly static tables appropriately) and run dostats without --force-run (aka do not include the FORCE option) daily and run dostats with --force-run at quiet times once a week also taking advantage of the small_table threshold for smaller tables. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art. Agreed - at my last site I disabled AUS for a few reasons and managed upd stats on my own. Interesting trivia for you ... I have been recommending your dostats since I was aware of it to "the masses." At my last long gig though, I just wanted a tech challenge so I crafted my own flavor (Mstats, lol) and it was a blast. Had the luxury of a full run twice a week initially, and then every night once we got more horses. So didn't have to monkey with STATCHANGE, etc. but wished I'd learned more about all the new-ish params around upd stats. And no, my version was just for this one client and my tech fix. If I were to consult again I'd lead with dostats. ;) Thanks - Mark ------------------------------ Thanks! Mark Scranton The Mark Scranton Group mark@markscranton.com ------------------------------

For me I think it's necessary to separate the tool used to generate and run the commands from what happens inside the database engine when these commands are run. On AUS significant changes have been made under the covers to the tool with 14.10 without anything changing to the user interface or appearing in the release notes. I suspect this stems from the general policy of not documenting system internals and I have some issues with this approach but I don't expect it will change. Having said this the end result is broadly the same but the re-implementation has fixed some issues and created two new ones that I am aware of, one of which we are touching on here. For me the question is more why the FORCE keyword has been put in there and I can only think of two reasons: Someone thought it was a good idea and was perhaps playing safe. it is covering up a bug in STATCHANGE where the engine doesn't do any work even in situations where it should. Of course both these could be wrong as I am just speculating. (2) is a bug I think exists in the engine but haven't yet raised an SR about. For context our team has raised eleven APARs (I can list them all if you like!) via support cases around update statistics in the last three years, of which two relate to AUS specifically and nine affect you no matter what tool you use. I should perhaps devote some time to this one but it is a bit of an effort to reproduce. Ben. ------------------------------ Benjamin Thompson ------------------------------

Hi, Your observations seem to point to two possible reasons for the inclusion of the FORCE keyword: 1. A safety precaution 2. A workaround for a bug FORCE might kick in as an addition to various options as a precaution or, maybe, to hide a bug in the STATCHANGE engine. Your team has opened 11 APARs related to update statistics in the last three years. The absence of documentation and lack of clarity around alterations to system internals make it hard for users to understand the effects of such changes. A collaborative effort with the support team might help in tracing and fixing the root causes of these issues. Your experience with raising APARs (Authorized Program Analysis Reports) via support cases suggests that problems are manifesting with update statistics in the database engine, with some probably specific to AUS (Automatic Update Statistics) and others affecting the tool in a more general manner. Indeed, it may be challenging to reproduce them and are indeed time-consuming. Nonetheless, documenting and rectifying these defects will set a suitable precedent for enhancing the reliability and performance of the database engine. ------------------------------ Suman Suhag ------------------------------

Just to follow up on this, I did raise a case with support and this resulted in this defect being raised: IBM Support Ibm remove preview IBM Support View this on Ibm > (link edited) It can only affect you if you use STATLEVEL FRAGMENT. Ben. ------------------------------ Benjamin Thompson ------------------------------ For me I think it's necessary to separate the tool used to generate and run the commands from what happens inside the database engine when these commands are run. On AUS significant changes have been made under the covers to the tool with 14.10 without anything changing to the user interface or appearing in the release notes. I suspect this stems from the general policy of not documenting system internals and I have some issues with this approach but I don't expect it will change. Having said this the end result is broadly the same but the re-implementation has fixed some issues and created two new ones that I am aware of, one of which we are touching on here. For me the question is more why the FORCE keyword has been put in there and I can only think of two reasons: Someone thought it was a good idea and was perhaps playing safe. it is covering up a bug in STATCHANGE where the engine doesn't do any work even in situations where it should. Of course both these could be wrong as I am just speculating. (2) is a bug I think exists in the engine but haven't yet raised an SR about. For context our team has raised eleven APARs (I can list them all if you like!) via support cases around update statistics in the last three years, of which two relate to AUS specifically and nine affect you no matter what tool you use. I should perhaps devote some time to this one but it is a bit of an effort to reproduce. Ben. ------------------------------ Benjamin Thompson ------------------------------ Original Message: Sent: Tue April 01, 2025 12:47 PM From: Art Kagel Subject: Automated Update Statistics and FORCE Mark: You are not missing anything. This is the reason why I recommend to my clients that they disable AUS altogether, set AUTO_STAT and STATCHANGE (as well as the statchange levels for highly active and highly static tables appropriately) and run dostats without --force-run (aka do not include the FORCE option) daily and run dostats with --force-run at quiet times once a week also taking advantage of the small_table threshold for smaller tables. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com Original Message: Sent: Tue April 01, 2025 12:40 PM From: Mark Scranton Subject: Automated Update Statistics and FORCE Adding FORCE "under the covers" seems silly. And having to call a UDR to disable is awful. I want to be able to use FORCE at my discretion, certainly on giant tables that will obviously affect the performance and total runtime the overall runtime of the upd stats job. Am I missing something? Thanks! Mark ------------------------------ Thanks! Mark Scranton The Mark Scranton Group mark@markscranton.com Original Message: Sent: Mon March 31, 2025 05:10 AM From: Benjamin Thompson Subject: Automated Update Statistics and FORCE Hi Doug, There are two versions of the AUS Evaluator (and also the AUS Refresh job). The original one is written in SPL and in sysadmin:ph_task table its tk_execute value is aus_evaluator . This one doesn't add the FORCE keyword to statements. At some point (maybe with 14.10) a new version was introduced, implemented as a C UDR. It is functionally slightly different and uses the FORCE keywork liberally. Its tk_execute value is aus_evaluate_stats . I would be interested to hear if this is why you see differences. There is a procedure to call to switch between them. Let me know if you need it and I will dig it out. I am not sure why the FORCE keyword was added. We have observed that without it tables with STATCHANGE 0 do not always see statistics and distributions updated even when there have been some changes: maybe this is a bug. Ben. ------------------------------ Benjamin Thompson

Thanks, Ben. ------------------------------ Doug Lawry Oninit Consulting ------------------------------