Host Variables and Structures

A host variable is a data item declared in the host language for use within an SQL statement. Using host variables, you can

• Retrieve data into the host variable for your application program's use

• Place data into the host variable to insert into a table or to change the contents of a row

• Use the data in the host variable when evaluating a WHERE or HAVING clause

• Assign the value in the host variable to a special register, such as CURRENT SQLID and CURRENT DEGREE

• Insert null values in columns, using a host indicator variable that contains a negative value

• Use the data in the host variable in statements that process dynamic SQL, such as EXECUTE, PREPARE, and OPEN

A host structure is a group of host variables that an SQL statement can refer to using a single name. You can use host structures in all languages except REXX. Use the host language statements to define the host structures.

In some languages, such as C, the program variables that are used as host variables must be defined in a section of the program that is delimited by BEGIN DECLARE SECTION and END DECLARE SECTION statements, as follows:

 EXEC SQL BEGIN DECLARE SECTION; DECLARE DB2USER1.TEST_TABLE   CHAR NUMBER[7];   CHAR NAME[51];   SHORT LENGTH; EXEC SQL END DECLARE SECTION; 

Using Host Variables

Any valid host variable name can be used in an SQL statement. Host variables must be declared to the host program before they can be used. Host variables are used by DB2 to

• Retrieve data and put it into the host variables for use by the application program

• Insert data into a table or update it from the data in the host variable

• Evaluate a WHERE or HAVING clause using the data in the host variable

However, host variables cannot be used to represent a table, a column, or a view.

To optimize performance, make sure that the host language declaration maps as closely as possible to the data type of the associated data in the database. Table, view, or column names can be specified at runtime when using dynamic SQL. We discuss dynamic SQL later in this chapter.

Host variables follow the naming conventions of the host language. A colon (:) must precede host variables used in SQL to tell DB2 that the variable is not a column name. A colon must not precede host variables outside of SQL statements.

In the following example, :CANPHONE and :CIDNO are host variables; the values will be supplied at runtime.

 EXEC SQL    UPDATE CANDIDATE       SET PHONE = :CANPHONE       WHERE CID = :CIDNO END-EXEC. 

The host variables can be used to specify a program data area to contain the column values of a retrieved row or rows.

Using Host Structures

A host structure can be used in place of one or more host variables. In the following example, assume that a COBOL program includes the following SQL statement:

 EXEC SQL    SELECT CID, LNAME, FNAME    INTO :CIDHV,:LNAMEHV,:FNAMEHV    FROM DB2USER1.CANDIDATE    WHERE CID =:CIDHV END-EXEC. 

If you want to avoid listing host variables, you can substitute the name of a structure, say, :CANDHS, that contains :CIDHV, :LNAMEHV, and :FNAMEHV. The example then reads

 EXEC SQL    SELECT CID, LNAME, FNAME    INTO :CANDHS    FROM DB2USER1.CANDIDATE    WHERE CID =:CIDHV END-EXEC. 

Host structures can be declared manually, or DCLGEN can be used to generate a COBOL record description, PL/I structure declaration, or C structure declaration that corresponds to the columns of a table. DCLGEN, the declarations generator supplied with DB2, produces a DECLARE statement for use in a C, COBOL, or PL/I program so that the declares for the tables do not need to be manually coded. DCLGEN generates a table declaration and puts it into a member of a partitioned data set that you can include in your program. When you use DCLGEN to generate a table's declaration, DB2 gets the relevant information from the DB2 catalog, which contains information about the table's definition and the definition of each column within the table. DCLGEN uses this information to produce a complete SQL DECLARE statement for the table or view and a matching PL/I or C structure declaration or COBOL record description.

NOTE

DCLGEN can be used only on existing tables.

Before the program is precompiled, DCLGEN must be used, and the DCLGEN name of the table or view must be supplied. In order to use the declarations generated by DCLGEN in the program, the SQL INCLUDE statement is used. The following example shows this INCLUDE statement for the CAND source member generated by DCLGEN:

 EXEC SQL     INCLUDE CAND END-EXEC 

