a. PREPARE – This phase implies assigning all the resources in order to support the sentence, verifying that the syntax is correct, that the tables exist, and that they have the attributes mentioned, etc. It is also the stage when the access strategy, (*), for solving the sentence is established. All this has quite a high cost in terms of time. As a result, we recommend doing it the as little as possible. The ideal situation is to do it once for each application’s execution.
b. EXECUTE – In this phase, the sentence is executed. If, for example, the sentence was ‘SELECT ... FROM CLIENTES WHERE CLICOD = ?’, then a value is given to the ‘?’, and you ‘search’ for the client that corresponds to such a value. The access strategy is the one that was determined at step A. Except, in cases of very complex sentences of very high-resolution cost (this is determined by the DBMS’s optimizer) there are no strategy changes on this phase. The Execute is executed as many times as is necessary to ‘start again’.
c. FETCH – This phase is the one that allows accessing the data that resulted from the executed sentence on step B. It only applies if the sentence is a SELECT. In such case, of the INSERTs, UPDATEs or DELETEs the FETCH does not make sense.
It is executed, as a maximum, as many times as resultant rows of the SELECT exist.
d. CLOSE – This phase releases some of the resources consumed by the sentence. In particular, it releases some lock types (depending on the isolation level), rows of the result that have NOT been read by the client, etc.
This phase does NOT release the strategy for accessing the database, in such a way that if another EXECUTE is performed it can be re-used.
e. FREE – This phase, although it does not exist formally, is the one that releases all the resources of a sentence; the strategy is not left, either. In GX, this phase occurs at the end of the program or, if the maximum of open cursors (active sentences) is exceeded (configured in 100, by default).
(*) ACCESS STRATEGY- It is the method that the DBMS selects in order to solve a query. The DBMS’ optimizer is the one that makes the decisions on the access method. The access method means determining, having a given query and a database’s status (tables, indexes, quantity of records, data distribution information, etc.), the most efficient way of solving it. Solving it implies deciding what Table/tables to access first, and how to access them (by index, by which index to access it, if performing sort, if sweeping them all, etc.).