Hello,

I slowly prepare the steps to upgrade.
And I see that the "update statistic" is an important one, after the upgrade.

The problem is, I inherited the database without really knowing what it is !! : )
or even what was going on in it.

It seems to me that the AUS is "running":
If sysadmin.ph_run has a run_time with an actual date,
and pretty much all of the fields in sysadmin.ph_task have "T" on everyday,

can I understand that the AUS is scheduled and working?

Ans, if this is the case, this conf won't change after the upgrade isn't it ?

Thanks

------------------------------
Sh To
------------------------------

#Informix

What you're describing sounds like AUS might be active (which it would by default).

What actaually determines active/inactive (separate from schedule) is the tk_enable field:  t for true (on), f for false (off).

And yes, none of this should be modifed by upgrade.

HTH,
 Andreas

------------------------------
Andreas Legner
------------------------------

Sh To:

The way to see if a task is active is to look at the ph_task.tk_enable column. If it is set to 't' then the task is active, if 'f' it is inactive.

As for whether the AUS settings will survive the upgrade, that depends on whether the upgrade includes changes to the sysadmin database which happens from time to time, especially with bigger version jumps.

Also, when upgrading you must wipe the existing data distributions (UPDATE STATISTICS LOW DROP DISTRIBUTIONS) and set completely new ones. Personally I don't like the default set of distributions that AUS produces, but if you prime the pump with the set of distributions that you want, AUS will maintain them for you. 

For me the ideal set of distributions is generated by my dostats utility and you can use dostats to prime the pump for AUS. Dostats can even drop the existing distributions for you:

dostats -d <database> --drop-distributions 

Alternatively, my dbschema replacement utilility, myschema, will generate a set of update statistics commands to duplicate the level of distributions you currently have if you run it before dropping distributions:

myschema -d <database> --distributions-file=/some/file/path.sql /dev/null 

Then you can run the file it produces for you after dropping distributions (or edit the file to add the drop command to the top).

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

This query against the sysadmin database will tell you when the AUS jobs last ran and if enabled or not:

select
t.tk_name,
t.tk_enable,
r.run_time,
round(r.run_duration,2) run_duration
from ph_task t, outer ph_run r
where t.tk_name matches "Auto Update Statistics*"
and r.run_task_id = t.tk_id
and r.run_id =
(select max(r2.run_id)
from ph_run r2
where r2.run_task_id = r.run_task_id)

Example output:

tk_name Auto Update Statistics Evaluation
tk_enable t
run_time 2021-06-09 01:00:21
run_duration 21.50

tk_name Auto Update Statistics Refresh
tk_enable t
run_time 2021-06-06 01:11:19
run_duration 19.77


In most cases with an upgrade, the sysadmin customizations will be preserved, however one of the few problems I have had with upgrades have been issues with updating sysadmin, and I have needed to rebuild it...wiping out any customizations.


------------------------------
Mike Walker
------------------------------

Thank you all

------------------------------
Sh To
------------------------------

Original Message:
Sent: Wed June 09, 2021 10:25 AM
From: Mike Walker
Subject: AUS status

This query against the sysadmin database will tell you when the AUS jobs last ran and if enabled or not:

select
t.tk_name,
t.tk_enable,
r.run_time,
round(r.run_duration,2) run_duration
from ph_task t, outer ph_run r
where t.tk_name matches "Auto Update Statistics*"
and r.run_task_id = t.tk_id
and r.run_id =
(select max(r2.run_id)
from ph_run r2
where r2.run_task_id = r.run_task_id)

Example output:

tk_name Auto Update Statistics Evaluation
tk_enable t
run_time 2021-06-09 01:00:21
run_duration 21.50

tk_name Auto Update Statistics Refresh
tk_enable t
run_time 2021-06-06 01:11:19
run_duration 19.77


In most cases with an upgrade, the sysadmin customizations will be preserved, however one of the few problems I have had with upgrades have been issues with updating sysadmin, and I have needed to rebuild it...wiping out any customizations.


------------------------------
Mike Walker
------------------------------

Hi, just a couple of thoughts:

It's good to check that the two AUS jobs are enabled and that they run. For extra thoroughness and peace of mind it's also good to check the age of your statistics and distributions match your expectations.

Art said you must wipe existing data distributions when upgrading. If you have a small database you could do this anyway and it would not cost you much. However, recreating them afterwards can take many hours, even days on some systems and may be completely impractical.

The manual at:
https://informix.hcldoc.com/12.10/help/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_633.htm
says:
When you upgrade to a new version of the database server, you might need to drop distributions to remove the old distribution structure in the sysdistrib system catalog table.

The word "might" is important here. I don't think this has been necessary for quite some time, although this leaves open that it might be again for a specific upgrade in the future. 11.70 -> 12.10 or 12.10 -> 14.10 upgrades do not require this.

Ben.

------------------------------
Benjamin Thompson
------------------------------

Original Message:
Sent: 6/10/2021 9:23:00 AM
From: Benjamin Thompson
Subject: RE: AUS status

Hi, just a couple of thoughts:

It's good to check that the two AUS jobs are enabled and that they run. For extra thoroughness and peace of mind it's also good to check the age of your statistics and distributions match your expectations.

Art said you must wipe existing data distributions when upgrading. If you have a small database you could do this anyway and it would not cost you much. However, recreating them afterwards can take many hours, even days on some systems and may be completely impractical.

The manual at:
https://informix.hcldoc.com/12.10/help/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_633.htm
says:
When you upgrade to a new version of the database server, you might need to drop distributions to remove the old distribution structure in the sysdistrib system catalog table.

The word "might" is important here. I don't think this has been necessary for quite some time, although this leaves open that it might be again for a specific upgrade in the future. 11.70 -> 12.10 or 12.10 -> 14.10 upgrades do not require this.

Ben.

------------------------------
Benjamin Thompson
------------------------------