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 SECONDARY ACCESS_METHOD statement, which sets values in the sysams system catalog table.

The CREATE SECONDARY 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 or clustering

The sample statement in the following figure assigns registered function names to some purpose functions. It specifies that the access method should use sbspaces and it enables clustering.

Figure 1. Statement that assigns registered function names to some purpose functions

CREATE SECONDARY 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)

The following figure shows the resulting sysams system catalog entry for the new access method.

Figure 2. Register a secondary access method

am_name        my_virtual
am_owner       informix
am_id          101
am_type        S
am_sptype      S
am_defopclass  0
am_keyscan     0
am_unique      0
am_cluster     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

The statement in Figure 1 does not name a purpose function for am_stats, am_scancost, or am_check, or set the am_keyscan or am_unique flag, as the 0 values in Figure 2 indicate. 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.