© 2001 International Business Machines Corporation. All rights reserved.

Introduction

This technical note introduces two ways to extend Informix® Dynamic Server index methods. One way is to implement a new index method, called a "secondary access method", using the Virtual Index Interface (VII). The other way is to implement an operator class, which extends an existing index method to support a new data type.

This section introduces access methods and uses an example to briefly describe how index processing works.

An access method manages data in a table or an index. The Informix Dynamic Server has two kinds of access methods:

• A primary access method manages table data. For more information, see the Virtual Table Interface (VTI) Overview technical note.
• A secondary access method manages index data. VII lets you add new secondary access methods.

Access Method Purpose Functions

An access method is made up of a collection of routines, called purpose functions, that perform specific tasks, such as inserting, updating, deleting, and fetching data. An access method registers the following purpose functions so that the server knows which routine it should call to perform a particular task:

• am_create
• am_open
• am_close
• am_insert
• am_update
• am_delete
• am_drop
• am_beginscan
• am_getnext
• am_endscan

Let's look at an example. The following table supports a Video rental store application:

-- Create the table. 
 
create table videos 
(title   char(50),  -- Movie name 
year     int,       -- Year the movie was produced

copies   int        -- Number of copies on hand 
); 
 
-- Create an index on the title column that uses the 
-- vix_blob_am secondary access method
-- secondary access method that stores the index in 
-- a smart blob.

 
create index vidx on videos (title) 
using vix_blob_am; 

Here is an example.

How INSERT Gets Processed

When data is inserted into the videos table, for example:

insert into videos values ('Tarzan', 1991, 4);

The Informix server kicks off the following processing:

• It calls several of the primary access method purpose functions, passing the new data values to the functions, so the access method can do the physical i/o required to store the new data in the table. Specific steps include:

  • Open the videos table (am_open),
  • Insert the new data (am_insert),
  • Close the videos table (am_close).

• The table has a vix_blob_am index on the title column, so the server also calls several of the vix_blob_am purpose functions. vix_blob_am does the physical i/o required to store the title index key. Specific steps include:

  • Open the vidx index (am_open),
  • Insert the new data (am_insert):
    • First it decides where the Tarzan key should go,
    • Then it stores the index key and the table rowid.
  • Close the vidx index (am_close).

How SELECT Gets Processed

Now, let's say the video rental clerk enters the following query to look for all Tarzan movies:

select * from videos where title = 'Tarzan';

How does the database return the results to the user? How does it decide which rows qualified and which rows did not? What processing takes place?

During query processing, the query optimizer looks at each WHERE clause predicate, also called a qualification. The query above has just one predicate:

title = 'Tarzan'

Incidentally, the = operator is bound to the Equal routine, so that qualification actually gets translated to this:

Equal(title, 'Tarzan')

The server bundles up information about the vidx index and the qualification, then executes the vix_blob_am purpose functions needed for processing the SELECT statement:

• am_open to open the vidx index.

• am_beginscan to start the index scan.
• am_getnext to return qualified rows. The vix_blob_amam_getnext routine compares the search constant (Tarzan) to the index entries, and passes the table rowid of each one that matches back to the server.
• am_endscan to shut down the index scan.
• am_close to close the vidx index.

Summary

In a nutshell, this is how index processing works.

But, of course, the true story is more complex. This short introduction explained the role of the secondary access method purpose functions, but you also have to identify which SQL routines can use that index method. For example, something has to tell the optimizer that the vix_blob_am access method can evaluate Equal(title, 'Tarzan').

The rest of this technical note focuses on the two database objects that must be in place for an end-user to create an index using a new method:

1. A secondary access method, which is composed of purpose functions that perform low-level index tasks.
2. An operator class that provides:
   • support functions that help the access method construct and maintain the index for a specific data type.
   • strategy functions ("search functions") for use in SQL statements. This list tells the optimizer which SQL functions can use the index.

Contents

Back to top

Secondary access methods and VII

As introduced above, a secondary access method consists of functions that the Informix server calls to perform an index task, including creating, dropping, opening, scanning and closing the index, as well as inserting, updating, and deleting index entries. The routines that perform these tasks are collectively referred to as purpose functions. VII is the API that lets you implement purpose functions for a new secondary access method.

This section describes a secondary access method from two perspectives:

• The section on Purpose Functions uses a bottom-up approach that focuses on the purpose functions that implement access method support, how they are created, and how they get associated with access method tasks when the access method is created.
• The section on System Catalogs uses a top-down approach that focuses on how information in the system catalogs drives which functions get executed to perform a given task.

Purpose Functions

Purpose functions are implemented in C using the functions documented in the:

• Informix DataBladeTM API Programmer's Manual
• Virtual-Index Interface Programmer's Manual
• Virtual-Table Interface Programmer's Manual

These manuals are available for download from the Informix Online Documentation web site.

