Previous Topic: Writing an External Function in COBOL, PL/I, or AssemblerNext Topic: Considerations for SQL-invoked External Routines

Defining and Using Functions › Writing External Functions as CA ADS Mapless Dialogs

Writing External Functions as CA ADS Mapless Dialogs

You can also code an SQL function as a CA ADS mapless dialog. This requires the function to be defined with PROTOCOL ADS and SYSTEM MODE. The name of the dialog that is loaded and run when the SQL function is invoked is specified in the EXTERNAL NAME clause of the CREATE/ALTER FUNCTION statement.

Note: For more information, see CREATE FUNCTION and the examples given in Sample CA ADS Function.

Mapless Dialog

The ADS dialog that implements the SQL function must be mapless. The name of this mapless dialog is specified as external-routine-name in the external name clause of the function definition.

To return to the SQL engine, the CA ADS premap process of the mapless dialog must issue a LEAVE ADS.

Work Records

To access the function parameters and the result parameter USER_FUNC, the dialog must include a work record whose name is schema-name.function-identifier. This record is not copied from the dictionary but instead is automatically constructed by the CA ADS dialog compiler (ADSC or ADSOBCOM) when it compiles the dialog. You can refer to the function parameters and the result parameter USER_FUNC and the corresponding null indicators in the ADS process code in the same way as you refer to columns in any SQL table.

When parameters of a function are dropped, added or altered, the dialog that implements the SQL function must be recompiled. Failure to do so may result in a DC171066 error message when the function is next executed. The runtime validation producing this message is based solely on the size of the record.

Additional Records

Besides the pseudo-work-record, schema-name.function-identifier, other records related to the function can be included.

ADSO-SQLPROC-COM-AREA is a system-supplied record. The record layout is shown next:

ADD RECORD NAME ADSO-SQLPROC-COM-AREA.
  03 FILLER                    PIC S9(8) COMP SYNC.
  03 FILLER                    PIC X(3).
  03 SQLPROC-SQLSTATE          PIC X(5).
  03 SQLPROC-NAME              PIC X(18).
  03 SQLPROC-SPECIFIC-NAME     PIC X(18).
  03 SQLPROC-MSG-TEXT          PIC X(80).
  03 SQLPROC-COMMAND-CODE      PIC S9(8) COMP SYNC.
  03 SQLPROC-OPERATION-CODE    PIC S9(8) COMP SYNC.
  03 SQLPROC-INSTANCE-ID       PIC S9(8) COMP SYNC.
  03 FILLER            OCCURS 2.

The non-FILLER elements of the ADSO-SQLPROC-COM-AREA record are the parameters that are common to all SQL functions. For a description of these parameters, see Calling Arguments.

If the function definition contains a LOCAL or GLOBAL WORKAREA clause, you can define corresponding records in the dictionary. While the layout of these records is application dependent, the name must comply with the following rules for the ADS runtime to properly initialize these records:

The dialogname is the name of the dialog, specified as external-routine-name in the external name clause of the function definition.