Host Variables

Host Variables

When embedding SQL in an application program, you, as the programmer, rarely know every value that needs to be accessed by SQL predicates. Often you need to use variables to specify the values of predicates. For example, when a program reads a flat file for data or accepts user input from a terminal, a mechanism is needed to hold these values in an SQL statement. This is the function of host variables.

A host variable is an area of storage allocated by the host language and referenced in an SQL statement. You define and name host variables using the syntax of the host language. For COBOL, you must define host variables in the DATA DIVISION of your program in the WORKING-STORAGE section or the LINKAGE section. Additionally, when you're using INCLUDE , you must delimit the host variable specifications by using EXEC SQL and END-EXEC (as previously discussed).

When you use host variables in SQL statements, prefix them with a colon ( : ). For example, a COBOL variable defined in the DATA DIVISION as

 EXAMPLE-VARIABLE     PIC X(5) 

should be referenced as follows when used in an embedded SQL statement:

 :EXAMPLE-VARIABLE 

When the same variable is referenced by the COBOL program outside the context of SQL, however, do not prefix the variable with a colon. If you do so, a compilation error results.

CAUTION

Prior to DB2 Version 6, DB2 allowed users to "forget" prefixing host variables with a colon. If a colon was not specified, an informational warning was generated, but the SQL statement was still processed . For DB2 V6 and later releases, this is no longer the case. If the colon is missing, an error message will be generated and the SQL will not execute.

Host variables are the means of moving data from the program to DB2 and from DB2 to the program. Data can be read from a file, placed into host variables, and used to modify a DB2 table (through embedded SQL). For data retrieval, host variables are used to house the selected DB2 data. You also can use host variables to change predicate values in WHERE clauses. You can use host variables in the following ways:

• As output data areas in the INTO clause of the SELECT and FETCH statements

• As input data areas for the SET clause of the UPDATE statement

• As input data areas for the VALUES clause of the INSERT statement

• As search fields in the WHERE clause for SELECT , INSERT , UPDATE , and DELETE statements

• As literals in the SELECT list of a SELECT statement

Several examples of host variables used in SQL statements follow. In the first example, host variables are used in the SQL SELECT statement as literals in the SELECT list and as output data areas in the INTO clause:

 EXEC SQL     SELECT  EMPNO, :INCREASE-PCT,             SALARY * :INCREASE-PCT     INTO    :HOSTVAR-EMPNO,             :HOSTVAR-INCRPCT,             :HOSTVAR-SALARY     FROM    EMP     WHERE   EMPNO = '000110' END-EXEC. 

In the second example, host variables are used in the SET clause of the UPDATE statement and as a search field in the WHERE clause:

 EXEC SQL     UPDATE EMP        SET SALARY = :HOSTVAR-SALARY     WHERE  EMPNO = :HOSTVAR-EMPNO END-EXEC. 

A third example shows a host variable used in the WHERE clause of an SQL DELETE statement. In this statement every row that refers to a WORKDEPT equal to the host variable value will be deleted from the table:

 EXEC SQL     DELETE FROM  EMP     WHERE  WORKDEPT = :HOSTVAR-WORKDEPT END-EXEC. 

The final example depicts host variables used in the VALUES clause of an SQL INSERT statement:

 EXEC SQL     INSERT INTO DEPT     VALUES (:HOSTVAR-DEPTNO,             :HOSTVAR-DEPTNAME,             :HOSTVAR-MGRNO,             :HOSTVAR-ADMRDEPT) END-EXEC. 

Host Structures

In addition to host variables, SQL statements can use host structures. Host structures enable SQL statements to specify a single structure for storing all retrieved columns . A host structure, then, is a COBOL group -level data area composed of host variables for all columns to be returned by a given SELECT statement.

The following is a host structure for the DSN8810.DEPT table:

 01  DCLDEPT.     10  DEPTNO               PIC X(3).     10  DEPTNAME.         49  DEPTNAME-LEN     PIC S9(4) USAGE COMP.         49  DEPTNAME-TEXT    PIC X(36).     10  MGRNO                PIC X(6).     10  ADMRDEPT             PIC X(3).     10  LOCATION             PIC X(16). 

DCLDEPT is the host structure name in this example. You could write the following statement using this host structure:

 EXEC SQL     SELECT  DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION     FROM    DEPT     INTO    :DCLDEPT     WHERE   DEPTNO = 'A00' END-EXEC. 

This statement populates the host variables for all columns defined under the DCLDEPT group-level data area.

Null Indicator Variables and Structures

