Write purpose functions

The Virtual-Table Interface (VTI) specifies the parameters and return values for a limited set of UDRs, called purpose functions, that correspond to one or more SQL statements.

For most SQL statements, the database server attempts to start a sequence of task-specific purpose functions to process the statement. You choose the tasks and SQL statements that the access method supports and then write the appropriate purpose functions for those tasks.

The following table shows purpose-function prototypes for access-method tasks and one or more corresponding SQL statements. The table includes the purpose function prototype that the database server calls to process the oncheck utility.

Table 1. Statements and their purpose functions
Invoking statement or command	Purpose-function prototype
All If you do not supply am_open and am_close, open and close the data source in am_getnext.	`am_open(MI_AM_TABLE_DESC ) am_close(MI_AM_TABLE_DESC )`
CREATE TABLE	`am_create(MI_AM_TABLE_DESC *)`
DROP TABLE	`am_drop(MI_AM_TABLE_DESC *)`
INSERT	`am_insert(MI_AM_TABLE_DESC , MI_ROW , mi_integer *)`
DELETE	`am_delete(MI_AM_TABLE_DESC , mi_integer )`
SELECT INSERT, UPDATE, DELETE WHERE...	`am_scancost(MI_AM_TABLE_DESC , MI_AM_QUAL_DESC ) am_beginscan(MI_AM_SCAN_DESC ) am_getnext(MI_AM_SCAN_DESC , MI_ROW *, mi_integer )am_endscan(MI_AM_SCAN_DESC *)`
SELECT with join	`am_rescan(MI_AM_SCAN_DESC *)`
SELECT using an index	`am_getbyid(MI_AM_TABLE_DESC , MI_ROW *, mi_integer)`
UPDATE	`am_update(MI_AM_TABLE_DESC , MI_ROW , mi_integer )`
UPDATE STATISTICS	`am_stats(MI_AM_TABLE_DESC , MI_AM_TSTATS_DESC )`
oncheck utility	`am_check(MI_AM_TABLE_DESC *, mi_integer)`

Restriction: Do not use the purpose label (am_open, am_create, am_getnext) as the actual name of a user-defined purpose function. Avoid names such as vti_open, vti_create, vti_*.

You must assign unique names, such as image_open, docfile_open, and getnext_record.

When the database server calls a purpose function, it passes the appropriate parameters for the current database server activity. Most parameters reference the opaque descriptor data structures. The database server creates and passes descriptors to describe the state of the table and the current SQL statement or oncheck command.

As you write the purpose functions, adhere to the syntax provided for each function.

At a minimum, you must supply one purpose function, the am_getnext purpose function, to scan data. To determine which other purpose functions to provide, decide if the access method should support the following tasks:

Opening and initializing files or smart large objects, as well as closing them again at the end of processing
Creating new tables
Inserting, updating, or deleting data
Running the oncheck utility
Optimizing queries

Important: The database server issues an error if a user or application tries to execute an SQL statement, and the access method does not include a purpose function to support that statement.

The following sections name the functions that the database server calls for the specific purposes in the previous list. The access-method library might contain a separate function for each of several purpose-function prototypes or supply only an am_getnext purpose function as the entry point for all the essential access-method processing.