SYSSBSPACENAME configuration parameter

Use the SYSSBSPACENAME configuration parameter to specify the name of the sbspace in which the database server stores fragment-level data-distribution statistics, which the syfragsdist system catalog table stores as BLOB objects in its encsdist column. Also use SYSSBSPACENAME to specify the name of the sbspace in which the database server stores statistics that the UPDATE STATISTICS statement collects for certain user-defined data types.

onconfig.std value

Not set.

if not present

0

values

Up to 128 bytes. SYSSBSPACENAME must be unique, begin with a letter or underscore, and contain only digits, letters, underscores, or $ characters.

takes effect

After you edit your onconfig file and restart the database server.

When you reset the value dynamically in your onconfig file by running the onmode -wf command.

When you reset the value in memory by running the onmode -wm command.

refer to

Updating statistics, in the chapter on individual query performance in your HCL Informix Performance Guide
Sbspace characteristics, in the chapter on configuration effects on I/O in your HCL Informix Performance Guide
Writing user-defined statistics, in the performance chapter in HCL Informix User-Defined Routines and Data Types Developer's Guide
Providing statistics data for a column, in the HCL Informix DataBlade API Programmer's Guide

Usage

To support fragment level statistics, you must specify the name of an sbspace as the SYSSBSPACENAME setting, and you must allocate that sbspace (by using the onspaces utility, as described below. For any table whose STATLEVEL attribute is set to FRAGMENT, the database server returns an error if SYSSBSPACENAME is not set, or if the sbspace to which SYSSBSPACENAME is set was not properly allocated).

For the distribution statistics of a column in a fragmented table, you can estimate how many bytes of storage capacity the sbspace requires by this formula:

nfrags * 1.25 * ((10000 / resolution) * ((2 * column_width) + 6))

Here 1.25 approximates the number of overflow bins. The formula also includes these variables:

column_width is the width in bytes of the column that the UPDATE STATISTICS statement specifies.
nfrags is the number of fragments of the table.
resolution is the percent value in the resolution clause of the UPDATE STATISTICS statement that calculates the distribution.

The resolution is also what the dbschema -hd table command displays for the column distribution statistics.

SYSSBSPACENAME also specifies the name of the sbspace in which the database server stores statistics that the UPDATE STATISTICS statement collects for certain user-defined data types. Normally, the database server stores statistics in the sysdistrib system catalog table.

Do not confuse the SYSSBSPACENAME configuration parameter with the SBSPACENAME configuration parameter .

Because the data distributions for user-defined data types can be large, you have the option to store them in an sbspace instead of in the sysdistrib system catalog table. If you store the data distributions in an sbspace, use DataBlade API or Informix® ESQL/C functions to examine the statistics.

Even though you specify an sbspace with the SYSSBSPACENAME parameter, you must create the sbspace with the -c -S option of the onspaces utility before you can use it. The database server validates the name of this sbspace when one of the following occurs:

The database server attempts to write data distributions of the multirepresentational type to SYSSBSPACENAME when it executes the UPDATE STATISTICS statement with the MEDIUM or HIGH keywords.
The database server attempts to delete data distributions of the multirepresentational type to SYSSBSPACENAME when it executes the UPDATE STATISTICS statement with the DROP DISTRIBUTIONS keywords.

If SBSSPACENAME is not set, or if storage is not allocated to that sbspace, the database server might not be able to store the distribution statistics, so that the UPDATE STATISTICS operation fails with error -9814.

Although you can store smart large objects in the sbspace specified in SYSSBSPACENAME, keeping the distribution statistics and smart large objects in separate sbspaces is recommended, because:

You avoid disk contention when queries are accessing smart large objects, and the query optimizer is using the distributions to determine a query plan.
Disk space takes longer to fill up when each sbspace is used for a different purpose.