QMF queries can contain substitution variables. Before a query is run, QMF replaces each variable in the query with its value. If a variable does not have a value (that is, it is unbound) then the query cannot be run.
Variable bindings can be established through the SET GLOBAL and RUN QUERY commands. When using QMF in an online mode, a prompt panel is displayed whenever a query with unbound variables is run. The panel lists the unbound variables with a field in which you can enter its value. You cannot be prompted when running QMF in batch. Therefore, the values must be set through commands in the procedure that invokes the query.
A variable can be replaced with a literal text string. Before the query with the variable is embedded in the generated program, the variable is replaced by the literal. For example, this query contains a variable named &VAR1:
SELECT &VAR1 FROM HR.DEPT_TABLE
If &VAR1 is given the value NAME, then the query that will be embedded in the generated program is:
SELECT NAME FROM HR.DEPT_TABLE
In addition, QMF substitution variables can be replaced with host variables. Host variables can be used to receive values from DB2 or send values to DB2. For example, this query contains a host variable named: MAXDAYS in the WHERE clause:
SELECT NAME, DEPT FROM HR.DEPT_TABLE WHERE VACATION_DAYS > :MAXDAYS
If :MAXDAYS holds the value of 20 when this query is run, then the predicate will be evaluated as:
WHERE VACATION_DAYS > 20
A QMF substitution variable cannot always be replaced by a host variable. You can convert some QMF queries with variables into syntactically correct embedded SQL with host variables by substituting the variables with host variable names. However, it is possible to create a QMF query with variables that cannot be converted into embedded SQL by this substitution. For example, a QMF query can contain a variable in the FROM clause:
SELECT NAME, DEPT FROM &TAB
It is syntactically incorrect to code embedded SQL with a host variable in a FROM clause:
SELECT NAME, DEPT FROM :TAB
In a case like this, the &TAB variable must be replaced by a literal string (for example HR.DEPT_TABLE) as described above.
When specifying a replacement value for a QMF substitution variable, a host variable is specified by entering a value that begins with a colon (:). Although host variables are not required to start with a colon in embedded SQL, that requirement is made for host variables generated here. A value that does not begin with a colon is processed as a literal string.
The following example shows a query that contains variables, binding values for those variables, and the resulting embedded SQL
Query with substitution variables:
SELECT NAME, &ASOFDATE, DEPT FROM &CREATOR.DEPT_TABLE WHERE VACATION_DAYS > &MAXDAYS AND JOB = &JOB AND STATUS = &STATUS
Variable binding values:
Variable name | Value |
&ASOFDATE | :TODAYS-DATE |
&CREATOR | HR |
&MAXDAYS | 20 |
&JOB | 'MGR' |
&STATUS | :STATUS-IND |
SQL embedded in a COBOL program:
EXEC SQL DECLARE C1 CURSOR FOR SELECT NAME, :TODAYS-DATE, DEPT FROM HR.DEPT_TABLE WHERE VACATION_DAYS > 20 AND JOB = 'MGR' AND STATUS = :STATUS-IND END-EXEC.
When you specify a host variable to replace a substitution variable, you can request that Program Generator automatically generate a host variable that is suitable for comparison with a specific database column. To do so, after the colon, enter an equal sign (=) followed by the name of the column. The name and data type of the host variable will be generated automatically to match the column. For example:
Variable name | Value |
&INPUTID | :=Q.STAFF.ID |
&INPUTNAME | :=Q.STAFF.NAME |
You can also explicitly define the host variable name and data type. After the host variable's name, enter a space followed by its data type. For example:
Variable name | Value |
&ASOFDATE | :TODAYS-DATE DATE |
&STATUS | :STATUS-IND SMALLINT |
Valid data types are:
When the program is generated, a definition of the appropriate type is included for each host variable. For example:
01 HOST-VARIABLES. 03 TODAYS-DATE PIC X(10). 03 STATUS-IND PIC S9(4) COMP.
Code to read a value for each host variable will also be included in the generated program. For stored procedure programs, values are obtained from input parameters to the stored procedure, accessed via the program's LINKAGE SECTION. For data layer programs, values are also passed in from the calling application and accessed via the LINKAGE SECTION. For other types of programs that run in batch, values are obtained by reading the HOSTVARS or PROCVARS DD. Online programs display prompt panels to obtain values from the user.
When you set the Edit Variables? action flag to Y, the Edit Variables panel is displayed. This tabular panel contains three columns:
Program Generator determines the default value for each variable by parsing the input query to determine if the variable is compared with a specific database column. If it is, the default value for that variable is an automatically-generated host variable for that column. Otherwise, the default value is a host variable with the same name as the original substitution variable. However, if the input to the generation process was a procedure, then the default setting for a variable is taken from a binding on the RUN QUERY command for that report. If there is no binding, the default setting is taken from any preceding SET GLOBAL command. If you do not provide appropriate values for each variable, you may encounter an SQL error when you try to generate a program from the query.
DB2P / DB2PLOCATION -- Edit Variables ------------------------ Row 1 to 1 of 1 Command ===> Scroll ===> PAGE Overtype variable values to change. Press ENTER to save variables values. Type CAN or CANCEL or press PF3 to leave values unchanged. Variable Name Literal Value or Host Variable and Data Type Null ---------------- ----------------------------------------------------- ---- ****************************** Bottom of data ****************************** |
If you press Enter, all input fields will be validated. If any errors are detected during input validation, the panel will be redisplayed with an error message. Otherwise, these variable settings will be saved (and later used if you generate a program for this report).
If you press PF3, all input fields will be ignored and you will proceed to the next panel. The previous variable settings will be restored.
If you type ABORT and press Enter, all input fields will be ignored and you will return to the Report List panel. The previous variable settings will be restored.
To replace variables with literals, enter the following field
To replace variables with host variables, enter the following fields:
To automatically define a host variable that is suitable for comparison with a specific database column, enter an equal sign (=) after the colon, followed by the table creator, table name, and column name.
For example, to create a host variable suitable for use with the ID column of the Q.STAFF table, enter :=Q.STAFF.ID. The name and data type of the host variable will be generated automatically. To explicitly specify the host variable name and data type, enter the name after the colon, followed by at least one space and then the data type. Valid data types are: SMALLINT, INTEGER, DECIMAL(p,s), DECIMAL(p), DECIMAL, FLOAT, CHAR(n), VARCHAR(n), DATE, TIME, and TIMESTAMP.
This is not a requirement of Program Generator; however, DB2's processing of a LIKE clause with a CHAR host variable is non-intuitive. Using the VARCHAR data type usually produces the desired results.