Defer execution of the PREPARE statement

When the application uses a PREPARE/DECLARE/OPEN statement block to execute a cursor, each statement involves a round trip of message requests between the application and the database server. The Deferred-PREPARE feature reduces the number of round trips by one. When the Deferred-PREPARE feature is enabled, saves a round trip of message requests because it does not need to send a separate command to execute the PREPARE statement. Instead, the database server automatically executes the PREPARE statement when it receives the OPEN statement.

Suppose you enable the Deferred-PREPARE feature for the following select cursor: 
/* Select cursor associated with a SELECT statement */
EXEC SQL prepare slct_stmt FOR
   'select * from customer';
EXEC SQL declare sel_curs cursor for slct_stmt;
EXEC SQL open sel_curs;

The application does not send the PREPARE statement to the database server when it encounters the PREPARE before the DECLARE statement. Instead, it sends the PREPARE and the OPEN to the database server together when it executes the OPEN statement.

You can use the Deferred-PREPARE feature in applications that contain dynamic SQL statements that use statement blocks of PREPARE, DECLARE, and OPEN to execute the following statements:
  • SELECT statements (select cursors)
  • EXECUTE FUNCTION statements (function cursors)
  • INSERT statement (insert cursors)
For example, the Deferred-PREPARE feature reduces network round trips for the following select cursor: 
/* Valid select cursor for Deferred-PREPARE optimization */
EXEC SQL prepare sel_stmt 'select * from customer';
EXEC SQL declare sel_curs cursor for sel_stmt;
EXEC SQL open sel_curs;