A host variable is a data item declared in the host language for use within an SQL statement. Using host variables, you can
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 VariablesAny 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
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 StructuresA 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:
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 DataThe 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 DataIf 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 DataYou 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 DataYou 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. |