Home
Extending Informix®Beyond standard relational database objects, HCL Informix® can be extended to handle specialized data types, access methods, routines, and other objects. Informix® includes many built-in extensions that are fully integrated in the database server. Informix® also provides DataBlade® modules, which are packages of extended database objects for a particular purpose and that are installed separately from the database server. Alternatively, you can create your own user-defined objects for Informix®.
Creating extensionsYou can create user-defined data types, routines, access methods, and other database objects to suit your needs. You can use application programming interfaces to write user-defined routines and applications that access data in Informix® databases.
DataBlade® API Programmer's GuideThe Informix® DataBlade® API Programmer's Guide describes information about the DataBlade® API, the C-language application programming interface (API) provided with HCL Informix®.
Create user-defined routines
Extend data typesThis section describes the ways to extend data types with C user-defined routines (UDRs).
Provide statistics data for a columnThe database server can provide statistics data for the columns of a table. This statistics data describes the distribution of the values within a column. The query optimizer uses this statistics data to determine the best path for an SQL statement. With this information, the optimizer can estimate the effect of a WHERE clause by examining, for each column included in the WHERE clause, the proportionate occurrence of data values contained in the column.
Collect statistics dataThe UPDATE STATISTICS statement collects statistics about the tables in your database. It automatically collects statistics for all columns with built-in data types (except TEXT and BYTE). However, it cannot automatically collect statistics for columns with user-defined data types because it does not know the structure of these data types.

Collect statistics data

The UPDATE STATISTICS statement collects statistics about the tables in your database. It automatically collects statistics for all columns with built-in data types (except TEXT and BYTE). However, it cannot automatically collect statistics for columns with user-defined data types because it does not know the structure of these data types.

For UPDATE STATISTICS to collect statistics for a column with a user-defined data type, you must write a user-defined function named statcollect() that collects statistics data for your user-defined data type. The UPDATE STATISTICS statement takes the following steps for columns of user-defined data types:

Calls the statcollect() function that handles the user-defined data type
This statcollect() function gathers the statistics data for the column and stores it as the stat opaque data type.
Stores this stat data type in the sysdistrib system catalog table, where the statistics data can be accessed by the query optimizer
UPDATE STATISTICS stores the following information in the row of the sysdistrib table that corresponds to the user-defined-type column:
- In the encdat column of the sysdistrib row: the stat data type that statcollect() returns
- In the type column of the sysdistrib row: an 'S' to indicate that the encdat column contains user-defined statistics

To have the UPDATE STATISTICS statement collect statistics for your user-defined data type, you must:

Design the statistics information that is appropriate for your user-defined data type.
Define a C statistics-collection function to implement the statistics collection.
Collect the statistics for the column within this statistics-collection function.
Register this C function as a statcollect() user-defined function.

If a statcollect() function does not exist for your user-defined data type, UPDATE STATISTICS does not collect statistics data for any column of that type.

Rate this topic

5 stars 4 stars 3 stars 2 stars 1 star

Comment on this topic.

By clicking this box, you acknowledge that you are NOT a U.S. Federal Government employee or agency, nor are you submitting information with respect to or on behalf of one. HCL provides software and services to U.S. Federal Government customers through its partners immixGroup, Inc. Contact this team at https://hcltechsw.com/resources/us-government-contact. Do not include any personal data in this Comment box.