When an SQL statement involves the access method, the database server checks the purpose settings in the sysams system catalog table to determine whether the access method supports the statement and the keywords within that statement.
The database server issues an exception and an error message if the purpose settings indicate that the access method does not support a requested SQL statement or keyword. If a user inadvertently specifies a feature that the access-method design purposely omits and the SQL syntax conforms to the IBM Informix: Guide to SQL Syntax, the documentation does not provide a solution.
Specify access-method support for the following items in the sysams system catalog table with a CREATE SECONDARY ACCESS_METHOD or Alter ACCESS_METHOD statement:
The user can receive an SQL error for statements that require a purpose function that you did not supply. The access-method user guide must advise users which statements to avoid.
If the access method does not supply one or more of the following purpose functions, the access-method user guide must advise users not to use any of the following corresponding statements
| Without this purpose function | Avoid this SQL statement |
|---|---|
| am_insert | INSERT, ALTER FRAGMENT |
| am_delete | DELETE, ALTER FRAGMENT |
| am_update | UPDATE |
| am_stats | UPDATE STATISTICS |
You must set a purpose flag to indicate the existence of code within the access method to support certain keywords. If a purpose flag is not set, the database server assumes that the access method does not support the corresponding keyword and issues an error if an SQL statement specifies that keyword.
For example, unless you set the am_unique purpose flag in the sysams system catalog table, an SQL statement with the unique keyword fails. If the access method does not support unique indexes, the access-method user guide must advise users not to use the unique or DISTINCT keyword.
An SQL statement fails if it specifies a storage space that does not agree with the am_sptype purpose value in the sysams system catalog table. In the user guide, specify whether the access method supports sbspaces, extspaces, or both. Advise the user how to do the following:
For more information about specifying storage spaces, refer to Creating and Specifying Storage Spaces.
If the access method supports fragmentation in sbspaces, advise the user to create multiple sbspaces with onspaces before issuing an SQL statement that creates fragments. For an example, refer to Using Fragments.
The database server also raises exceptions due to restrictions that the VII imposes on SQL. A user cannot specify a dbspace in a CREATE INDEX or ALTER FRAGMENT statement. The VII does not support the following activities for virtual indexes: