sysams

When the R-tree access method is initially created, information about the access method is stored in the sysams system catalog. The database server uses this information to dynamically load support for the access method and call the correct user-defined function for a given task. These tasks include creating an R-tree index, scanning the index, inserting into the index, and updating the index.

Some of the columns of the sysams table include:

• am_name, the internal name of the access method. For the R-tree access method, the value of this column is rtree.
• am_type, the type of the index: primary (P) or secondary (S). R-tree is a secondary (S) index.
• am_sptype, the storage type of the index: dbspace (D), external to the database (X), sbspace (S), or any (A). R-tree indexes are stored in dbspaces (D).
• am_defopclass, the unique identifier of the default operator class. The unique identifier for the R-tree access method is 2, which corresponds to the row for rtree_ops in the sysopclasses system catalog.

The following query returns values for the am_name, am_owner, am_id, am_sptype, and am_defopclass columns of the sysams system catalog for the rtree entry:

SELECT am_name, am_owner, am_id, am_type, am_sptype, am_defopclass
FROM sysams
WHERE am_name = 'rtree';

am_name        rtree 
am_owner       informix 
am_id          2 
am_type        S 
am_sptype      D 
am_defopclass  2 

The query shows that the internal name of the R-tree access method is rtree, which is the name you specify in the USING clause of the CREATE INDEX statement when you create an R-tree index. The am_sptype column shows that R-tree indexes are stored in dbspaces, often in the same dbspace the indexed table is stored. The identifier for the default operator class, shown by the am_defopclass column, is 2. A query of the sysopclasses system catalog would show that rtree_ops has a unique identifier of 2 and is thus the default operator class for the R-tree access method.

For a complete description of the columns of the sysams system table, refer to the IBM Informix: Guide to SQL Reference.