Before you select or insert a column that can be set to null, it must have an indicator variable defined for it. You can use indicator variables also with the UPDATE statement to set columns to null. A third use for null indicators occurs when any column (defined as either nullable or not nullable) is retrieved using the built-in column functions AVG , MAX , MIN , and SUM . Finally, null indicators should be used in outer join statements for each column that can return a null result (even if the column is defined as not null).

If you fail to use an indicator variable, a -305 SQLCODE is returned when no rows meet the requirements of the predicates for the SQL statement containing the column function. For example, consider the following statement:

 SELECT  MAX(SALARY) FROM    DSN8810.EMP WHERE   WORKDEPT = 'ZZZ'; 

Because no ZZZ department exists, the value of the maximum salary that is returned is null ”not zero or blank. So be sure to include a null indicator variable whenever you use a built-in column function.

You should define null indicators in the WORKING-STORAGE section of your COBOL program as computational variables, with a picture clause specification of PIC S9(4) . The null indicator variables for the DSN8810.EMP table look like this:

 01  EMP-INDICATORS.     10  WORKDEPT-IND   PIC S9(4) USAGE COMP.     10  PHONENO-IND    PIC S9(4) USAGE COMP.     10  HIREDATE-IND   PIC S9(4) USAGE COMP.     10  JOB-IND        PIC S9(4) USAGE COMP.     10  EDLEVEL-IND    PIC S9(4) USAGE COMP.     10  SEX-IND        PIC S9(4) USAGE COMP.     10  BIRTHDATE-IND  PIC S9(4) USAGE COMP.     10  SALARY-IND     PIC S9(4) USAGE COMP.     10  BONUS-IND      PIC S9(4) USAGE COMP.     10  COMM-IND       PIC S9(4) USAGE COMP. 

This structure contains the null indicators for all the nullable columns of the DSN8810.EMP table.

To associate null indicator variables with a particular host variable for a column, code the indicator variable immediately after the host variable, preceded by a colon. For example, to retrieve information regarding SALARY (a nullable column) from the DSN8810.EMP table, you can code the following embedded SQL statement:

 EXEC SQL     SELECT  EMPNO, SALARY     INTO    :EMPNO,             :SALARY:SALARY-IND     FROM    EMP     WHERE   EMPNO = '000100' END-EXEC. 

The null indicator variable is separate from both the column to which it pertains and the host variable for that column. To determine the value of any nullable column, a host variable and an indicator variable are required. The host variable contains the value of the column when it is not null. The indicator variable contains one of the following values to indicate a column's null status:

• A negative number indicates that the column has been set to null.

• The value -2 indicates that the column has been set to null as a result of a data conversion error.

• A positive or zero value indicates that the column is not null.

• If a column defined as a CHARACTER data type is truncated on retrieval because the host variable is not large enough, the indicator variable contains the original length of the truncated column.

You can use null indicator variables with corresponding host variables in the following situations:

• SET clause of the UPDATE statement

• VALUES clause of the INSERT statement

• INTO clause of the SELECT or FETCH statement

You can code null indicator structures in much the same way you code the host structures discussed previously. Null indicator structures enable host structures to be used when nullable columns are selected. A null indicator structure is defined as a null indicator variable with an OCCURS clause. The variable should occur once for each column in the corresponding host structure, as shown in the following section of code:

 01  IDEPT PIC S9(4) USAGE COMP OCCURS 5 TIMES. 

This null indicator structure defines the null indicators needed for retrieving rows from the DSN8810.DEPT table using a host structure. The DCLDEPT host structure has five columns, so the IDEPT null indicator structure occurs five times. When you're using a host structure for a table in which any column is nullable, one null indicator per column in the host structure is required.

You can use the host structure and null indicator structure together as follows:

 EXEC SQL     SELECT  DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION     FROM    DEPT     INTO    :DCLDEPT:DEPT-IND     WHERE   DEPTNO = 'A00' END-EXEC. 

Based on the position in the null indicator structure, you can determine the null status of each column in the retrieved row. If the n th null indicator contains a negative value, the n th column is null. So, in this example, if DEPT-IND(3) is negative, MGRNO is null.

CAUTION

Always use a null indicator variable when referencing a nullable column. Failure to do so results in a -305 SQLCODE . If you fail to check the null status of the column being retrieved, your program may continue to execute, but the results will be questionable.

NOTE

You can avoid using null indicator variables by using the VALUE or COALESCE function. Both of these functions can be used to supply a value whenever DB2 would return a null. For example, VALUE(MANAGER_NAME,'*** No Manager Name ***') will return the actual value of MANAGER_NAME when the column is not null and the literal '*** No Manager Name ***' when the MANAGER_NAME column is null.

