Using OPTOFC and Deferred-PREPARE together

To achieve the most optimized number of messages between the client application and the database server, use the Optimize OPEN, FETCH, CLOSE feature, and the Deferred-PREPARE feature together.

However, keep in mind the following requirements when you use these two optimization features together:
  • If syntax errors exist in the statement text, the database server does not return the error to the application until it executes the FETCH.

    does not send the PREPARE, DECLARE, and OPEN statements to the database server until it executes the FETCH statement. Therefore, any errors that any of these statements generate are not available until the database server executes the FETCH statement.

  • You must use a special case of the GET DESCRIPTOR statement to obtain DESCRIBE information for a prepared statement.

    Typical use of the DESCRIBE statement is to execute it after the PREPARE to determine information about the prepared statement. However, with both the OPTOFC and Deferred-PREPARE features enabled, does not send the DESCRIBE statement to the database until it reaches the FETCH statement. To allow you to obtain information about the prepared statement, executes a statement similar to the SET DESCRIPTOR statement to obtain data type, length, and other system-descriptor fields for the prepared statement. You can then use the GET DESCRIPTOR statement after the FETCH to obtain this information.

    Also, can only perform data conversions on the host variables in the GET DESCRIPTOR statement when the data types are built-in data types. For opaque data types and complex data types (collections and row types), the database server always returns the data to the client application in its native format. You can then perform data conversions on this data after the GET DESCRIPTOR statement.

    For example, the database server returns data from an opaque-type column in its internal (binary) format. Therefore, your program must put column data into a var binary (or fixed binary) host variable when it executes the GET DESCRIPTOR statement. The var binary and fixed binary data types hold opaque-type data in its internal format. You cannot use an lvarchar host variable to hold the data, because cannot convert the opaque-type data from its internal format (which it receives from the database server) to its external (lvarchar) format.

  • The FetArrSize feature does not work when both the Deferred-PREPARE and OPTOFC features are enabled. When these two features are enabled, does not know the size of a row until after the FETCH completes. By this time, it is too late for the fetch buffer to be adjusted with the FetArrSize value.
Tip: To obtain the maximum optimization, use the OPTOFC, Deferred-PREPARE, and AUTOFREE features together.