The Adapter for DB2 comes with a Static SQL facility for quickly and easily creating application plans or packages for 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.

When you run static procedures, you take advantage of the following security and performance benefits of static SQL:

• Procedure-level security.

  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.

• Performance improvements.

  Static SQL offers potentially substantial performance improvements for MODIFY procedures. The PREPARE cursor operation that checks syntax and authorization and selects access paths is removed from the run-time environment.

  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. (Introduction to Adapters for Relational Data Sources includes a discussion of basic bind concepts.)

• Ease of Use.

  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.