You can implement many existing dynamic applications as static procedures with little preparatory work. A FOCEXEC does not require special coding (for example, embedded SQL) to use static SQL. All current MODIFY procedures that update DB2 tables (and can currently be compiled), can be registered as static SQL procedures with no alterations.

A MODIFY procedure to be compiled with the Static SQL for MODIFY facility can also contain SQL commands (for example, SQL COMMIT WORK, INSERT, or UPDATE). The FOCEXEC can also invoke other FOCEXECs; however, to run these additional FOCEXECs statically, you must convert them separately to static SQL.

You must satisfy the following requirements to create a static SQL procedure:

• A MODIFY procedure can contain only one MODIFY request. It cannot contain any other FOCUS or operating system commands. If the FOCEXEC contains Dialogue Manager commands, FOCUS prompts you for the values of any variables at compilation time. You cannot substitute new values at run time.
• All tables used in a MODIFY procedure must have primary keys. That is, you may not include any table whose Access File specifies KEYS=0.
• A MODIFY procedure cannot use the WITH-UNIQUES method for processing unique segments.
• The Adapter for DB2 must have been installed to use the Call Attachment Facility (CAF). You can verify this by using the SQL DB2 ? command to display adapter settings (Call Attachment Facility should be ON).
• You cannot compile a MODIFY FOCEXEC as a static procedure if it accesses a DB2 view created with a GROUP BY or HAVING clause.

  The DB2 environment prohibits the type of static SQL syntax the adapter uses against a DB2 view created with a GROUP BY or HAVING clause. If you issue a static COMPILE for a FOCEXEC that operates against such a view, an SQLCODE of -815 results either at BIND or RUN time, depending on your current level of DB2 maintenance.

• Static SQL is not supported for tables with date-time columns.

Note:

• Certain resource restrictions exist for any procedure that uses static SQL. Under some circumstances they may make it impossible to compile a MODIFY procedure to use static SQL. Resource Restrictions explains these restrictions and how to determine whether your procedure may be affected.
• If your table contains a TEXT (LONG VARCHAR) field, place the description of that field at the end of the segment declaration for that table in the Master File. No change has to be made to the RDBMS table. If you have more than one TEXT field in a table, place one of them last. It does not matter where you place the other TEXT field.
• The FOCCOMP library member for a MODIFY procedure contains a record of the fact that the procedure uses static SQL. To have both static and dynamic versions of the compiled procedure, you need two FOCCOMP libraries, one compiled with STATIC ON or NOBIND, and the other compiled with STATIC OFF.
• The DB2 default value of 100 for the FETCHSIZE parameter is incompatible with static MODIFY procedures. To run a compiled MODIFY procedure, you must SET FETCHSIZE to 1.