CREATE ACCESS_METHOD statement

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

Syntax

(1)

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.

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).

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.