Host Structures | Teradata Vantage - Host Structures - Advanced SQL Engine - Teradata Database

A host structure is an array of host variables that is declared outside of SQL in the host language of your embedded SQL application.

Example: Producing a Report on the Employee Ethnicity Demographics

Consider the following embedded SQL SELECT statement written for a COBOL application. The purpose of this statement is to produce a report on the ethnic demographics of the first 100 employees hired by the corporation.

EXEC SQL
 SELECT EmpNo, LastName, Ethnicity, BirthDate, SSN, DeptNo
 INTO :EmpNo, :LastName, :Ethnicity, :BirthDate, :SSN, :DeptNo
 FROM Employee
 WHERE EmpNo < ‘100’
END-EXEC

Rather than typing the names of the six host variables, you can create a named host structure that contains :EmpNo, :LastName, :Ethnicity, :BirthDate, :SSN, and :DeptNo as individual elements within the array and then substitute that name in the query for the individual host variables.

The same COBOL example could then be rewritten as follows, where :FounderEmployeeInfo is the name of the host structure that contains the host variables :EmpNo, :LastName, :Ethnicity, :BirthDate, :SSN, and :DeptNo.

EXEC SQL
 SELECT EmpNo, LastName, Ethnicity, BirthDate, SSN, DeptNo
 INTO :FounderEmployeeInfo
 FROM Employee
 WHERE EmpNo < ‘100’
END-EXEC

Host Structures Not Supported In ANSI Session Mode

ANSI session mode does not support arrays; therefore, it does not support host structures nor does it support qualified host variables.

Host Structures Supported In Teradata Session Mode

Teradata session mode supports IBM-style host structures to a maximum of two levels.

Teradata session mode also supports qualified host variables to reference fields within host structures.

Fully qualified host variable references in embedded SQL statements are expressed in the same way as fully qualified SQL column references: with a FULLSTOP character separating the different levels of qualification.

This syntax is valid for all supported host languages.

This example uses COBOL to illustrate the point, using :SHIPMENT-RECORD.WEIGHT as a fully qualified host variable:

ADD 5 TO WEIGHT OF SHIPMENT-RECORD. 
EXEC SQL
 DELETE FROM SHIPMENT_TABLE
 WHERE WEIGHT > :SHIPMENT-RECORD.WEIGHT
END-EXEC.