Home > Articles > Data

This chapter is from the book

IDS 9.x Object-Relational Features

Before we can take advantage of IDS 9.x extensibility, we must understand its main features. Table 1–1 lists these features.

Table 1-1 IDS 9.x Object-Relational Features.

Collection Types

Distinct Type

Functional Index

Opaque Type

Primary and secondary access methods

Row type

Row type inheritance


Smart blob

Table inheritance

User-defined aggregate

User-defined function

Let's first look at the types available. These types are divided into two categories: simple types and complex types. We start with the simple types, followed by the complex types.

Smart Blob

Informix has added two new types to handle objects that require a large amount of storage. The first type is called Character Large Object (CLOB), the other one Binary Large Object (BLOB). They are base types of the system and are stored in specialized storage called sbspaces.

Smart blobs provide two advantages over the traditional blob types (BYTE and TEXT). First, they can store a much larger amount of data (4 * 240 bytes). The second benefit is that it is possible to modify the content of a smart blob without having to extract the entire blob from the database. This can have a significant impact in the time required for data transfer.

Distinct Type

A distinct type takes its definition from an existing type. For example, you can define a type USDollar based on the Money type. This is done with the following statement:


The use of distinct types makes database schemas more meaningful. Instead of relying on the name of a column to find out what it is for, we now have the use of specific types that provide more discrimination in their use.

Yet, a more important benefit is the addition of strong typing within the database server. Because you can now differentiate between different types of integers or decimal values, you can implement specific business processing to manipulate them. You can also decide what the interactions are between different types. This is done by implementing polymorphic user-defined routines, as we will see later.

The importance of distinct types cannot be overstated. This parallels the addition of strong typing to the C language and the use of strong typing in object-oriented languages. These advances helped reduce the programming errors in applications by ensuring that the right type of argument is used in functions or method calls. Similarly, without strong typing, it is possible to write SQL statements that will be syntactically correct and complete their execution. These same SQL statements could be wrong at the business level. Just imagine what would happen if you filled up an intercontinental plane with a specific number of liters of fuel instead of gallons.

Opaque Type

An opaque type provides the capability to define the structure of an area of storage. Opaque types come in two flavors: fixed-size and variable-size. The variable-size opaque type can accommodate any size, as its storage can vary based on the current amount of storage required.

System architects can decide on the best way to represent their business data to provide optimal processing speed. Opaque types provide total control of the external and internal representation. If comparison operators are provided, they can also be indexed.

Implementations can take advantage of opaque types to include better ways to process business data to simplify processing and improve performance.

Collection Types

Informix provides three types of collection types: set, multiset, and list. A set is an unordered collection of unique values. A multiset is an unordered collection that allows for duplicate values. A list is an ordered collection of values, allowing for duplicates.

The values stored in a collection type can be of any type, including opaque types, row types, and even collection types.

Row Type and Inheritance

A row type defines a multicolumn value similar to a table definition. Row types can be named or unnamed and are used as columns in tables or as table types in a table hierarchy.

Figure 1–1 illustrates a table hierarchy for loans that specialize into different industries. The definition of the manufacturing table starts with the definition of a manufacturing row type that inherits from the loans row type. This definition follows the following format:

Figure 1–1 Table hierarchy for loans in multiple industries.

CREATE ROW TYPE manufacturing_t (
. . .
) UNDER loans_t;

Following the same logic, the manufacturing table is defined as being under the loans table:

CREATE TABLE manufacturing OF TYPE manufacturing_t
UNDER loans;

Once the table hierarchy is defined, you can issue statements that operate on the entire hierarchy as one table or on a specific subtable. For example, if the loans table includes a loan_amount attribute, you can operate on the entire hierarchy to find the total loan amounts for each company branch (branch_id) as in:

SELECT branch_id, SUM(loan_amount)
FROM loans
GROUP BY branch_id
ORDER BY branch_id;

This opens the door to interesting approaches, as we'll see in the later section on aggregates. This feature also helps in the matching of object-oriented analysis and design in the database implementation.

User-Defined Functions (UDFs)

UDFs implement the business logic you want as part of the database server. The functions can be written using Informix stored-procedure language (SPL) and C. Informix also provides the ability to write UDFs in Java using the J/Foundation option.

You could consider UDFs as object methods. The syntax differences are mainly cosmetic. Consider the following statement syntaxes:

method(Object [, args]);

The first syntax represents the usual object method execution where the method is part of the object definition. The second syntax shows a method that requires a first argument, which is the object it operates on. The end result is the same. The system can differentiate between multiple methods with the same name by looking at the type and number of arguments. The second method is the approach taken in IDS 9.x.

When a UDF is called within an SQL statement, the database server examines the function name and its argument types. It then chooses the function that fits the definition. This polymorphic dispatch provides another example of object-oriented capabilities. You can then define multiple functions with the same name and have the database server figure out which one to call depending on the context.

Looking back at Figure 1–1, we can imagine a set of riskfactor() functions, each one taking a different row type. The function definitions would look like the following:

CREATE FUNCTION riskfactor(manufacturing_t)
RETURNING double precision
. . .

These functions are relatively straightforward. They receive a specific type of record and return the risk factor based on the input values. The database server provides the framework for set processing. For example, if we want to find out the average risk factor that each branch is taking, we can submit an SQL statement such as:

SELECT branch_id, AVG(riskfactor(loans))
FROM loans
GROUP BY branch_id;

