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.


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


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.


The following statement alters the remote user-defined access method:
   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