To reduce the overhead required to repeatedly execute a select query statement, specify the query as a repeated query. For repeated queries, the DBMS Server saves the query execution plan after the first time the query is executed. This can account for significant performance improvements on subsequent executions of the same select.
If your application needs to be able to change the search conditions, dynamically constructed search conditions cannot be used with repeated queries. The saved execution plan is based on the initial value of the search condition and subsequent changes are ignored.
The cursor select statement is specified as part of a declare cursor statement. Within the declare cursor statement, the select statement is not preceded by EXEC SQL. The cursor select statement specifies the data to be retrieved by the cursor. When executed, the declare cursor statement does not perform the retrieval-the retrieval occurs when the cursor is opened. If the cursor is declared for update, the select cannot see more than one table, cannot see a view and cannot include a group by, having, order by, or union clause.
The cursor select can return multiple rows, because the cursor provides the means to process and update retrieved rows one at a time. The correlation of expressions to host language variables takes place with the fetch statement, so the cursor select does not include an into clause. The rules for the remaining clauses are the same as in the non-cursor select.
If the select statement retrieves no rows, the SQLCA variable sqlcode is set to 100. The number of rows returned from the database is in the SQLCA variable sqlerrd(3). In a select loop, if the endselect statement was issued, sqlerrd(3) contains the number of rows retrieved before endselect was issued.
ost language variables can be used as expressions in the select clause and the search_conditions. Variables used in search_conditions must specify constant values and cannot represent names of database columns or include any operators. Host string variables can also substitute for the complete search condition.