Optimize OPEN, FETCH, and CLOSE
When the application
uses DECLARE and OPEN statements to execute a cursor, each statement
involves a round trip of message requests between the application
and the database server. The optimize-OPEN-FETCH-CLOSE feature (OPTOFC)
reduces the number of round trips by two, as follows:
- saves
one round trip because it does not send the OPEN statement as a separate
command.
When executes the OPEN statement, it does not open the cursor. Instead, it saves any input value that was supplied in the USING clause of the OPEN statement. When executes the initial FETCH statement, it sends this input value along with the FETCH statement. The database server opens the cursor and returns the first value in this cursor.
- saves
a second round trip because it does not send the CLOSE statement as
a separate command.
When the database server reaches the last value of an open cursor, it automatically closes the cursor after it sends the last value to the client application. Therefore, does not need to send the CLOSE statement to the database server.
Important: does
not send the CLOSE statement to the database server. However, if you
include the CLOSE statement, no error is generated.