Updating statistics for columns with user-defined data types

Programmers can write functions that gather statistics for columns with user-defined data types. You can store the data distributions for user-defined data types in an sbspace.

About this task

Because information about the nature and use of a user-defined data type (UDT) is not available to the database server, it cannot collect the colmin and colmax column of the syscolumns system catalog table for user-defined data types. To gather statistics for columns with user-defined data types, programmers must write functions that extend the UPDATE STATISTICS statement. For more information, see the performance chapter in HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.

Because the data distributions for user-defined data types can be large, you can optionally store them in an sbspace instead of the sysdistrib system catalog table.

To store data distributions for user-defined data types in an sbspace:

Procedure

Use the onspaces -c -S command to create an sbspace.
To ensure recoverability of the data distributions, specify LOGGING=ON in the -Df option, as the following sample shows:
```
% onspaces -c -S distrib_sbsp -p /dev/raw_dev1 -o 500 -s
      20000
   -m /dev/raw_dev2 500 -Ms 150 -Mo 200 -Df
      "AVG_LO_SIZE=32,LOGGING=ON"
```
For information about sizing an sbspace, see Estimating pages that smart large objects occupy.
For more information about specifying storage characteristics for sbspaces, see Configuration parameters that affect sbspace I/O.
Specify the sbspace that you created in step 1 in the configuration parameter SYSSBSPACENAME.
Specify the column with the user-defined data type when you run the UPDATE STATISTICS statement with the MEDIUM or HIGH keywords to generate data distributions.

Results

To print the data distributions for a column with a user-defined data type, use the dbschema -hd option.