Purpose options

The CREATE PRIMARY ACCESS_METHOD and ALTER ACCESS_METHOD statements specify purpose attributes with the following syntax.

Syntax

Purpose Option


Element	Purpose	Restrictions	Syntax
`purpose function`	A keyword that specifies a task and the corresponding access-method function	The interface specifies the predefined purpose-function keywords to which you can assign UDR names. You cannot name a UDR with the same name as the keyword.	Function purpose category; see Purpose functions, purpose flags, and purpose values.
`purpose value`	A keyword that identifies configuration information	The interface specifies the predefined configuration keywords to which you can assign values.	Value purpose category; see Purpose functions, purpose flags, and purpose values.
`purpose flag`	A keyword that indicates which feature a flag enables	The interface specifies flag names.	Flag purpose category; see Purpose functions, purpose flags, and purpose values.
`function name`	The user-defined function that performs the tasks of the specified purpose function	A CREATE FUNCTION statement must register the function in the database.	Database Object Name segment; see Database Object Name .
`string value`	An indicator that is expressed as one or more characters	None	Quoted String segment; see Quoted String.
`numeric value`	A value that can be used in computations	None	A numeric literal.

Usage

Each purpose-name keyword corresponds to a column name in the sysams system catalog table. The database server uses the following types of purpose attributes:

Purpose functions: A purpose-function attribute maps the name of a user-defined function to one of the prototype purpose functions that Purpose functions describes.
Purpose flags: Each flag indicates whether an access method supports a particular SQL statement or keyword.
Purpose values: These string, character, or numeric values provide configuration information that a flag cannot supply.

You specify purpose options when you create an access method with the CREATE PRIMARY ACCESS_METHOD statement. To change the purpose options of an access method, use the ALTER ACCESS_METHOD statement.

To enable a purpose function:

Register the access-method function that performs the appropriate tasks with a CREATE FUNCTION statement.
Set the purpose-function name equal to a registered UDR name.
For example, Sample CREATE PRIMARY ACCESS_METHOD statement sets the am_getnext purpose-function name to the UDR name textfile_getnext. This example creates a access method.
The example in Sample ALTER ACCESS_METHOD statement adds a purpose function to an existing access method.

To enable a purpose flag, specify the purpose name without a corresponding value.

To clear a purpose-option setting in the sysams system catalog table, use the DROP clause of the ALTER ACCESS_METHOD statement.

Settings purpose functions, flags, and values

The following table describes the possible settings for the sysams columns that contain purpose-function names, purpose flags, and purpose values. The items in following table appear in the same order as the corresponding sysams columns.

Table 1. Purpose functions, purpose flags, and purpose values
Purpose-name keyword	Explanation	Purpose category	Default setting
am_sptype	A character that specifies what type of storage space the access method supports For a user-defined access method, am_sptype can have any of the following settings: `X` indicates that the access method accesses only extspaces `S` indicates that the access method accesses only sbspaces `A` indicates that the access method can provide data from extspaces and sbspaces You can specify am_sptype only for a new access method. You cannot change or add an am_sptype value with ALTER ACCESS_METHOD. Do not set am_sptype to D or attempt to store a virtual table in a dbspace.	Value	`A`
am_cluster	A flag that you set if the access method supports clustering of tables	Flag	Not set
am_rowids	A flag that you set if the primary access method can retrieve a row from a specified address	Flag	Not set
am_readwrite	A flag that you set if the access method supports data changes The default setting for this flag, not set, indicates that the virtual data is read-only. Unless you set this flag, an attempt to write data can cause the following problems: An INSERT, DELETE, UPDATE, or ALTER FRAGMENT statement causes an SQL error. The database server does not run am_insert, am_delete, or am_update.	Flag	Not set
am_parallel	A flag that the database server sets to indicate which purpose functions can run in parallel If set, the hexadecimal am_parallel flag contains one or more of the following bit settings: The `1` bit is set for parallelizable scan. The `2` bit is set for parallelizable delete. The `4` bit is set for parallelizable update. The `8` bit is set for parallelizable insert.	Flag	Not set
am_costfactor	A value by which the database server multiplies the cost that the am_scancost purpose function returns An am_costfactor value `0.2` - `0.9` reduces the cost to a fraction of the value that am_scancost calculates. An am_costfactor value of `1.1` or greater increases the am_scancost value.	Value	`1.0`
am_create	The name of a user-defined function that adds a virtual table to the database	Function	None
am_drop	The name of a user-defined function that drops a virtual table	Function	None
am_open	The name of a user-defined function that makes a fragment, extspace, or sbspace available	Function	None
am_close	The name of a user-defined function that reverses the initialization that am_open performs	Function	None
am_insert	The name of a user-defined function that inserts a row	Function	None
am_delete	The name of a user-defined function that deletes a row	Function	None
am_update	The name of a user-defined function that changes the values in a row	Function	None
am_stats	The name of a user-defined function that builds statistics based on the distribution of values in storage spaces	Function	None
am_scancost	The name of a user-defined function that calculates the cost of qualifying and retrieving data	Function	None
am_check	The name of a user-defined function that tests the physical structure of a table	Function	None
am_beginscan	The name of a user-defined function that sets up a scan	Function	None
am_endscan	The name of a user-defined function that reverses the setup that AM_BEGINSCAN initializes	Function	None
am_rescan	The name of a user-defined function that scans for the next item from a previous scan to complete a join or subquery	Function	None
am_getbyid	The name of a user-defined function that fetches data from a specific physical address	Function	None
am_getnext	The name of the required user-defined function that scans for the next item that satisfies the query	Function	None
am_expr_pushdown	A flag that enables the use of parameter descriptors.	Flag	Not set

The following rules apply to the purpose-option specifications in the CREATE PRIMARY ACCESS_METHOD and ALTER ACCESS_METHOD statements:

To specify multiple purpose options in one statement, separate them with commas.
The CREATE PRIMARY ACCESS_METHOD statement must specify a routine name for the am_getnext purpose function.
The ALTER ACCESS_METHOD statement cannot drop am_getnext but can modify it.
The ALTER ACCESS_METHOD statement cannot add, drop, or modify the am_sptype value.