DB2 - Host Variables




DB2 Host Variables

You can use host variables, host variable arrays, and host structures in your application program to exchange data between the application and the DBMS. A host variable is a data item that you declare in a program for use within an SQL statement. 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. A special register is a storage area that Db2 defines for a process to hold information that SQL statements can reference.

The CURRENT SQLID special register contains the SQL authorization ID of a process, which is set in an SQL statement. Db2 replaces the register name with the value of the authorization ID when the SQL statement runs.

Use the host variable to indicate a null value, How you code a host variable varies according to the programming language that you use. Some languages require a separate declaration section for SQL variables. In this case, you can code the BEGIN and END DECLARE SECTION statements in an application program wherever variable declarations can appear according to the rules of the host language. A host variable declaration section starts with the BEGIN DECLARE SECTION statement and ends with the END DECLARE SECTION statement. The host variable must be preceded with a,

The INTO clause of the SELECT statement names one or more host variables to contain the returned column values. For host variables and host variable arrays, the named variables correspond one-to-one with the list of column names in the SELECT list. The example that follows uses a host variable to retrieve a single row of data.

Host variables are data items defined within a COBOL program. They are used to pass values to and receive values from a database. Host variables can be defined in the File Section, Working-Storage Section, Local-Storage Section or Linkage Section of your COBOL program and have any level number between 1 and 48. Level 49 is reserved for VARCHAR data items. When a host variable name is used within an embedded SQL statement, the data item name must begin with a colon (:) to enable the Compiler to distinguish between host variables and tables or columns with the same name.

Host variables are used in one of two ways, Input host variables, These are used to specify data that will be transferred from the COBOL program to the database. Output host variables, These are used to hold data that is returned to the COBOL program from the database.

For example, in the following statement, :book-id is an input host variable that contains the id of the book to search for while :book-title is an output host variable that returns the result of the search −

EXEC SQL
   SELECT title INTO :book-title FROM titles
      WHERE title_id=:book-id
END-EXEC

What is DB2 Host Variables ?

DB2 is an external system and can’t able to update from the program. COBOL program can’t able to retrieve the data directly by the SQL and can’t able to use it directly. There is a requirement to use intermediate fields to capture the data and then only it is ready to use in the program. There is one more limitation for the working storage fields are, the declaration should match with the corresponding table column COBOL declaration. These variables are called as HOST VARIABLES. Equivalent data types in COBOL & DB2

For Example
Cobol Db2
S9(8)COMP INTEGER
S9(4)COMP SMALLINT
X(n) NUM(n)
X(n) CHAR(n)
X(n)+2 VARCHAR(n)
X(8) TIME
X(10) DATE
X(26) TIMESTAMP

Database column equalent variable can be created by DCLGEN.

Suppose that you want to retrieve the EMPNO, LASTNAME, and DEPT column values from a single row in the EMP table. You can define a host variable in your program to hold each column. The host variable consists of the local variable name, preceded by a colon. You then can name the data areas with an INTO clause, as shown −

EXEC SQL
  SELECT EMPNO, LASTNAME, DEPT
    INTO :CBLEMPNO, :CBLNAME, :CBLDEPT
    FROM EMP
    WHERE EMPNO = :EMPID
END-EXEC.

You must declare the host variables CBLEMPNO, CBLNAME, and CBLDEPT in the data declaration portion of the program. The data types of the host variables must be compatible with the SQL data types of the columns EMPNO, LASTNAME, and DEPT of the EMP table.

Suppose that you don't know how many rows Db2 will return, or you expect more than one row to return. In either case, you must use an alternative to the SELECT, INTO statement. Using a Db2 cursor, an application can process a set of rows and retrieve rows from the result table.