ALTER ACCESS_METHOD statement

Use the ALTER ACCESS_METHOD statement to change one or more attributes of a user-defined primary or secondary access method in the sysams system catalog table.

Syntax

^{(explicit id accmeth002)
accmeth002} ALTER ACCESS_METHOD [ owner . ] access_method { | { MODIFY | ADD } <Purpose Option>^[] | DROP purpose_keyword }


Element	Description	Restrictions	Syntax
`access_method`	Name of the access method to modify	Access method must be registered in the sysams system catalog table by a previous CREATE ACCESS_METHOD statement	Identifier
`owner`	Name of the owner of the access method	Must own the access method	Owner name
`purpose _keyword`	A keyword that indicates which attribute to change	Keyword must be associated with the access method by a previous CREATE or ALTER ACCESS_METHOD statement	Purpose Functions, Flags, and Values

Usage

This statement is an extension to the ANSI/ISO standard for SQL. This statement cannot modify a built-in access method.

Use ALTER ACCESS_METHOD to modify the definition of a user-defined access method. You cannot modify a built-in access method.

You must own the access method or hold the DBA privilege to alter a user-defined access method. In an ANSI-compliant database, the DBA must qualify the name of the access method with the owner name if another user is the owner of the access method.

When you alter an access method, you change the purpose-option specifications (purpose functions, purpose methods, purpose flags, or purpose values) that define the access method. For example, you might alter an access method to declare a new user-defined function or method name, or to provide a multiplier for the scan cost on a table.

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

Examples

The following statement alters the remote user-defined access method:

ALTER ACCESS_METHOD remote
   ADD am_scancost = FS_scancost,
   ADD am_rowids,
   DROP am_getbyid,
   MODIFY am_costfactor = 0.9;

The preceding example makes the following changes to the access method:

Adds a user-defined function or method named FS_scancost( ), which is associated in the sysams table with the am_scancost keyword
Sets (adds) the am_rowids flag
Drops the user-defined function or method associated with the am_getbyid keyword
Modifies the am_costfactor value

¹ See Purpose Options