Execute the UPDATE STATISTICS statement

To collect user-defined statistics, run the UPDATE STATISTICS statement in HIGH or MEDIUM mode.

The syntax of UPDATE STATISTICS is the same for user-defined data types as for built-in data types. However, when UPDATE STATISTICS collects statistics for a user-defined type, it does not automatically determine the minimum and maximum column values (stored in the colmin and colmax columns of the syscolumns system catalog table). Your statcollect() function can explicitly calculate these values if desired.

The statcollect() function executes once for every row that the database server scans during UPDATE STATISTICS. Therefore, a database table must contain more than one row before the database server calls any statcollect() functions.

The number of rows that the database server scans depends on the mode and the confidence level. Executing UPDATE STATISTICS in HIGH mode causes the database server to scan all rows in the table. In MEDIUM mode the database server chooses the number of rows to scan based on the confidence level. The higher the confidence level, the higher the number of rows that the database server scans. For general information about UPDATE STATISTICS, see the HCL Informix® Guide to SQL: Syntax.

For example, if the mytable table contains a column of type Box, the following UPDATE STATISTICS statement collects user-defined statistics for all columns of mytable, including any columns with user-defined statistics defined:

UPDATE STATISTICS HIGH FOR TABLE mytable;

If the mytable table contains columns with any data types that require user-defined statistics and you do not define this statistics collection, the UPDATE STATISTICS statement does not collect statistics for the column.

Important: The statistics that the database server collects might require a smart large object for storage. For the database server to use user-defined statistics, the configuration parameter SYSSBSPACENAME must be set in the ONCONFIG file before the database server is initialized. This configuration parameter must specify the name of an existing sbspace. If SBSSPACENAME is not set, the database server might not be able to collect the specified statistics.