Relating SQL DML statements to Table Procedure calls

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

Table procedures are a type of SQL callable procedure supported by CA IDMS as an extension to the ANSI standard. They are differentiated from procedures in that they return a set of data per call, as opposed to a single row. Table procedures are invoked directly by the SQL engine when a table procedure is listed as a data source in an SQL DML statement. Because of this, it's important to understand how a CA IDMS SQL DML statement is translated into one or more invocations of a table procedure. Also, the code of a table procedure must be highly structured and written to interpret and respond appropriately to the parameters passed when the program is invoked.

Environment

IDMS - All Supported Releases

Resolution

The SQL engine translates SQL DML commands into units of work called scans. 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 specific 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. When the SQL DML references a table procedure as the data source, the engine invokes the table procedure to process the scans.

Each scan is comprised of one or more operations, each of which is identified by a specific code. One of the parameters passed from the SQL engine to the table procedure is an internal operation code indicating the type of action expected of the procedure. The table below lists possible operation codes, along with the value passed to the table procedure and the description of the work that the engine is expecting the table procedure to perform.

Code	Value	Description
Open Scan	Value 12	Requests that the procedure prepare itself for returning a set of result rows. Selection criteria specified in the WHERE clause or in the procedure reference are passed as arguments to the procedure.
Next Row	Value 16	Requests that the procedure return the next result row for the indicated scan. Next Row requests are repeated in order to return all of the result rows for a scan. The procedure can set an SQLSTATE value indicating that all rows have been returned.
Close Scan	Value 20	Informs the procedure that no further Next Row requests will be issued for the scan. The procedure may free resources in response to this request.
Update Row	Value 40	Requests that the 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 that the 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 that the procedure insert a row into the database using the values of the passed parameters as the insert values.
Suspend Scan	Value 24	Informs the procedure that the SQL session is being suspended. The procedure may release resources in response to this request.
Resume Scan	Value 28	Informs the procedure that the indicated scan is being resumed following a suspend operation. The procedure may re-establish its state if necessary.

SQL DML statements will translate to calls with operation codes as follows:

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

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

A searched UPDATE statement (one with a WHERE clause specifying data comparison criteria) will result in the following:

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

The 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 of the WHERE clause criteria specified on the searched update statement. If all criteria are satisfied, the procedure is then called to update the row. If any criteria are not satisfied, the row is not updated and the procedure is called instead to retrieve the next row.

A positioned UPDATE statement associated with an open cursor (WHERE CURRENT OF CURSOR) does not require the Open and Close Scans, and there is no iteration so only one Update Row call is executed.

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

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

Another way to understand this is through the following chart:

SQL DML Statement	Open Scan	Next Row	Delete Row	Update Row	Close Scan	Insert Row	Suspend Scan	Resume Scan
SELECT	X	Y			X
DELETE Searched	X	Y	Y		X
UPDATE Searched	X	Y		Y	X
DELETE Positioned			X
UPDATE Positioned				X
FETCH		X
INSERT						X
OPEN	X
RESUME
SUSPEND

X=Called Once         Y=Called 1 to   n, in loop with other Y until all applicable rows found

As the last seven lines of this chart demonstrate, some DML statements get translated into a single call to a table procedure for one operation code. As the first three lines show, others get translated into multiple calls, where each one has a specific operation code. In these situations where looping occurs, the engine will loop between 1 to many retrievals (next row calls); followed by an update DML (for searched delete row or update row); then the process repeats until no more rows are found that satisfy the specified criteria.

The chart may also be read vertically. The table procedure code to handle an Open Scan will have to work correctly whether it is called by a DELETE, OPEN, SELECT, or UPDATE. A next Row will have to work correctly whether it is called for a DELETE, FETCH, SELECT, or UPDATE. And it must be able to determine what record it is to return, and it may need to know whether this is the first or nth row being returned, depending on what logic is involved. This level of complexity demonstrates why thorough testing of a table procedure is important, and why many users rely on CA IDMS SQL Quick Bridge to generate the necessary control statements and logic flow when a table procedure is required. For more information on these aspects of development, see other online support knowledge documents addressing CA IDMS SQL Quick Bridge, and table procedure testing.