Identifying the available access methods

To supplement the built-in B-tree secondary-access method that Informix® provides, your enterprise might have installed DataBlade modules that implement additional secondary-access methods. If additional access methods exist, they are defined in the sysams system catalog table. You can query the sysams system catalog to determine if additional access methods are available.

To identify the secondary-access methods that are available for your database, query the sysams system catalog table with the following SELECT statement:
SELECT am_id, am_owner, am_name, am_type FROM sysams
   WHERE am_type = 'S';
An 'S' value in the am_type column identifies the access method as a secondary-access method. This query returns the following information:
  • The am_id and am_name columns identify the secondary-access method.
  • The am_owner column identifies the owner of the access method.

In an ANSI-compliant database, the access-method name must be unique within the name space of the user. The access-method name always begins with the owner in the format am_owner.am_name.

By default, Informix provides the following definitions in the sysams system catalog table for two secondary-access methods, btree and rtree.
Access Method am_id Column am_name Column am_owner Column
Generic B-tree 1 btree 'informix'
R-tree 2 rtree 'informix'
Important: The sysams system catalog table does not contain a row for the built-in primary access method. This primary access method is internal to Informix and does not require a definition in sysams. However, the built-in primary access method is always available for use.

If you find additional rows in the sysams system catalog table (rows with am_id values greater than 2), the database supports additional user-defined access methods. Check the value in the am_type column to determine whether a user-defined access method is a primary- or secondary-access method.

For more information about the columns of the sysams system catalog table, see the HCL Informix Guide to SQL: Reference. For information about how to determine the operator classes that are available in your database, see Identifying the available operator classes.


Copyright© 2019 HCL Technologies Limited