CREATE ACCESS_METHOD statement

Use the CREATE ACCESS_METHOD statement to register a new primary or secondary access method in the sysams system catalog table.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

(explicit id cream002) cream002 CREATE { SECONDARY | PRIMARY } ACCESS_METHOD [IF NOT EXISTS] access_method ( <Purpose Options>[] )
Element Description Restrictions Syntax
access method Name declared here for the new access method Must be unique among access-method names in the sysams system catalog table Identifier

Usage

The CREATE ACCESS_METHOD statement adds a user-defined access method to a database. To create an access method, you specify purpose functions (or purpose methods), purpose flags, or purpose values as attributes of the access method, and you associate keywords (based on column names in the sysams system catalog table) with UDRs. You must have the DBA or Resource privilege to create an access method.

For information on setting purpose options, including a list of all the purpose function keywords, refer to Purpose Options.

The PRIMARY keyword specifies a user-defined primary-access method for a virtual table. The SECONDARY keyword specifies creating a user-defined secondary-access method for a virtual index. The SECONDARY keyword (and creating virtual indexes) is not supported in the Java™ Virtual-Table Interface.

The following statement creates a secondary-access method named T_tree: 
CREATE SECONDARY ACCESS_METHOD T_tree
(
am_getnext = ttree_getnext,

. . .
am_unique,
am_cluster,
am_sptype = 'S'
);

In the preceding example, the am_getnext keyword in the Purpose Options list is associated with the ttree_getnext( ) UDR as the name of a method to scan for the next item that satisfies a query. This example indicates that the T_tree secondary access method supports unique keys and clustering, and resides in an sbspace.

Any UDR that the CREATE ACCESS_METHOD statement associates with the keyword for a purpose function task, such as the association of ttree_getnext( ) with am_getnext in the preceding example, must already have been registered in the database by the CREATE FUNCTION statement (or by a functionally equivalent statement, such as CREATE PROCEDURE FROM).

The following statement creates a primary-access method named am_tabprops that resides in an extspace. 
CREATE PRIMARY ACCESS_METHOD am_tabprops
(
am_open = FS_open,
am_close = FS_close,
am_beginscan = FS_beginScan,
am_create = FS_create,
am_scancost = FS_scanCost,
am_endscan = FS_endScan,
am_getnext = FS_getNext,
am_getbyid = FS_getById,
am_drop = FS_drop,
am_truncate = FS_truncate,
am_rowids,
am_sptype = 'x'
);

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if an access method of the specified name is already registered in the current database.

1 See Purpose Options