Previous Topic: Writing an External Procedure in COBOL, PL/I or AssemblerNext Topic: Defining and Using Functions

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

Writing External Procedures as CA ADS Mapless Dialogs

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

Note: For more information about coding an SQL procedure as a CA ADS, see CREATE PROCEDURE and the examples given in Sample CA ADS Procedure.

Mapless Dialog

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

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

Work Records

To access the procedure parameters, the dialog must include a work record whose name is schema-name.procedure-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 procedure parameters in the ADS process code in the same way as you refer to columns in any SQL table. Null indicator(s) (variables) can be referenced by appending "-I" to the relevant column name (see Usage).

When parameters of a procedure are dropped, added or altered, the dialog that implements the procedure must be recompiled. Failure to do so may result in a DC171066 error message when the procedure 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.procedure-identifier, other records related to the procedure can be included.

ADSO-SQLPROC-COM-AREA is a system-supplied record. The record layout is given 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 procedures. For a description of these parameters, see Calling Arguments.

If the procedure 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 in order 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 procedure definition.

Note: For more information and examples, see Sample CA ADS Procedure.