To describe what appears in the Link Variables dialog box, we will use the following sample SELECT, statement, named SelectStaff

 

SELECT STAFF.NAME, STAFF.JOB, STAFF.SALARY FROM DB2ADMIN.STAFF STAFF

WHERE STAFF.ID = ?

 

Below shows the Link Variables dialog box for the SelectStaff statement.

Link Variables Dialog Box

Columns/Variables Table

When you run DB/Assist standalone, the Columns/Variables table displays the same types of statement columns as it does when you run DB/Assist from Workbench.

As shown above, you can link ESL variables to any of the following columns:

•Output columns, such as STAFFJOB

•Null indicator columns, such as STAFF JOB (Null Ind)

•Input argument columns, such as (where) STAFF.ID =

Output columns are linked to ESL output variables. For example, in the SelectStaff statement, you must specify output variables to hold values for the employee name, job, and salary.

Automatically Creating a Variable describes how to use the Auto Create push button in the Link Variables dialog box The Auto Create push button automatically creates ESL variables of the appropriate types for the selected columns. When creating output variables, DB/Assist uses the following default names, where Column is the name of the database table column.

•Column_SV        for string variables

•Column_IV        for integer variables

•Column_FV        for float variables

Null indicator columns are linked to ESL integer variables. A null indicator variable is an integer variable that records whether the value for a particular column is or is not null. If the value for a column is null, DB2 passes a value of -1.

If a column in your statement permits null values (that is, if it is not a required column), its name appears twice in the Columns/Variables table: once as an output column, and a second time followed by "(Null Ind)" as a null indicator column.

For example, in the Link Variables dialog box shown for the SelectStaff statement in the example Link Variable Dialog Box above, STAFF JOB is an output column and STAFF.JOB OB (Null Ind) is a null indicator column.

When you use the Auto Create push button to create a null indicator variable, as described in Automatically Creating a Variable, DB/Assist uses the default name Column_NIV, where Column is the name of the database table column.

When you run the ESL program containing the generated action routines for the SelectStaff statement, you can write code that queries the null indicator variables to determine whether the value for a particular column is null.

Input argument columns are linked to ESL program variables that hold input values. When you compile the SAM containing this statement, DB/Assist generates code that uses these variables as input parameters to the SQL statement. For example, in the SelectStaff statement, you must specify an ESL variable to hold the value of the Staff ID.

Input argument columns appear in the Columns/Variables table with the clause keyword, such as WHERE or HAVING, enclosed in parentheses, followed by the column name and the expression operator.

For example, in the SelectStaff statement, "(where) STAFF.ID =" is an input argument column.

When you use the Auto Create push button to create an input variable, DB/Assist uses the following default names, where Column is the name of the database table column.

Variable List

You can select the Variable File push button to specify an existing file containing variable definitions. DB/Assist then displays these variables in the Variable List, which you can filter by any combination of string, integer, and float types.

Methods for Linking Variables

When you use the Link Variables dialog box, you can link variables by any combination of the following methods:

•Select the Auto Create push button to automatically create variables for one or more selected columns.

•Select the New push button to create a new variable for a selected column.

•Select a variable from the Variable List to link a selected column with a predefined variable.

The UpdateStaff statement below is used to describe these methods. UpdateStaff is a searched UPDATE statement that updates a row in the Staff table, as follows:

 

UPDATE DB2ADMIN.STAFF

SET ID=?, NAME=?, DEPT=?, JOB=?, YEARS=?, SALARY=?, COMM=?

WHERE DB2ADMIN.STAFF.ID=?

 

Creating a Variable File

After you link variables for all of the statements in a particular SAM, you can write these variable definitions to a file by selecting SQL Access Module Ø Create variable file.

For information about creating a file to contain your variable definitions, see Creating a Variable File.