Defining and Using Table Procedures › Writing a Table Procedure

Writing a Table Procedure

The program associated with a table procedure can be written in COBOL, PL/I, or Assembler. When called, the program is passed a fixed parameter list consisting of the parameters specified on the table procedure definition and additional parameters used for communication between CA IDMS and the table procedure.

Whenever a reference to a table procedure is made, CA IDMS calls the program associated with the table procedure to service the request. Part of the information passed to the table procedure is an indication of the type of action that the table procedure is to perform, such as "return the next result row" or "update the current row." The table procedure responds by performing the requested action or returning an error.

CA IDMS performs transaction and session management automatically in response to requests that the originating application issues. Changes to the database made by a table procedure are committed or rolled out together with other changes made within the SQL transaction. No special action is required of the table procedure to ensure this occurs.

The next section discusses writing a table procedure in detail.

For an example of a table procedure written in COBOL, see Sample Table Procedure Program.

Calling Arguments

The following sets of arguments are passed each time a table procedure is called:

• One argument for each of the parameters specified on the table procedure definition, passed in the order the parameters were declared
• One argument for each null indicator associated with a parameter specified in the table procedure definition, passed in the order the parameters were declared
• A set of common arguments used for communications between CA IDMS and the table procedure

The first two sets of arguments vary from one table procedure to another. They are used to pass selection criteria and insert/update values to the table procedure and result values from the table procedure.

The last set of arguments, shown in the next table, is the same for all table procedures.

Table Procedure Requests

Part of the information passed to the table procedure is the type of request being made. This information is conveyed in two parameters:

• The first parameter contains a code indicating the type of SQL statement for which the request is issued (for example, INSERT, OPEN). The table following "SQL command codes" lists valid SQL command codes.
• The second parameter is an internal operation code indicating the type of action expected of the table procedure. The table following "Operation codes" lists possible operation codes.

SQL Command Codes

The following table lists SQL command code values.

Operation Codes

The following table lists operation code values and their meanings:

Both SELECT statements and OPEN/FETCH/CLOSE cursor requests result in the following set of calls to the table procedure:

      Open Scan
        Next Row  (1 to n times)
      Close Scan

A searched UPDATE statement results in the following:

      Open Scan
        Next Row   \ (1 to n times)
        Update Row /
      Close Scan

The table procedure is called repeatedly to return the next row to be updated based on the selection criteria passed on the Open Scan request. The results of the Next Row request are examined by the DBMS to determine whether they satisfy all the WHERE clause criteria specified on the searched update statement. If all criteria are satisfied, the table procedure is then called to update the row. If any criteria are not satisfied, the row is not updated and the table procedure is called instead to retrieve the next row.

A positioned UPDATE statement associated with an open cursor has a similar calling sequence except the invoking application determines whether to update the current row.

Searched and positioned DELETE statements result in similar calling sequences to those for searched and positioned UPDATE statements, except a Delete Row request is issued instead of an Update Row request.

INSERT statements result in a single call to the table procedure for each row to be inserted.

Parameter Arguments

On entry to the table procedure, the value of the arguments corresponding to the parameters defined on the CREATE TABLE PROCEDURE statement vary depending on the type of operation performed:

• On an Open Scan request, non-null parameters contain one of the following:
  • Selection criteria specified in the WHERE clause
  • Parameter values specified on the table procedure reference
  • Data type-specific default value if WITH DEFAULT was specified in the table procedure definition

  All other parameters contain nulls (that is, the null indicator for the parameter is negative).

• On an Update Row request, the parameters contain the values returned from the previous Next Row request, overlaid with the values specified in the SET clause of the UPDATE statement.
• On an Insert Row request, the parameters contain the values specified in the VALUES clause of the INSERT statement or the values returned by the SELECT associated with the INSERT statement. Unspecified values are either null or contain the parameter's default value.
• On other types of requests, the contents of the parameters are undefined on entry.

On exit from a Next Row request, the table procedure is expected either to have set the value of the parameter arguments and their indicators appropriately or to have set an SQLSTATE value indicating no-more-rows. If an indicator parameter is set to -1, CA IDMS ignores the value of the corresponding parameter.

Instance Identifier

On every call issued to a table procedure, a parameter is passed identifying the scan to which the request is directed. In the case of INSERT, this has no meaning. However, in all other cases (SELECT, UPDATE, DELETE, and cursor operations) the instance ID can be used to distinguish one scan from another.

Local Work Area

Another parameter passed on each call to a table procedure is a local work area where the table procedure may save information it wishes to preserve from one call to another. Each scan is allocated its own local work area so that values associated with processing an individual scan may be saved appropriately in a local work area. The types of information which you might need to preserve across calls include:

• Subschema control block for a run unit or the session identifier of an SQL session (for retrieval-only table procedures)
• Database position information
• Input parameter values used as selection criteria

CA IDMS allocates a local work area when a scan is opened and frees it when the scan is closed. Each scan receives its own local work area. When the local work area is allocated, it is initialized to binary zeros.

Global Work Area

A global work area is a storage area that can be shared across one or more table procedures or other SQL routines within a transaction. Each global work area has an associated key which is either:

• The four-character identifier specified on the GLOBAL WORK AREA clause
• The fully-qualified name of the table procedure if no identifier was specified

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 table procedure uses a global work area to share the subschema control or SQL session identifier with other SQL routines. A retrieval-only table procedure might instead use only a local work area for each scan, opening the run unit or SQL session on the Open Scan request and terminating it on the Close Scan request.