DB2 must be active before DCLGEN can be used. DCLGEN can be started in several ways:

• From ISPF through DB2I. Select the DCLGEN option on the DB2I Primary Option Menu panel. Fill in the DCLGEN panel with the information it needs to build the declarations. Then press Enter.

• Directly from TSO. Sign on to TSO, issue the TSO command DSN, and then issue the subcommand DCLGEN.

• From a CLIST. In TSO foreground or background, run a CLIST that issues DSN, and then run DCLGEN.

• With JCL. Supply the required information, using JCL, and run DCLGEN in batch.

Following is an example of DCLGEN output:

 ***************************************************************      DCLGEN TABLE(DB2USER1.TEST)          LIBRARY(DB2U01.DCLGEN.LIB(TEST))          LANGUAGE(COBOL)          APOST ***************************************************************    EXEC SQL DECLARE DB2USER1.TEST TABLE      (NUMBER         CHAR(6)         NOT NULL,       NAME           VARCHAR(50)     NOT NULL,       TYPE           CHAR(1)         NOT NULL,       CUT_SCORE      DECIMAL(6,2),       LENGTH         SMALLINT        NOT NULL,       TOTALTAKEN     SMALLINT        NOT NULL)        END-EXEC. *************************************************************** * COBOL DECLARATION FOR TABLE DB2USER1.TEST ***************************************************************    01  DCLTEST.        10 NUMBER                PIC X(63).        10 NAME.            49 NAME-LEN          PIC S9(4) COMP-4.            49 NAME-TEXT         PIC X(50).        10 TYPE                  PIC X(1).        10 CUTSCRE               PIC S(4)V9(2) COMP-3.        10 LENGTH                PIC 9(5) COMP.        10 TOTALTKN              PIC 9(5) COMP. *************************************************************** *    THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 6 *************************************************************** 

Retrieving a Single Row of Data

The INTO clause of the SELECT statement names one or more host variables to contain the column values returned. The named variables correspond one to one with the list of column names in the SELECT list. The following example retrieves CID, LNAME, and FNAME from the CANDIDATE table and populates the host variables. Note that a colon precedes each host variable. You can define a data area in your program to hold each column and then name the data areas with an INTO clause, as in the following example:

 EXEC SQL    SELECT CID, LNAME, FNAME    INTO :CIDHV,:LNAMEHV,:FNAMEHV    FROM DB2USER1.CANDIDATE    WHERE CID =:CIDHV END-EXEC. 

In the DATA DIVISION of the program, the host variables CIDHV, LNAMEHV, and FNAMEHV must be declared and need to be compatible with the data types in the columns CID, LNAME, and FNAME of the DB2USER1.CANDIDATE table.

NOTE

If the SELECT statement returns more than one row, a -811 error code will result, and any data returned is undefined and unpredictable.

Retrieving Multiple Rows of Data

If the number of rows returned by DB2 is unknown or if you expect more than one row to return, you must use an alternative to the SELECT...INTO statement. A cursor enables an application to process a set of rows and retrieve one row at a time from the result table. We will take a look at cursors later in this chapter.

Inserting and Updating Data

You can set or change a value in a DB2 table to the value of a host variable. To do this, you use the host variable name in the SET clause of UPDATE or the VALUES clause of INSERT. This example changes a candidate's home phone number:

 EXEC SQL    UPDATE DB2USER1.CANDIDATE       SET PHONENO =:HPHONEHV       WHERE CID =:CIDHV END-EXEC. 

Searching Data

You can use a host variable to specify a value in the predicate of a search condition or to replace a constant in an expression. For example, if you have defined a field called CIDHV that contains a candidate number, you can retrieve the name of the candidate whose number is '012345678' with

 MOVE '012345678' TO CIDHV. EXEC SQL    SELECT LNAME    INTO :LNAMEHV    FROM DB2USER1.CANDIDATE    WHERE CID =:CIDHV END-EXEC.