This statement executes over the entire hierarchy. It returns rows from different types. This means that the appropriate riskfactor() function is called depending on the context. It is important to note that the riskfactor() is used in the processing of the different sets of values but is unaware of set processing. There is a direct benefit here because the application code does not have to include additional processing to handle the processing of sets, keeping track of partial sums, counts, and so on. The database framework is used to reduce the complexity of the business code.


IDS 9.x allows you to add new types of aggregate functions. An aggregate function has four stages of processing: initialization, iteration, combine, and final. An aggregate function must then implement these four functions. Only the iteration and combine stages are mandatory. A UDA is in fact a grouping of up to four functions that are used to implement the aggregation.

The UDAs add some interesting features compared to standard aggregate functions. First, they can receive complex types as input. This means that a row type can be passed as the value to aggregate. Second, it allows for an additional parameter that is used as an initialization value. This initialization parameter can also be a row type. This opens the door to interesting possibilities where the initialization value can impact the way the aggregation is done.

Let's go back to our loan example. In the previous section, we described the use of a set of riskfactor() functions. The result of the SQL statement was an average risk factor. This is not necessarily what management wants to see. Instead, we may want to provide the average risk taken by each branch. To do this, each risk must be weighted with the amount of the loan. We can do this by using the riskfactor() functions we already have in the context of a UDA. The new aggregate function must handle row types. Assuming that we called the UDA avgrisk(), we can find the average risk taken by each branch on its portfolio of loans with the following statement:

SELECT branch_id, avgrisk(loans)
FROM loans
GROUP BY branch_id;

All the processing added to the server to implement the UDA would be required in an application program and more. The end result of using this new approach is less complexity and increased performance.


Informix supports two types of indexing: B-tree and R-tree. The B-tree is the default indexing method. If you want to use the R-tree, you must indicate it on the create index. In up to version 9.30 of IDS, you must also create opaque types and support functions to be able to use the R-tree. An example of implementation can be found in Chapter 6.

A B-tree is stored as a balance tree structure. Because of the balance feature, all searches take around the same number of comparisons to find the answer. The B-tree is a standard indexing method provided by database vendors. As for previous versions, it is used to index all the built-in types, excluding BYTE and TEXT. Informix has modified its B-tree implementation to support user-defined types. This includes distinct types and opaque types. The two criteria for indexing are that the type has comparison operators (equal, less than, greater than, etc.) and that the total key length does not exceed 390 bytes.

The indexing of new types makes sense but what about distinct types? Can't we just use the support provided by the base type the distinct type is based on? The short answer is yes. There are some situations in which the comparison provided by the base type does not correspond with the business way to use the distinct type. In this case, you only have to add comparison functions for your distinct type and the indexing will be done appropriately.

Another interesting application of the B-tree relates to what we call functional indexes. It is possible to create an index on the result of a function. For example:

ON order(quarter(order_date)) USING btree;

The advantage of this approach is that you don't need to add calculated fields to your table. Even a four-byte field has an impact when we are talking about millions of rows.

The other indexing method available is the R-tree. This indexing method has a perfect fit in spatial queries. It allows the indexing of multiple dimensions where each dimension is considered in the query. This is better explained with an example. Consider a spatial query in which you want to find all the objects that are within a certain radius of a location. With an R-tree, you can answer this by asking for all the objects within a specified circle:

SELECT * FROM location
WHERE within(longlat, myCircle);

The myCircle value is a new opaque type that defines a circle at a specific location. If you only had the B-tree available, you could submit a query such as:

SELECT * FROM location2
WHERE long >begin AND long < end;

Of course, you could go further and provide a similar comparison for latitude. The problem with this approach is that the indexes may not discriminate enough on the values and a table scan will be issued, causing the entire table to be read. Because the R-tree handles all the dimensions, it can provide a better discrimination on the data and can be picked where the B-tree would be ignored. If you are dealing with more complex searches involving more complex shapes, then the advantage of the R-tree is even more obvious.

Of course, you may not care much about longitude and latitude today. If you look carefully at the functionality provided by the R-tree, you should realize that it can index any numbers, not just coordinates. What about indexing age and income? This could facilitate data analysis for better customer targeting. And why stop at two dimensions? It should be safe to go to five or six if not a little higher.

Access Methods

Informix also provides a way to add access methods to the database server. They consist of the Virtual Table Interface (VTI) and the Virtual Index Interface (VII). As their names indicate, the VTI is used to make things look like a table in the database, and the VII is used to add indexing methods such as the R-tree and Excalibur and Verity text search engines.

VTI and VII should be considered advanced features and should be approached carefully. I liken VTI and VII to device drivers. Depending on the functionality you provide, it will be simple or complex. If you provide support for write operations, you must then worry about transaction control. However, if used appropriately, these interfaces can be very beneficial.

The use of VTI to access external files from the database server is the subject of Chapter 4.

The Spatial DataBlade

Informix added the Environmental Systems Research Institute (ESRI) spatial support to the base IDS 9.x product. It is a commercial DataBlade, not an open-source product. This DataBlade provides support for queries containing locations. It defines several new data types and functions to accommodate this capability. These location data types can be indexed using the R-tree mentioned previously to dramatically speed up retrieval of information.

If you have a need to store and retrieve geographical information, this should be the first thing you look at to solve your problem. It is a supported commercial product and it comes with the IDS 9.x server.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020