DB2 graphic QMF Version 8

Editing variables

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.

Values for variables

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.

Edit Variables panel

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.

Table 11. Edit Variables
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

Literal Value
Enter the literal value with which to replace this variable. The value can be up to 55 characters in length. It must not start with a colon (:). Leaving this field blank implies a value of a single space.

To replace variables with host variables, enter the following fields:

Host Variable and Data Type
Enter the host variable name, beginning with a colon (:).

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.

Note:
If you are specifying a data type for a host variable that will be the target in an SQL LIKE clause, you should specify VARCHAR(n) as the type rather than CHAR(n).

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.

Null
Enter Y to specify that an indicator variable should be included with the specified host variable. An indicator variable is an additional host variable with data type SMALLINT that indicates whether or not the value of the primary host variable is NULL. If the indicator value is less than zero, the primary host variable value is NULL. Enter N to specify that an indicator variable is not required. For host variables used in the WHERE clause of an SQL statement, indicator variables are typically not required.
Note:
For stored procedure programs, the nullability of input parameters is determined by the parameter style chosen when the program is generated. In this case, the nullability values you enter on the Edit Variables panel are ignored.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004