- we have fragment level statistics (sysdistrib + sysfragdist) if we have more than 1 mio rows
and table is fragmented/partitioned.
- table level statistics (only sysdistrib) if we have less or equal 1 mio rows.
even if table is fragmented/partitioned.
- with fragment level statistics, sysfragdist stores statistic data (inserts/updates/deletes) for each fragment
and sysdistrib stores the sum of all fragments.
- fragment statistics (sysfragdist) will be updated if 10% (STATCHANGE) rows are changed
(updated/inserted/deleted)
- fragment level statistics are stored in a sbspace, so you need to define SYSSBSPACENAME
- we can use UPDATE STATISTICS MEDIUM/HIGH ... FORCE to "force" a "real" UST MED/HIGH

From the manuals:

Note: The SYSSBSPACENAME configuration parameter, which must be set when
the database server instance is initialized, specifies the sbspace in which the
database server stores fragment-level data distribution statistics. These are stored
as BLOB objects in the encdist column of the syfragsdist system catalog table. For
the database server to support fragment level statistics, the SYSSBSPACENAME
configuration parameter setting must specify an existing sbspace.

If you use the Statistics Options clause to set the STATLEVEL property to
FRAGMENT, the database server returns an error -9814 ("Invalid default sbspace
name") if either of the following is true:

- The SYSSBSPACENAME configuration parameter is not set
- The sbspace that SYSSBSPACENAME specifies was not properly allocated by the
onspaces -c -S command.

So what we know in Part I is that "Statistics updated." does not always mean that your statistics are up to date.
Sometimes it means: "Statistics are up to date and not changed - STATLEVEL threshold is not reached".

Lets look at the explain plan to verify, that your statistics are up to date or not:

Statistics are updated in sysdistrib after a little bit more than 10% of data has changed (via UPDATE).
In fact USTH changed sysdistrib data after 14.28% of data updated (we have 28 rows and 4 updates).
Maybe USTH changes sysdistrib only if more than % STATCHANGE rows are changed (4 rows).
10% of 28 rows == 2.8 rows this is rounded 3 rows. From the manual STATCHANGE is a threshold.
So it seems that threashold means more than STATCHANGE number of rows changed.

From the manuals:

"...the UPDATE STATISTICS statement compares the STATCHANGE setting with the
percentage of rows that have changed in each table or fragment since the current
data distributions were calculated, and selectively updates only the missing or stale
distribution statistics for each table or fragment within the scope of the UPDATE
STATISTICS statement."

"When the UPDATE STATISTICS statement runs
in MEDIUM or HIGH mode against the table, the database server compares the
stored values in these columns with the current values in the partition. Column
distribution statistics for the table are not updated if the sum of the stored values
differs from the sum of these current sysdistrib DML counter values from the
partition page by less than the threshold specified by the setting of the
STATCHANGE table attribute or of the STATCHANGE configuration parameter."

Did you sometimes wonder why a "UPDATE STATISTICS" Statement
is so faster in newer Informix version on greater tables ?

Or you wonder why the query is not faster after executed some
"update statistics medium/high" statements ?

Beginning with 11.70 there are 2 new onconfig parameters:

AUTO_STAT_MODE

and

STATCHANGE

This parameters has following default values in onconfig.std:

AUTO_STAT_MODE 1
STATCHANGE 10

Description from onconfig.std:

# AUTO_STAT_MODE - Enables (1) or disables (0) update statistics
# automatic mode. In automatic mode, statistics of
# table, fragment or index are rebuilt only if existing
# statistics are considered stale. A table, fragment
# or index can change by STATCHANGE percentage before
# its statistics are regarded as stale.
# STATCHANGE - In automatic mode, rebuild statistics only for
# table, fragment or index changed by STATCHANGE
# percentage since last statistics run.

Description in SQL Syntax Guide:

Automatic detection of stale statistics

You can enable Informix to automatically detect which table or fragment and index statistics are stale,
and ONLY REFRESH THE STALE STATISTICS when the UPDATE STATISTICS statement is run.
By default, statistics will be refreshed when 10% of the data is stale. You can use the
STATCHANGE property when a table is created oraltered to set the minimum percentage of change that is
required for the data to be considered stale. The database server refreshes statistics only if the data
has changed beyond that threshold since the distribution statistics were last calculated.

--#
--# NOTE:
--# coltype 7 == DATE
--# coltype 10 == DATETIME
--# In SQL Reference both fields are defined as DATETIME YEAR TO FRACTION(5),
--# but in fact only const_time is DATETIME YEAR TO FRACTION(5)
--# and constructed is DATE !

Description:
Use the STATCHANGE environment option to specify a positive integer
for a global percentage of a change threshold for the UPDATE
STATISTICS statement to use when the automatic mode for restricting
UPDATE STATISTICS operations to stale or missing distributions
is enabled.

The value of the STATCHANGE environment option is used when the
AUTO_STAT_MODE configuration parameter or the AUTO_STAT_MODE
environment option has enabled the automatic mode for the UPDATE
STATISTICS statement, so that it selectively refreshes only stale
data distributions.

The value that you set for STATCHANGE specifies a change threshold
to determine whether distribution statistics qualify for an update
when the UPDATE STATISTICS statement is operating in automatic mode.

Node 2 (Secondary) detects that Node 1 is unavailabe.
CMSM detects that Node 1 (Primary) is unavailable and initiate a failover.
Then Node 2 (Secondary) is converted to primary by the CMSM.
If Network on Node 1 is now back (cable replugged or interface is going up or so on..)
then we have 2 primaries: SPLIT-BRAIN !

How prevent a HDR SPLIT-BRAIN scenario ?

If network connection is broken, following alarm events on primary are triggered:

The most important event is the last one: "DR: Cannot connect to secondary server".

This trigger can be used inside the ALARMPROGRAM to prevent the SPLIT BRAIN
and initiate a shutdown of the primary (STOMITH or STONITH) if some more conditions are true:

- the secondary is no longer pingable by the primary
- the default gateway is not pingable

If these conditions are both true, than we can kill all (hanging sessions) and shutdown the primary.
Note: The default gateway should be pingable (can be disabled on switch) and the condition is that
secondary AND default gateway are NOT pingable.

This behavior is also called "Fencing".
Fencing is the process of isolating a node of a computer cluster or protecting shared resources when
a node appears to be malfunctioning.
Fencing is required because it is impossible to distinguish between a real failure and a temporary hang.

The default ALARMPROGRAM in $INFORMIXDIR/etc/$ONCONFIG is set to:
$INFORMIXDIR/etc/alarmprogram.sh

You can copy this script and modify it to prevent the split brain, backup logfiles, do something
other that is triggerd by an event...

Here is some code for the ALARMPROGRAM that initiates this split brain prevention:

Here is a short calculation of Informix Backup costs with and without compression.Informix backup can be compressed in different ways:- ontape to disk (with ONCONFIG Parameters BACKUP_FILTER and RESTORE_FILTER)- onbar to tape (with ONCONFIG Parameters BACKUP_FILTER and RESTORE_FILTER)- onbar to tape (with TSM compression)

The compression size depends on the compression tool/algorithm and the data(CHARs versus VARCHARS, BYTE, TEXT, BLOBs and so on).Compression time (Backup Time) depends only on the compression tool/algorithm,the number of CPUs the tool can use and the CPU frequency.

For the example calculation we assume, that the resulting (compressed) backup sizeis between 25% and 45% and the monthly storage costs for the backup is about0.15 USD per GByte per month (or 1.80 USD per GByte per year).We also assume, that we need one full backup per instance per day (to shorten therecovery time) and about 10 GByte logfiles to backup.

You can test your backup time and compression size with following commands:

$ onmode -wf SP_THRESHOLD=131072 # set the minimum amount of free kilobytes that can exist in a storage space # before Informix automatically runs a task to expand the space, either by # extending an existing chunk in the space or by adding a new chunk.

$ onmode -wf SP_WAITTIME=300 # specify the maximum number of seconds that a thread waits for a dbspace, # temporary dbspace, sbspace, temporary sbspace, or blobspace space to expand # before returning an out-of-space error.

Now we can check, that the "Expandable Chunk" Feature is enabled:

$ onstat -d |tail

Expanded chunk capacity mode: always

There are 2 ways, a chunk can be expanded.A: Via a monitoring task (mon_low_storage) and SP_THRESHOLD.B: While inserting data.

NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

Here we can see the flags:

Dbspaces: A on the 5th position: The dbspace is auto-expandable, because the SP_AUTOEXPAND configuration parameteris enabled and the dbspace is configured with a create size or extend size that is not zero.

Chunks: E on 5th position:Identifies the chunk as extendable.and also new in IDS 11.70 - D on 6th position:Using the direct I/O option for this cooked file chunk

Now we can create an Index or insert some data:

$ onstat -m..05/11/11 18:21:25 Chunk 5 in space 'dat_dbs01' has been extended by 131072 kb.05/11/11 18:38:20 Chunk 5 in space 'dat_dbs01' has been extended by 8000000 kb.05/11/11 19:21:23 Chunk 5 in space 'dat_dbs01' has been extended by 131920 kb.05/11/11 19:25:41 Chunk 5 in space 'dat_dbs01' has been extended by 8000000 kb.05/11/11 20:07:59 Chunk 5 in space 'dat_dbs01' has been extended by 8000000 kb.05/11/11 20:21:24 Chunk 5 in space 'dat_dbs01' has been extended by 131920 kb.05/11/11 23:40:30 Chunk 6 in space 'idx_dbs01' has been extended by 4592592 kb.05/11/11 23:40:36 Chunk 6 in space 'idx_dbs01' has been extended by 131072 kb.05/11/11 23:40:39 Chunk 6 in space 'idx_dbs01' has been extended by 131072 kb.05/11/11 23:45:22 Chunk 6 in space 'idx_dbs01' has been extended by 4592592 kb.05/12/11 00:21:23 Chunk 6 in space 'idx_dbs01' has been extended by 132016 kb.05/12/11 23:07:45 Chunk 6 in space 'idx_dbs01' has been extended by 9631360 kb.05/12/11 23:21:24 Chunk 6 in space 'idx_dbs01' has been extended by 132016 kb.

IF dty2s >= DATETIME(1970-01-01 00:00:00) YEAR TO SECOND AND dty2s <= DATETIME(2038-01-19 03:14:07) YEAR TO SECOND THEN RETURN (((((EXTEND(dty2s, YEAR TO DAY) - EXTEND(DATETIME(1970-01-01 00:00:00) YEAR TO SECOND, YEAR TO DAY))::INTERVAL DAY(9) TO DAY)::CHAR(20))::INTEGER * 86400) + (((EXTEND(dty2s, YEAR TO SECOND) - EXTEND(dty2s, YEAR TO DAY))::INTERVAL SECOND(9) TO SECOND)::CHAR(20))::INTEGER - ((DBINFO('utc_to_datetime', 0)::DATETIME YEAR TO SECOND - DATETIME(1970-01-01 00:00:00) YEAR TO SECOND)::INTERVAL SECOND(9) TO SECOND)::CHAR(20)::INTEGER); ELSE RETURN NULL;END IF;

END FUNCTION;

Database selected.

Routine created.

Database closed.

DATABASE sysmaster;

SELECT (CURRENT YEAR TO SECOND), DBINFO('utc_current'), sysadmin:datetime2utc(CURRENT YEAR TO SECOND) FROM sysmaster:sysdual;

to compete the CLR on destination Server/Instance we must break the loop to get the last logfile from Source Server/Instance: on Destination Server/Instance: onmode -l (triggers ALARMPROGRAM and writes Log to Backup Server, and copies ixbar.SERVERNUM File to NFS Share)

Now here we can stop the Source Server IDS Instance on Source Server/Instance:

Every unfragmented/unpartitioned IDS Table can have a maximum of 16 Mio. pages.To exceed this limit the table can be fragmented / partitioned.Here is a example function/task for the IDS 11.X sysadmin database to check this limit.The function triggers a Serverity RED Warning event if page fillfactor >= 98%, a Severity YELLOWWarning event if page fillfactor >= 90% and a YELLOW Info event if page fillfactor >= 70%.

It could be a problem for the application (or the admin) when database tables next extent sizeis larger than the dbspace free size. Here is a example function/task for the IDS 11.X sysadmin database tocheck these events:

INSERT INTO ph_task(tk_name,tk_type,tk_group,tk_description,tk_execute,tk_start_time,tk_stop_time,tk_frequency)VALUES("check_table_extent1","TASK","TABLES","Checks that table next extent will fit into dbspace.","check_table_extent1",DATETIME(00:00:00) HOUR TO SECOND,NULL,INTERVAL ( 4 ) HOUR TO HOUR);