SYSAMS

The sysams system catalog table contains information that is required for using built-in access methods and those created by the CREATE ACCESS_METHOD statement of SQL.

The sysams table has the following columns.
Table 1. SYSAMS table column descriptions
Column Type Explanation
am_name VARCHAR(128, 0) Name of the access method
am_owner CHAR(32) Name of the owner of the access method
am_id INTEGER Unique identifying code for an access method

This corresponds to the am_id columns in the systables, sysindices, and sysopclasses tables.
am_type CHAR(1) Type of access method: P = Primary; S = Secondary
am_sptype CHAR(3) Types of spaces where the access method can exist:
  • A means the access method supports extspaces and sbspaces. If the access method is built in, such as a B-tree, it also supports dbspaces.
  • D or d means the access method supports dbspaces only.
  • DS means the access method supports dbspaces and sbspaces.
  • S or s means the access method supports sbspaces only.
  • X or x means the access method supports extspaces only.
  • sx means the access method supports sbspaces and extspaces.
am_defopclass INTEGER Unique identifying code for default-operator class

Value is the opclassid from the entry for this operator class in the sysopclasses table.
am_keyscan INTEGER Whether a secondary access method supports a key scan

(An access method supports a key scan if it can return a key and a rowid from a call to the am_getnext function.) (0 = FALSE; Non-zero = TRUE)
am_unique INTEGER Whether a secondary access method can support unique keys (0 = FALSE; Non-zero = TRUE)
am_cluster INTEGER Whether a primary access method supports clustering (0 = FALSE; Non-zero = TRUE)
am_rowids INTEGER Whether a primary access method supports rowids (0 = FALSE; Non-zero = TRUE)
am_readwrite INTEGER Whether a primary access method can both read and write ( 0 = access method is read-only; Non-zero = access method is read/write )
am_parallel INTEGER Whether an access method supports parallel execution (0 = FALSE; Non-zero = TRUE)
am_costfactor SMALLFLOAT The value to be multiplied by the cost of a scan to normalize it to costing done for built-in access methods

The scan cost is the output of the am_scancost function.
am_create INTEGER The routine specified for the AM_CREATE purpose for this access method

Value = procid for the routine in the sysprocedures table.
am_drop INTEGER The routine specified for the AM_DROP purpose function for this access method
am_open INTEGER The routine specified for the AM_OPEN purpose function for this access method
am_close INTEGER The routine specified for the AM_CLOSE purpose function for this access method
am_insert INTEGER The routine specified for the AM_INSERT purpose function for this access method
am_delete INTEGER The routine specified for the AM_DELETE purpose function for this access method
am_update INTEGER The routine specified for the AM_UPDATE purpose function for this access method
am_stats INTEGER The routine specified for the AM_STATS purpose function for this access method
am_scancost INTEGER The routine specified for the AM_SCANCOST purpose function for this access method
am_check INTEGER The routine specified for the AM_CHECK purpose function for this access method
am_beginscan INTEGER The routine specified for the AM_BEGINSCAN purpose function for this access method
am_endscan INTEGER The routine specified for the AM_ENDSCAN purpose function for this access method
am_rescan INTEGER The routine specified for the AM_RESCAN purpose function for this access method
am_getnext INTEGER The routine specified for the AM_GETNEXT purpose function for this access method
am_getbyid INTEGER The routine specified for the AM_GETBYID purpose function for this access method
am_build INTEGER The routine specified for the AM_BUILD purpose function for this access method
am_init INTEGER The routine specified for the AM_INIT purpose function for this access method
am_truncate INTEGER The routine specified for the AM_TRUNCATE purpose function for this access method
am_expr_pushdown INTEGER Reserved for future useWhether parameter descriptors are supported (0 = FALSE; Non-zero = TRUE)

For each of the columns that contain a routine for a purpose function, the value is the sysprocedures.procid value for the corresponding routine.

A composite index on the am_name and am_owner columns in this table allows only unique values. The am_id column has a unique index.

For information about access method functions, see the documentation of your access method.