The sysdistrib system catalog table stores data-distribution information for the query optimizer to use. Data distributions provide detailed table and column information to the optimizer to improve the choice of execution paths of SELECT statements.

The sysdistrib table has the following columns.
Table 1. SYSDISTRIB table column descriptions
Column Type Explanation
tabid INTEGER Code identifying the table from which data values were gathered
colno SMALLINT Column number in the source table
seqno INTEGER Ordinal number for multiple entries
constructed DATETIME YEAR TO FRACTION(5) Date when the data distribution was created
mode CHAR(1) Optimization level: M = Medium H = High
resolution SMALLFLOAT Specified in the UPDATE STATISTICS statement
confidence SMALLFLOAT Specified in the UPDATE STATISTICS statement
encdat STAT Statistics information
type CHAR(1) Type of statistics: A = encdat has ASCII-encoded histogram in fixed-length character field S = encdat has user-defined statistics
smplsize SMALLFLOAT A value greater than zero up to 1.0 indicating a proportion of the total rows in the table that UPDATE STATISTICS samples. Values greater than 1.0 indicate the actual number of rows used that UPDATE STATISTICS samples. A value of zero indicates that no sample size is specified. UPDATE STATISTICS HIGH always updates statistics for all rows.
rowssmpld FLOAT Number of rows in the sample
constr_time DATETIME YEAR TO FRACTION(5) Time when the distribution was recorded
ustnrows FLOAT Rows in fragment when distribution was calculated.
ustbuildduration INTERVAL HOUR TO FRACTION(5) Time spent calculating the distribution statistics for this column
nupdates FLOAT Number of updates to the table
ndeletes FLOAT Number of deletes to the table
ninserts FLOAT Number of inserts to the table

Information is stored in the sysdistrib table when an UPDATE STATISTICS statement with mode MEDIUM or HIGH is executed for a table. (UPDATE STATISTICS LOW does not insert a value into the mode column.)

Only user informix can select the encdat column.

Each row in the sysdistrib system catalog table is keyed by the tabid and colno for which the statistics are collected.

For built-in data type columns, the type field is set to A. The encdat column stores an ASCII-encoded histogram that is broken down into multiple rows, each of which contains 256 bytes.

In HCL OneDB™, for columns of user-defined data types, the type field is set to S. The encdat column stores the statistics collected by the statcollect user-defined routine in multirepresentational form. Only one row is stored for each tabid and colno pair. A composite index on the tabid, colno, and seqno columns requires unique combinations of values.

The following three DML counter columns record counts of how many DML operations modifying data rows were performed on the table at the time of generation of column distribution statistics:

  • UPDATE operations in nupdates
  • DELETE operations in ndeletes
  • and INSERT operations in ninserts

These counts can also include rows modified by MERGE statements.

These DML counter columns store the values of the counters from the server partition that exists when distribution statistics are generated. If the AUTO_STAT_MODE configuration parameter, or the AUTO_STAT_MODE session environment setting, or the AUTO keyword of the UPDATE STATISTICS statement has enabled selective updating of data distribution statistics, the ninserts, ndeletes, and ninserts values can affect whether UPDATE STATISTICS operations refresh existing data distribution statistics. 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.