The purpose functions are user-defined routines (UDRs) that get associated with access method tasks when the access method is created.

For example, the following statements create the UDRs for the am_create and am_insert tasks (the vix_demo.bld shared object contains the compiled C code for the vix_blob_am method):

 
-- UDR for am_create purpose function 
create function vix_create (pointer) 
returns int 
external name 
   '$INFORMIXDIR/extend/vix_demo.1.3/vix_demo.bld(vix_create)' 
language c; 
 
-- UDR for am_insert purpose function 
create function vix_insert (pointer,pointer,pointer) 
returns int 
external name 
   '$INFORMIXDIR/extend/vix_demo.1.3/vix_demo.bld(vix_insert)' 
language c; 

You'll notice that each UDR argument gets specified as a pointer. Each UDR is matched to a C function in the vix_demo.bld shared library. Skimming the surface of the source code, below are the function prototypes for these two C functions:

 
mi_integer    vix_create (MI_AM_TABLE_DESC *td); 
 
mi_integer    vix_insert (MI_AM_TABLE_DESC *td,        -- index structure 
                          MI_ROW           *newrow,    -- new key value 
                          MI_AM_ROWID_DESC *newrowid); -- rowid of table row 

The Virtual-Index Interface Programmer's Manual specifies the C prototype for each purpose function. While the names are the same as for VTI, the C prototypes are different, so be sure you're working with the correct manual. Also, MI_AM_TABLE_DESC is a descriptor that describes a table structure for VTI and that describes an index structure for VII.

The following statement creates the vix_blob_am method, and ties each UDR to the corresponding access method task:

create secondary access_method vix_blob_am ( 
am_create    = vix_create, 
am_insert    = vix_insert, 
am_open      = vix_open, 
am_beginscan = vix_beginscan, 
am_getnext   = vix_getnext, 
... 
am_sptype='S' -- index is stored in a smart blob 
); 

When somebody executes the "create index ... using vix_blob_am" statement, the server automatically executes the UDR that is registered for the am_create task; in this case, vix_create.

System Catalogs

Access methods are table-driven. The SQL "create secondary access_method " command, shown above, creates a secondary access method and stores information about the new access method in the sysams system catalog. The server uses this information to dynamically load support for the access method and to call the correct purpose function for a given task.

For example, the following query shows the vix_blob_am method we just created, as well as the B-tree, R-tree, and hash access methods that ship with the Informix server:

select am_name, am_id, am_type, am_sptype, am_defopclass from sysams;

The am_name column stores the access method name and the am_id column stores its unique identifier. The am_type column stores the access method type ("S"=secondary). The am_sptype column identifies the kind of database space in which an index can be stored ("S"=smart blob space). The am_defopclass identifies its default operator class, and is a join key to sysopclasses.opclassid.

If you select all columns from sysams, you will see that it also stores the UDR ids for access method purpose functions. select * from sysams where am_name='vix_blob_am';

When a user creates an vix_blob_am index, the Informix server automatically calls the am_create function registered for the vix_blob_am access method.

Now, if you look closely, you may notice that the B-tree UDR ids are all set to zero and that the R-tree UDR ids are non-zero. B-tree is not a VII method; it is native to the server and the way that it actually gets processed is different from what has been presented in this technical note. R-tree is a VII method.

Contents

Back to top

Operator Class

An operator class provides UDRs that an index method uses to store and search for values. It also tells the optimizer which UDRs can use the index.

A new secondary access method requires at least one operator class. So, in that sense, a new VII method and an operator class go hand-in-hand.

Some secondary access methods also let you create your own operator class, which lets you extend an existing index to manage a new user-defined type (UDT). For example, both B-tree and R-tree let you create an operator class for a UDT. The Extending an Existing Index with an Operator Class technical note provides complete details.

This section describes an operator class from two perspectives:

• The section on Support and Strategy Functions uses a bottom-up approach that focuses on the support and strategy functions that implement opclass support, how they are created, and how they get associated with the opclass when it is created.

• The section on System Catalogs uses a top-down approach that focuses on how information in the system catalogs drives which functions get executed to perform a given task.

Support and Strategy Functions

Operator class UDRs fall into two categories:

Support functions

Support functions are UDRs that the access method uses internally to build and manage the index. These UDRs never get called from SQL.

When a new value is inserted, the access method am_insert purpose function is executed. It may execute an opclass support function to figure out where the new entry should go or to figure out how to split the index if the index page is full.

For example, B-tree operator classes require a user-defined Compare UDR that tells the B-tree where a new index key should be inserted.

Strategy functions

Strategy functions are UDRs that users execute in a SQL query. For example, B-tree has the five strategy functions summarized in the table below:

The strategy function list tells the optimizer which UDRs can use the index. If an end-user uses one of those functions in a SQL query predicate, the optimizer knows that an index on the column(s) involved might be useful for evaluating the query.

