Register the access method

The CREATE FUNCTION statement identifies a function as part of a database, but not necessarily as part of an access method. To register the access method, issue the CREATE PRIMARY ACCESS_METHOD statement, which sets values in the SYSAMS system catalog table.

The CREATE PRIMARY ACCESS_METHOD statement sets values in the SYSAMS system catalog table, such as:
  • The unique name of each purpose function
  • A storage-type (extspaces or sbspaces) indicator
  • Flags that activate optional features, such as writable data, clustering, or using parameters in qualification descriptors
The following example assigns registered function names to some purpose functions, specifies that the access method uses sbspaces, enables clustering, and enables the use of parameters in qualification descriptors:
CREATE PRIMARY ACCESS_METHOD my_virtual
(  AM_OPEN = open_virtual,
   AM_CLOSE = close_virtual,
   AM_CREATE = create_virtual,
   AM_DROP = drop_virtual,
   AM_BEGINSCAN = beginscan_virtual,
   AM_GETNEXT = getnext_virtual,
   AM_ENDSCAN = endscan_virtual,
   AM_INSERT = insert_virtual,
   AM_DELETE = delete_virtual,
   AM_UPDATE = update_virtual,
   AM_READWRITE,
   AM_ROWIDS,
   AM_SPTYPE = S,
   AM_CLUSTER,
   AM_EXPR_PUSHDOWN)
The resulting SYSAMS system catalog entry for the new access method is:
am_name          my_virtual
am_owner         informix
am_id            101
am_type          P
am_sptype        S
am_cluster       1
am_rowids        1
am_readwrite      1
am_parallel      0
am_costfactor    1.000000000000
am_create        162
am_drop          163
am_open          164
am_close         165
am_insert        166
am_delete        167
am_update        168
am_stats         0
am_scancost      0
am_check         0
am_beginscan     169
am_endscan       170
am_rescan        0
am_getnext       171
am_expr_pushdown 1
The CREATE PRIMARY ACCESS_METHOD statement does not name a purpose function for am_stats, am_scancost, or am_check, hence those flags have a value of 0 in the SYSAMS system catalog entry. The database server sets a 0 value for am_parallel because none of the CREATE FUNCTION statements for the purpose functions included the PARALLELIZATION routine modifier.
Important: Even if you supply and register a purpose function with the CREATE FUNCTION statement, the database server assumes that a purpose function does not exist if the purpose-function name in the SYSAMS system catalog table is missing or misspelled.