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.
idmscu
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.
For an example of a table procedure written in COBOL, see Sample Table Procedure Program.
The next section discusses writing a table procedure in detail.
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.
Argument
Contents
Result Indicator (fullword)
Not used
SQLSTATE (CHAR (5))
Status code returned by the table procedure:
00000 -- Indicates success
01Hxx -- Indicates a warning
02000 -- Indicates no more rows
38xxx -- Indicates an error
Table Procedure Name (CHAR (18))
Name of the table procedure
Explicit Name
Not used
Message Text (CHAR (80))
Message text returned by the table procedure and displayed by CA IDMS in the event of an error or warning
SQL Command Code (fullword)
Code indicating the type of SQL request for which the table procedure is being called. See Table Procedure Requests for a list of valid command codes.
SQL Operation Code (fullword)
Code indicating the type of request being made of the table procedure. See Table Procedure Requests for a list of valid operation codes.
Instance Identifier (fullword)
A unique value identifying the scan on which the table procedure is to operate.
Local Work Area (User-defined)
A user-defined storage area maintained across calls to the table procedure.
Global Work Area (User-defined)
A user-defined storage area maintained across calls to the table procedure and capable of being shared by other SQL routines.
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.
Command number
Statement type
1
Logical DDL
3
CLOSE
4
COMMIT
5
COMMIT continue
6
COMMIT release
7
CONNECT
8
DECLARE
9
DELETE searched
10
DELETE positioned
11
DESCRIBE
12
EXECUTE
13
TERMINATE
14
EXECUTE IMMEDIATE
16
FETCH
17
INSERT
18
LOCK TABLE
19
OPEN
20
PREPARE
21
RESUME
22
RELEASE
23
ROLLBACK
24
ROLLBACK release
25
SELECT
26
SET ACCESS MODE
27
SET TRANSACTION
28
SUSPEND
29
UPDATE searched
30
UPDATE positioned
31
SET COMPILE
32
SET SESSION
Operation Codes
The following table lists operation code values and their meanings:
Code
Value
Description
Open Scan
Value 12
Requests the table procedure prepare itself for returning a set of result rows. Selection criteria specified in the WHERE clause or in the table procedure reference are passed as arguments to the table procedure.
Next Row
Value 16
Requests the table procedure return the next result row for the indicated scan. Next Row requests are repeated to return all the result rows for a scan. The table procedure can set an SQLSTATE value indicating that all rows have been returned.
Close Scan
Value 20
Informs the table procedure that no further Next Row requests will be issued for the scan. The table procedure may free resources in response to this request.
Update Row
Value 40
Requests the table procedure update the "current" row of the indicated scan using the values of the passed parameters as the update values. Update Row requests are issued in response to either searched or positioned UPDATE statements.
Delete Row
Value 36
Requests the table procedure delete the "current" row of the indicated scan. Delete Row requests are issued in response to either searched or positioned DELETE statements.
Insert Row
Value 32
Requests the table procedure insert a row into the database using the values of the passed parameters as the insert values.
Suspend Scan
Value 24
Informs the table procedure the SQL session is being suspended. The table procedure may release resources in response to this request.
Resume Scan
Value 28
Informs the table procedure the indicated scan is being resumed following a suspend. The table procedure may re-establish its state if necessary.
Note: The term
scan
refers to a set of related operations performed on behalf of one or more SQL statements. A SELECT statement is associated with a separate scan. Similarly, each searched UPDATE or searched DELETE statement is associated with a separate scan. However, all statements referencing the same cursor are associated with the same scan.
 
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.