Static SQL Requirements

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 or DB2 for VM tables (and can currently be compiled), and most current FOCEXEC procedures that access DB2 or DB2 for VM tables using the TABLE FILE or MATCH FILE commands, 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). A FOCEXEC to be compiled with the Static SQL for TABLE facility can contain SQL commands, MODIFY procedures, Direct SQL Passthru commands, and Dialogue Manager commands, although the Static SQL for TABLE facility does not convert these commands to static SQL. 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.

A TABLE request can contain Dialogue Manager variables, and you can substitute new values when you execute the static procedure (see SQL COMPILE and SQL RUN Processing).

• 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.
• For DB2:
• TSO ATTACH is not supported.

The DB2 Data Adapter must have been installed to use the Call Attachment Facility (CAF). You can verify this by using the SQL DB2 ? command to display Data Adapter settings (Call Attachment Facility should be ON). There is no similar requirement for DB2 for VM.

• 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 data 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.

• For DB2 for VM:

The DB2 for VM product does not support double-byte character set (DBCS) data types in static SQL procedures of the type generated by the data adapter.

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.