DROP ACCESS_METHOD statement

Use the DROP ACCESS_METHOD statement to remove a previously defined primary or secondary access method from the database.

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

Syntax

DROP ACCESS_METHOD [IF EXISTS] access_method RESTRICT
Element Description Restrictions Syntax
access_method Name of access method to drop Must be registered in sysams system catalog table; cannot be a built-in access method Identifier
owner Owner of the access method Must own the access method Owner name

Usage

The RESTRICT keyword is required. You cannot drop an access method if virtual tables or indexes exist that use the access method. You must be the owner of the access method or have DBA privileges to drop an access method.

If a transaction is in progress, the database server waits to drop the access method until the transaction is committed or rolled back. No other users can execute the access method until the transaction has completed.

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

Example

For this example, suppose that an access method was created by this statement: 
	CREATE SECONDARY ACCESS_METHOD T_tree
	(
	  am_getnext = ttree_getnext,
	  am_unique,
	  am_cluster,
	  am_sptype = 'S'
	);
The following statement drops this access method: 
DROP ACCESS_METHOD T_tree RESTRICT;
Details of existing access methods can be found in the sysams system catalog table with the following query: 
SELECT am_name FROM informix.sysams;