COALESCE works the same as VALUES and uses the same syntax ” COALESCE(MANAGER_NAME,'*** No Manager Name ***') .

Host Variable Guidelines

Practice the following tips and techniques to ensure proper host variable usage.

Use Syntactically Valid Variable Names

Host variables can use any naming scheme that is valid for the definition of variables in the host language being used. For host variables defined using COBOL, underscores are not permitted. As a general rule, use hyphens instead of underscores.

Avoid Certain COBOL Clauses

COBOL host variable definitions cannot specify the JUSTIFIED or BLANK WHEN ZERO clauses.

You can specify the OCCURS clause only when you're defining a null indicator structure. Otherwise, you cannot use OCCURS for host variables.

Using Host Structures

In general, favor individual host variables over host structures. Individual host variables are easier to understand, easier to support, and less likely to cause errors as a result of changes to tables. Additionally, using individual host variables promotes proper SQL usage. When using host structures, too often developers try to fit every SQL SELECT to the host structure. Instead of limiting each SQL SELECT statement to retrieve only the columns required, developers sometimes will force every SELECT statement in a program to fit a single host structure. To optimize performance, this must be avoided.

However, when it is necessary to squeeze every last bit of performance out of an application, consider using host structures. When a host structure is used, some minimal number of instructions are saved because DB2 does not have to move each column separately to an individual host variable. Instead, one move is required to move the columns into the host structure.

CAUTION

When host structures are used, be sure not to fall into the trap of making every SELECT statement use a single host structure, or performance will suffer.

Avoid Null Indicator Structures

Favor individual null indicator variables over null indicator structures. Individual null indicator variables can be named appropriately for each column to which they apply. Null indicator structures have a single common name and a subscript. Tying a subscripted variable name to a specific column can be tedious and error-prone .

For example, consider the host structure and its corresponding null indicator structure shown previously. The fact that IDEPT(2) is the null indicator variable for the DEPTNAME host variable is not obvious. If you had used separate null indicators for each nullable column, the null indicator for DEPTNAME could be called DEPTNAME-IND . With this naming convention, you can easily see that DEPTNAME-IND is the null indicator variable for DEPTNAME .

Be forewarned that null indicator structures can be generated by DCLGEN (as of DB2 V4), whereas individual indicator variables must be explicitly coded by hand. Even so, individual null indicator variables are easier to use and therefore recommended over null indicator structures.

Define Host Variables Precisely

Define all your host variables correctly. Consult Appendix C, "Valid DB2 Data Types," for a complete list of valid DB2 data types and their corresponding COBOL definitions. Failure to define host variables correctly can result in precompiler errors or poor performance due to access path selection based on non-equivalent data types, data conversions, and data truncation .

Use DCLGEN for Host Variable Generation

Use DCLGEN to generate host variables automatically for each column of each table to be accessed. DCLGEN ensures that the host variables are defined correctly.

Avoid DCLGEN for Null Indicator Generation

As I mentioned earlier, DCLGEN can optionally generate null indicator host structures. However, host structures are more difficult to use than individual null indicator variables and generally should be avoided.

Embedded SELECT Statements

The two types of embedded SQL SELECT statements are singleton SELECT s and cursor SELECT s. So far, all examples in the book have been singleton SELECT s.

Remember, SQL statements operate on a set of data and return a set of data. Host language programs, however, operate on data a row at a time. A singleton SELECT is simply an SQL SELECT statement that returns only one row. As such, it can be coded and embedded in a host language program with little effort: The singleton SELECT returns one row and the application program processes one row.

You code a singleton SELECT as follows:

 EXEC SQL     SELECT  DEPTNAME, MGRNO     INTO    :HOSTVAR-DEPTNAME,             :HOSTVAR-MGRNO     FROM    DEPT     WHERE   DEPTNO = 'A11' END-EXEC. 

The singleton SELECT statement differs from a normal SQL SELECT statement in that it must contain the INTO clause. In the INTO clause, you code the host variables that accept the data returned from the DB2 table by the SELECT statement.

Singleton SELECT s are usually quite efficient. Be sure, however, that the singleton SELECT returns only one row. If more than one row is retrieved, the first one is placed in the host variables defined by the INTO clause, and the SQLCODE is set to -811 .

Of course, as of DB2 V7, you can append the FETCH FIRST 1 ROW ONLY clause to any SQL statement and DB2 will only return one row ”regardless of how many may actually qualify. This is one way of avoiding “811 problems.

If your application program must process a SELECT statement that returns multiple rows, you must use a cursor, which is an object designed to handle multiple row results tables. As of DB2 V8, though, rowset positioning cursors can be coded to FETCH multiple rows at a time.