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 PRIMARY 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 or set the corresponding purpose flags, the access-method user guide must advise users not to use any of the following statements.
| Without this purpose function and purpose flag | Avoid this SQL statement |
|---|---|
| am_insert, am_readwrite | INSERT, ALTER FRAGMENT |
| am_delete, am_readwrite, am_rowids | DELETE, ALTER FRAGMENT |
| am_update, am_readwrite, am_rowids | 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 the am_cluster purpose flag is set in the sysams system catalog table, an SQL statement with the CLUSTER keyword fails. If the access method does not provide for clustering, the access-method user guide must advise users not to use the CLUSTER 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 VTI imposes on SQL. A user cannot specify a dbspace in a CREATE TABLE or ALTER FRAGEMENT statement. The VTI does not support the following statements for virtual tables: