The data adapter SQL COMPILE facility creates static TABLE procedures. To execute a static TABLE procedure, you must use the data adapter SQL RUN facility. |
In this section: Creating a Static FOCEXEC With the SQL COMPILE Facility Executing a Static FOCEXEC With the SQL RUN Facility SQL COMPILE and SQL RUN Processing Reference: |
The SQL COMPILE facility offers several options for generating static SQL modules depending on the functions your FOCEXEC performs. Choosing the proper combination of compilation options for each FOCEXEC requires careful consideration.
The syntax for invoking the SQL COMPILE facility is
SQL [sqlengine] COMPILE focexec [-OPTION(options)] [parameters]
END
where:
The following options are available with the SQL COMPILE facility:
INTERPRET |
This option simultaneously executes the FOCEXEC and generates the static SQL module. Use it to compile FOCEXECs containing TABLE requests that execute conditionally depending on output from other TABLE requests (for example, by testing the value of &LINES). Without this option, TABLE requests do not produce any output; therefore, the facility may fail to process some conditionally executed logic. Note: The INTERPRET option is mandatory if the FOCEXEC contains MATCH FILE commands. |
FROM(fxname) |
fxname is the name of a FOCEXEC that calls the FOCEXEC being compiled. No spaces are allowed between the word FROM and the opening parenthesis. Use this option to compile a FOCEXEC that is usually called from another FOCEXEC (for example, a FOCEXEC that displays a menu and sets ampersand variables that are used by the FOCEXEC being compiled). |
REPEAT[(n)] |
This option processes the FOCEXEC n times, each time appending the generated SQL to the static module being created. No spaces are allowed between the word REPEAT and the opening parenthesis. If you omit n, FOCUS asks after each execution whether to end or execute the FOCEXEC again. Use this option to generate all possible versions of SQL when values assigned to ampersand variables can affect the generated SQL. Assign values for variables by either prompting for them with Dialogue Manager, or by using the TED option. |
TED |
This option lets you edit the FOCEXEC with the TED editor prior to compiling it. You can use this option in conjunction with the REPEAT option to make incremental changes at each iteration. |
Note:
For more information on the SQL generation process at both SQL COMPILE and SQL RUN time, see SQL COMPILE and SQL RUN Processing.
The syntax for executing a module created by the SQL COMPILE facility is
SQL [sqlengine] RUN focexec [parameters]
END
where:
For more information on the SQL generation process at both SQL COMPILE and SQL RUN time, see SQL COMPILE and SQL RUN Processing.
To make optimal use of the Static SQL for TABLE facility, you should understand how the SQL COMPILE and SQL RUN commands generate SQL.
When you invoke the SQL COMPILE facility, TABLE or MATCH FILE commands that access DB2 or DB2 for VM tables generate input into two modules:
As a result of certain FOCUS TABLE and MATCH FILE request syntax, FOCUS generates SQL host variables in the created SQL statements. Host variables affect both the ability to substitute new variable values at SQL RUN time and RDBMS access path selection. The RDBMS may select different access paths when the request uses host variables because the optimizer must estimate what proportion of records in the table will pass the screening conditions.
In general, the SQL COMPILE facility substitutes an SQL host variable for any literal that appears to the right of a comparison operator in an IF or WHERE screening condition, whether or not the literal represents an ampersand variable in the FOCEXEC. Since FOCUS performs variable substitution prior to invoking the data adapter, the data adapter cannot distinguish between hard-coded values and values substituted for ampersand variables. Therefore, to support variable substitution for literals in screening conditions at run time, the SQL COMPILE facility assumes that all literals following comparison operators represent variables.
In addition, if varying the values of ampersand variables in a request could change the generated SQL, you must use the REPEAT option of the SQL COMPILE command to enumerate all possible values and generate all possible variations of SQL in a single execution of the SQL COMPILE facility. Examples of such variables include variable file names or field names in a FOCEXEC, or variables that cause the data adapter OPTIMIZATION setting to change, thereby altering the generated SQL.
When you invoke the SQL RUN facility, FOCUS executes the FOCEXEC as usual. In addition, the data adapter re-generates SQL statements in memory from any TABLE or MATCH FILE requests that reference DB2 or DB2 for VM tables.
However, instead of transmitting the SQL to the RDBMS using the dynamic data adapter plan, the data adapter invokes the STUBLIB load module and loads the RDBMS application plan or package. It then searches the static SQL statements in the plan or package until it finds a statement that exactly matches the re-generated SQL. If the data adapter locates an appropriate statement in the plan or package, it executes the statement. If it cannot find a matching statement in the plan or package, it issues a FOC1526 error, and execution terminates.
This run-time regeneration and comparison provides increased flexibility. You can change the source FOCEXEC and, as long as the changes do not affect the generated SQL, you can run the static FOCEXEC without re-submitting it to the SQL COMPILE facility. You need only recompile a FOCEXEC if the generated static SQL statements are different from those generated by the most recent SQL COMPILE.
Note:
Note: Since the AUTODB2 and AUTOSQL facilities make extensive use of the FOCUS IF/WHERE EQ (ddname) feature, you should not compile them for static SQL.
Note:
In order for the RDBMS to consider index access for an SQL predicate containing host variables, the DB2 and DB2 for VM query optimizers require the lengths of those host variables to be compatible with the lengths of the corresponding columns in the RDBMS. FOCUS uses the ACTUAL attribute from the Master File to create host variable descriptions in the generated Assembler program. Therefore, the length of the ACTUAL attribute for fields used in screening conditions or as join fields in cross-referenced files (in FOCUS-managed joins) must be equal to the length of the column in the RDBMS.
However, for packed decimal fields, the ACTUAL attribute alone does not provide enough information to determine the exact precision of the RDBMS DECIMAL column. For example, an ACTUAL attribute of P6 could correspond to a DECIMAL column of precision either 10 or 11. The PRECISION attribute of the Access File (described in Describing Tables to FOCUS) supplies the required information.
You can activate the STMTRACE to determine where the Static SQL for TABLE facility inserted host variables in the generated SQL (see Tracing Data Adapter Processing, for information on activating data adapter traces).
For example, consider the following FEX1 FOCEXEC and SQL COMPILE request (DB2 for VM Static TABLE Example illustrates the compile process for this request):
SQL DB2 SET OPTIMIZATION ON
TABLE FILE EMPLOYEE
PRINT EMP_ID SALARY POINTS
WHERE SALARY GE &SALARY
END
SQL DB2 COMPILE FEX1 SALARY=50000
The following command executes the static FEX1 module created by the preceding example:
SQL DB2 RUN FEX1 SALARY = 50000
END
The STMTRACE output from the SQL COMPILE or SQL RUN request follows:
SELECT T1.EMP_ID,T1.SALARY,T2.POINTS FROM "USER1"."PAYROLL" T1,
"USER1"."COURSES" T2 WHERE (T2.EMP_NUM = T1.EMP_ID) AND
(T1.SALARY >= :H);
The symbol :H (to the right of the comparison operator, >=, in the screening condition on T1.SALARY) represents the SQL host variable. In the example, the SQL RUN command supplies the value 50000 for this variable. Since the variable exists in the generated SQL, you can substitute a value at SQL RUN time, as long as the TABLE request includes a FOCUS ampersand variable in the corresponding location (&SALARY in FEX1).
At SQL COMPILE time, FOCUS substitutes SQL host variables for all literals to the right of logical operators in FOCUS IF and WHERE screening conditions. This substitution allows you to enter values for ampersand variables at SQL RUN time. FOCUS creates these host variables even if the operand is not an ampersand variable. That is, if the literal 50000 had been explicitly coded in the FOCEXEC, FOCUS would still have inserted the :H host variable in the generated SQL. This may have RDBMS optimization implications, especially in the case of SQL range predicates (for example, < or >), because the RDBMS optimizer has to make assumptions concerning data distribution when choosing the access path to the data.
If you are concerned about RDBMS optimization in the DB2 environment, execute the SQL COMPILE command with the STATIC option set to NOBIND, and execute a separate BIND using the EXPLAIN option. (For more information about bind options, consult the IBM DB2 Command and Utility Reference.) In the DB2 for VM environment, you can use the EXPLAIN option of the GENUSQL EXEC. (For more information about this option, consult the explanation of the SQLPREP command in the IBM DB2 for VM Application Programming for VM Systems manual.)
In many cases, you can use the FOCUS DEFINE facility to insert literal values, rather than host variables, into the generated SQL. For example, suppose the FEX1 FOCEXEC had the value 50000 hard coded into the screening condition. FOCUS would still substitute the host variable, :H, at SQL COMPILE time. The DB2 optimizer, unaware of the 50000 value at bind time, might select a tablespace scan access path. This type of scan is inefficient if the SALARY column is indexed, because DB2 will only need to retrieve a few rows when the value 50000 is substituted for :H at SQL RUN time.
You can alter the request as follows:
SQL DB2 SET OPTIMIZATION ON
DEFINE FILE EMPLOYEE
SALVALUE/P13.2 = 50000;
TABLE FILE EMPLOYEE
PRINT EMP_ID SALARY POINTS
WHERE SALARY GE SALVALUE
END
The generated SQL now includes the literal value rather than the host variable:
SELECT T1.EMP_ID,T1.SALARY,T2.POINTS FROM "USER1"."PAYROLL" T1,
"USER1"."COURSES" T2 WHERE (T2.EMP_NUM = T1.EMP_ID) AND
(T1.SALARY >= 50000);
The DB2 optimizer can now choose a direct index access path to the data because it knows the literal screening value at bind time.
This solution is not valid for literals in LIKE predicates, as FOCUS does not support field names to the right of the LIKE operator in TABLE requests. Also, this technique generates dual range predicates for FOCUS WHERE/FROM-TO clauses, not an SQL BETWEEN. The use of dual range predicates may have optimization implications.
Note: If a screening condition in a TABLE request has an ampersand variable to the right of a LIKE operator, different literals used with the same LIKE syntax may produce different SQL. You must ensure that any SQL that could be generated dynamically at SQL RUN time has an exact match in the STUBLIB module. You can use the REPEAT option of the SQL COMPILE facility and substitute a different LIKE literal format for each iteration. For more information on the SQL generated by the LIKE operator, see The Data Adapter Optimizer. If, at SQL RUN time, the data adapter cannot locate a static SQL statement that matches the generated dynamic SQL, it issues a FOC1526 message and terminates execution.
| Information Builders |