You can write the program associated with an external procedure in COBOL, PL/I or Assembler. This requires the procedure to be defined with PROTOCOL IDMS. When called, the program is passed a fixed parameter list consisting of the parameters specified on the procedure definition as well as additional parameters used for communication between CA IDMS and the procedure.
Whenever you make a reference to a procedure, CA IDMS calls the program associated with the procedure to service the request. The procedure responds by processing the input parameters. You can optionally set an error condition in SQLSTATE.
CA IDMS performs transaction and session management automatically in response to requests that the originating application issues. Changes to the database made by a procedure are committed or rolled out together with other changes made within the SQL transaction. The procedure requires no special action to ensure this occurs.
The next section discusses writing a procedure in detail.
For an example of a procedure written in COBOL, see Sample COBOL Procedure.
The following sets of arguments pass each time you call a procedure:
The first two sets of arguments vary from one procedure to another. They are used to pass selection criteria and insert/update values to the procedure and result values from the procedure.
The last set of arguments, shown in the table below, is the same for all procedures.
|
Argument |
Contents |
|---|---|
|
Result Indicator (fullword) |
Not used |
|
SQLSTATE (CHAR (5)) |
Status code returned by the procedure: The initial value is always 00000
|
|
Procedure Name (CHAR (18)) |
Name of the procedure |
|
Explicit Name |
Not used |
|
Message Text (CHAR (80)) |
Message text returned by the procedure and displayed by CA IDMS in the event of an error or warning |
|
SQL Command Code (fullword) |
Always 16, indicating a Fetch SQL request. |
|
SQL Operation Code (fullword) |
Always 16, indicating a "next row" request. |
|
Instance Identifier (fullword) |
Not meaningful for procedures |
|
Local Work Area (User-defined) |
A user-defined working storage area |
|
Global Work Area (User-defined) |
A user-defined storage area that can be shared by other SQL routines within a transaction. |
On entry to the procedure, the value of the arguments corresponding to the parameters defined on the CREATE PROCEDURE statement are as follows:
Non-null parameters contain one of the following:
All other parameters contain nulls (that is, the null indicator for the parameter is negative).
On exit expect the procedure to either have set the value of the parameter arguments and their indicators appropriately or to have set an SQLSTATE value indicating no-more-rows. If you set an indicator parameter to -1, CA IDMS ignores the value of the corresponding parameter.
Another parameter passed on each call to a procedure is a local work area.
CA IDMS allocates the local work area just before calling the procedure and frees it immediately after the procedure exits. When CA IDMS allocates the local work area, it is initialized to binary zeros.
A global work area is a storage area that can be shared across one or more procedures or other SQL routines within a transaction. Each global work area has an associated key which is either:
All SQL routines executing within a transaction and having the same global storage key share the same global work area.
Unless transaction sharing is in effect, all SQL routines within an invoking SQL transaction should update the database through only one run unit or SQL transaction to avoid deadlocking. Typically, an update procedure uses a global work area to share the subschema control or SQL session identifier with other SQL routines. A retrieval-only procedure might instead use only a local work area opening the run unit or SQL session and terminating it on exit.
|
Copyright © 2014 CA.
All rights reserved.
|
|