Host Variables and Host Structures A host variable is a data item declared in the host language for use within a 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 a 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. Using Host Variables Any valid host variable name can be used in a 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, column, or view. To optimize performance, make sure 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 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. |
The DCLGEN must be used and the DCLGEN name of the table or view must be supplied before the program is precompiled. In order to use the declarations generated by DCLGEN in the program, the SQL INCLUDE statement is used. The example below shows this INCLUDE statement for the CAND DCLGEN: EXEC SQL INCLUDE CAND END-EXEC DB2 must be active before DCLGEN can be used. The DCLGEN utility can be started in several different 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: Run a CLIST in TSO foreground or background 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 the output from a DCLGEN. *************************************************************** 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. In the following example, we retrieve CID, LNAME, and FNAME from the CANDIDATE table and populate the host variables. You can define a data area in your program to hold each column, then name the data areas with an INTO clause, as in the following example (notice that a colon precedes each host variable): 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, it will result in a-811 error code, 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, then 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. | 
