Database Interface in COBOL

Programs in COBOL communicate with the DB2 Database. Data Base2 is DB2, and it was created by IBM. The database is a relational one. The relational information is kept in the TABLE format, which consists of multiples of rows and attributes (Columns).

DB2 is generally used for storing sizable amounts of Mainframe application data. It is comparable to SQL but has several enhanced features.

The following terms are part of the COBOL vocabulary for the Database Interface:

• Embedded SQL
• DB2 Application Programming
• Host Variables
• SQLCA
• SQL Queries
• Cursors

Embedded SQL

Standard SQL operations are carried out by COBOL using integrated SQL statements. Before the application program is compiled, the SQL processor preprocesses these statements.
The host's primary language is COBOL. Applications are written in COBOL-DB2 use both DB2 and COBOL.
With a few small exceptions, embedded SQL statements function much like standard SQL (Structured Query Language) statements. A host variable is a predetermined group of variables to which the output of a query is often directed. The SELECT statement now includes a second INTO clause.

DB2 Application Programming

The guidelines to follow when writing COBOL-DB2 software are as follows:

• Between EXEC SQL and ENDEXEC, each SQL statement needs to be encapsulated.
• AREA B is where SQL statements must be coded.
• The INCLUDE statement in the Working-Storage section must define each and every table in the program.
• Except for INCLUDE and DECLARE TABLE, all SQL statements must appear in the Procedure Division.

Host Variables

The host variables are the data elements that are specified in the COBOL program. Host variables either take data from a table or add the data to a table. These variables are used to store and retrieve values from databases.

In the File, Local Storage, Linkage, or Working-Storage sections of your COBOL program, you can assign host variables with any level number between 1 and 48. VARCHAR data elements have level 49 designated for them.

Host variables cannot be group items, however, they can be grouped in the host structure. They cannot be altered in meaning or name.

If you utilize a host variable in an embedded SQL query, you must use a colon to provide the prefix of the data item name (:). To help the compiler distinguish between host variables and columns/tables with the same name, use the colon (:).

There are two ways to use host variables:

• Input Host variables: Defining the data that will be sent from the COBOL application to the database using input host variables.
• Output Host Variables: Used to store information that the database returns to the COBOL program.

Syntax:

  EXEC SQL  

  INCLUDE table-name  

  END-EXEC.   

  EXEC SQL BEGIN DECLARE SECTION  

  END-EXEC.  

  Data    

  EXEC SQL END DECLARE SECTION  

  END-EXEC.  

SQLCA

SQLCA is a SQL communication area where DB2 sends the program the results of the SQL execution. Every time a SQL query is executed, a set of variables called SQLCA is modified. One SQLCA may be provided by a program that contains SQL statements that can be executed, but not more.

It merely informs the program of the success or failure of the execution. Under SQLCA, a number of predefined variables exist, including SQLCODE, which holds the error code. The SQLCODE value '000' denotes a successful execution.

The syntax to declare SQLCA in the Working Storage section is as follows:

Syntax:

EXEC SQL  

INCLUDE SQLCA  

END-EXEC.  

Cursors

Cursors are a mechanism that DB2 supports. A set of table rows are processed one by one using the cursor. At once, it manages multiple row selections. Data structures called cursors to store all of a query's results.

The working storage portion or the procedure division is where we can define the cursor. The cursor-related actions are as follows:

• Delete: The delete cursor is used to delete the rows from the table to result-ser.
• Open: The Open statement should be used before employing a cursor. The SELECT is ready for execution, thanks to the Open statement.
• Close: To release every bit of memory utilized by the cursor, use the close statement. A program should be terminated with the cursor closed.
• Fetch: The cursor is located using the get command, which also inserts the value into the INTO clause. The loop codes a fetch statement each time a new row is received.

The Working storage section or the procedure division are both acceptable places to declare a cursor. The DECLARE statement, which is the first sentence, cannot be executed.

Syntax:

EXEC SQL  

DECLARE cursorname\cursor-variable name

CURSOR FOR  SELLECT statement

END-EXEC.