The DB2 and DB2 for VM Data Adapters come with a Static SQL facility for quickly and easily creating application plans or packages (in DB2) or packages (in DB2 for VM) for both TABLE and MODIFY requests.
The Static SQL for MODIFY facility creates a static SQL module for a compiled MODIFY procedure. The procedure can contain only one MODIFY request, which can contain SQL commands. If the procedure references Dialogue Manager variables, you supply all of the values at compile time and cannot substitute new values at run-time.
The Static SQL for TABLE facility can generate static SQL modules for most FOCUS TABLE or MATCH FILE requests in any FOCEXEC. The FOCEXEC can also contain other FOCUS commands, such as MODIFY requests, SET commands, or Dialogue Manager logic; however, the Static SQL for TABLE facility translates only the TABLE and MATCH FILE requests into static SQL. A TABLE request can reference Dialogue Manager variables, and you can substitute new values when you execute the procedure. In fact, you can edit the request without re-submitting it to the Static SQL for TABLE facility as long as changes to the request do not affect the generated SQL.
When you run static procedures, you take advantage of the following security and performance benefits of static SQL:
Privileges (for example, SELECT, UPDATE, or DELETE) for operations on a table are available only to authorized users of the application plan (or package) associated with the static procedure. Users have no access to underlying tables outside of the procedure. Under dynamic SQL, privileges must be granted on the actual tables. Many sites do not allow dynamic access to tables because users can then access the same tables using other programs, such as QMF™. Static SQL specifically addresses this security concern.
MODIFY procedures tend to have many more SQL statements than TABLE requests, and each statement executed must first be prepared. Furthermore, the access path is lost each time the procedure executes a COMMIT or ROLLBACK WORK statement, even if the SQL statement has already been prepared. In procedures that COMMIT after each transaction, the same SQL statements must be prepared prior to every transaction. Many interactive programs COMMIT after each transaction to release locks and minimize the impact of a system or program failure. These programs benefit most from static SQL.
If you do not normally compile your MODIFY procedures, you will probably notice that it takes less time to initialize the static procedure. FOCUS has already interpreted the MODIFY commands, and the time required to initialize the procedure is a function of compiled MODIFY rather than of static SQL.
Static SQL does not increase the actual speed of data retrieval or update. In a static SQL procedure, the time required to retrieve and/or update a given set of rows is identical to that of the same procedure executing dynamically, minus the cost of access path selection and authorization checking for the SQL statements.
Note:
If RDBMS table statistics change, or if table or index structures are altered after the static procedure is bound, the procedure should be rebound to ensure optimum performance. Refer to Optionally BIND the Plan for the FOCEXEC for a discussion of BIND in DB2; see Execute the GENUSQL EXEC for corresponding information in DB2 for VM. (Introduction to Relational Data Adapters, includes a discussion of basic bind concepts.)
The FOCUS implementation of static SQL offers you a far less complex and time-consuming development cycle than is normally the case with static SQL applications. No third-generation language or SQL skills are required. In fact, you can write a FOCUS application to use static SQL without coding a single SQL statement. Additionally, you can develop, test, and revise your FOCEXEC using dynamic SQL without going through the cumbersome static SQL preparation process every time you make a change. You do not have to create the application plan or package until the end of the development cycle.
| Information Builders |