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.
- refer to
- Updating statistics, in the chapter on individual query performance in your IBM® Informix Performance Guide
- Sbspace characteristics, in the chapter on configuration effects on I/O in your IBM Informix Performance Guide
- Writing user-defined statistics, in the performance chapter in IBM Informix User-Defined Routines and Data Types Developer's Guide
- Providing statistics data for a column, in the IBM 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).
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.
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.
- 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.
- 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.