If the query optimizer decides to use the index, information about the query predicate gets sent to the access method am_getnext purpose function so that it can perform the comparison. Information includes the data types of the arguments, the values of the arguments, and the UDR that the user executed. The access method might actually execute the strategy function (using mi_routine_exec) to determine if an index key matches the user's query criteria, or it might do the comparison directly.

The following statement creates an operator class for vix_blob_am called vix_blob_ops, and specifies the support and strategy functions:

create opclass vix_blob_ops for vix_blob_am 
strategies (LessThan, LessThanOrEqual, Equal, GreaterThanOrEqual, GreaterThan) 
support    (none); 

Now the end-user can create an index on a table that uses the new access method and the operator class:

 
-- Create a table.; 
create table people 
   (pno     char(5), 
    pname   char(20), 
    pstatus int, 
    pcity   char(15)); 
 
 
-- Create an index that uses a specific opclass. 
create index xp1 on people (pno vix_blob_ops) 
using vix_blob_am; 

The optimizer considers the following query:

select * 
from   people 
where  pno='s582'; 

The = operator is bound to the Equal routine, which appears in the vix_blob_ops opclass strategy list. So the optimizer knows that it could use the vix_blob_am index. If it decides to use it, information about the "Equal(pno,'s582')" qualification gets pushed down to the access method to perform the comparison.

Finally, if we make that opclass the default for the access method:

a 
lter access_method vix_blob_am 
add   am_defopclass=vix_blob_ops; 

Then the index created above could be created without specifying the operator class:

 
-- Create an index that uses the default opclass. 
create index xp1 on people (pno) 
using vix_blob_am; 

The optimizer considers the following query:

select * 
from   people 
where  pno='s582'; 

The = operator is bound to the Equal routine, which appears in the vix_blob_ops opclass strategy list. So the optimizer knows that it could use the vix_blob_am index. If it decides to use it, information about the "Equal(pno,'s582')" qualification gets pushed down to the access method to perform the comparison.

Finally, if we make that opclass the default for the access method:

 
alter access_method vix_blob_am 
add   am_defopclass=vix_blob_ops; 

Then the index created above could be created without specifying the operator class:

 
-- Create an index that uses the default opclass.; 
create index xp1 on people (pno) 
using vix_blob_am; 

System Catalogs

Operator classes are also table-driven. The SQL "create opclass " command, shown above, creates an operator class. The sysopclasses system catalog stores information about the new operator class, including the names of the support and strategy functions in the support and ops columns, respectively. Running the following query on a newly-installed Informix system shows that the base server includes the btree_ops and rtree_ops operator classes:

select * from sysopclasses;

The "create opclass" statement associates an operator class with a specific access method. Once an operator class has been created for an access method, it may be assigned as the default operator class for that method.

So the sequence is to:

1. Create the access method with the "create secondary access_method" command, which is documented in the Virtual-Index Interface Programmer's Manual.

2. Create the operator class with the "create opclass" command, which is documented in the Informix Guide to SQL: Syntax. For example,

   create opclass vix_blob_ops for vix_blob_am strategies (LessThan, LessThanOrEqual, Equal, GreaterThanOrEqual, GreaterThan) support (none);

   
   

3. Alter the access method to set the default opclass to the specific operator class with the "alter access_method" command, which is documented in the Virtual-Table Interface Programmer's Manual. For example,

   alter access_method vix_blob_am add am_defopclass=vix_blob_ops;

   
   

Contents

Back to top

DBDK Support

The BladeSmith opaque type wizard lets you specify B-tree or R-tree indexing support. The code it generates overloads the default operator class for the access method.

While DBDK does not directly support creating a new operator class or access method, it is useful for managing the UDRs as a project. You can include the SQL statements for creating the operator class or access method as custom SQL. Then when you generate the SQL scripts, the SQL statements will be included in the objects.sql file.

Contents

Back to top

For more information about this topic

The following documentation is available for download from the Informix Online Documentation web site:

• Virtual-Index Interface Programmer's Manual
  VII provides an api for implementing a secondary access method.

• Virtual-Table Interface Programmer's Manual
  VTI provides an api for implementing a primary access method.

• Extending Informix Dynamic Server.2000, Version 9.2
  Describes how to extend an existing access method by either overloading the default operator class or by adding a new operator class.

• DataBlade Developers Kit User's Guide
  Also describes secondary access methods. Since the kit generates code that overloads the btree_ops and rtree_ops operator classes, it is a good starting point for understanding how to overload and extend access methods.

• Performance Guide for Informix Dynamic Server.2000
  Describes the structure of B-tree indexes. Also describes how the Query Optimizer works.

The following IDN technical notes expand on the topics presented here:

Contents

IBM, DB2, Informix, and WebSphere are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Windows and Windows NT are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information

Resources

• Informix Online Documentation
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Secondary access methods and VII
• Operator Class
• DBDK Support
• For more information about this topic
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers