Purpose options

The database server recognizes a registered access method as a set of attributes, including the access-method name and options called purposes.

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

Syntax

Purpose Option

{ purpose function=function name | purpose value= { string value | numeric value } | purpose flag }


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 Informix® Guide to SQL: Syntax.
`string value`	An indicator that is expressed as one or more characters	None	Quoted String segment; see Informix® Guide to SQL: Syntax.
`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 SECONDARY 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 SECONDARY ACCESS_METHOD statement sets the am_getnext purpose-function name to the UDR name textfile_getnext. This example creates a new 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.

Setting 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 index in a dbspace.	Value	`A`
am_defopclass	The name of the default operator class for this access method. Because the access method must exist before you can define an operator class for it, you set this purpose with the ALTER ACCESS_METHOD statement.	Value	None
am_keyscan	A flag that, if set indicates that am_getnext returns rows of index keys If query selects only the columns in the index key, the database server uses the row of index keys that the secondary access method puts in shared memory, without reading the table	Flag	Not set
am_unique	A flag that you set if the secondary access method checks for unique keys	Flag	Not set
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 secondary 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 execute 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 execute 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.1` - `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 index to the database	Function	None
am_drop	The name of a user-defined function that drops a virtual index	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 an index entry	Function	None
am_delete	The name of a user-defined function that deletes an index entry	Function	None
am_update	The name of a user-defined function that changes the values in a row key	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 performs an integrity check on an index	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

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

To specify multiple purpose options in one statement, separate them with commas.
The CREATE SECONDARY 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.