Informix

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.

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

Original Message:
Sent: Fri March 28, 2025 12:06 PM
From: Doug Lawry
Subject: 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
------------------------------

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 useSELECT tk_name, tk_executeFROM sysadmin:ph_taskWHERE tk_name MATCHES 'Auto Update Statistics *';/*Output on system with FORCE absent:Auto Update Statistics Evaluation       aus_evaluatorAuto Update Statistics Refresh          aus_refresh_statsOutput on system with FORCE present:Auto Update Statistics Evaluation       aus_evaluate_statsAuto 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

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

Original Message:
Sent: Fri March 28, 2025 12:06 PM
From: Doug Lawry
Subject: 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
------------------------------

The RFE is there now:

https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-666

Please vote for it!

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 useSELECT tk_name, tk_executeFROM sysadmin:ph_taskWHERE tk_name MATCHES 'Auto Update Statistics *';/*Output on system with FORCE absent:Auto Update Statistics Evaluation       aus_evaluatorAuto Update Statistics Refresh          aus_refresh_statsOutput on system with FORCE present:Auto Update Statistics Evaluation       aus_evaluate_statsAuto 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

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

Original Message:
Sent: Fri March 28, 2025 12:06 PM
From: Doug Lawry
Subject: 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
------------------------------

The RFE is there now:

https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-666

Please vote for it!

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 useSELECT tk_name, tk_executeFROM sysadmin:ph_taskWHERE tk_name MATCHES 'Auto Update Statistics *';/*Output on system with FORCE absent:Auto Update Statistics Evaluation       aus_evaluatorAuto Update Statistics Refresh          aus_refresh_statsOutput on system with FORCE present:Auto Update Statistics Evaluation       aus_evaluate_statsAuto 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

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

Original Message:
Sent: Fri March 28, 2025 12:06 PM
From: Doug Lawry
Subject: 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.

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.

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

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.

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.

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

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

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.

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.

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

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

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.

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.

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:

1. Someone thought it was a good idea and was perhaps playing safe.
2. 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.

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

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

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.

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.