COBOL - Database Interface

In COBOL, programs interact with DB2 Database. DB2 is Data Base2, which is developed by IBM. It is a Relational Database. The relational data stored in the format of TABLE, which contains Tuples (Rows) and Attributes (Columns).

DB2 is similar to the SQL but has some advanced features and is primarily used for storing large amounts of Mainframe application data.

In the context of Database Interface, COBOL includes the following terms:

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

Embedded SQL

COBOL uses embedded SQL statements to execute standard SQL operations. These statements are pre-processed by the SQL processor before the application program is compiled.

COBOL is the language of the host. COBOL-DB2 applications include both DB2 and COBOL.

Embedded SQL statements work like normal SQL (Structured Query Language) statements with some minor changes. For example, the output of a query is directed to a predefined set of variables, which are referred to as Host Variables. An extra INTO clause is placed in the SELECT statement.

DB2 Application Programming

Following are the rules to be followed while coding a COBOL-DB2 program:

• All the SQL statements must be enclosed between EXEC SQL and ENDEXEC.
• SQL statements must be coded in AREA B.
• All the tables of the program have to be defined in the Working-Storage section by the INCLUDE statement.
• All SQL statements other than INCLUDE and DECLARE TABLE must show in the Procedure Division.

Host Variables

Data items specified in the COBOL program are the host variables. Host variables accept a table's data or insert the data into a table. These variables are used to transfer values from a database and to retrieve them.

You can assign host variables in your COBOL program's File section, Local Storage section, Linkage section, or Working-Storage section and have any level number from 1 to 48. Level 49 is reserved for VARCHAR data items.

Host variables cannot be group items, but in the host structure, they can be grouped. They can't be redefined or renamed.

If you use a host variable in an embedded SQL statement, then you must specify the data item name prefix with a colon (:). Colon (:) is used to enable the compiler to understand the difference between host variables and columns/tables that have the same name.

Host variables can be used in two ways:

• Input Host variables: Used to define data that will be transferred from the COBOL program to the database.
• Output Host Variables: Used to hold data that is returned to the COBOL program from the database.

Syntax:

SQLCA

SQLCA is an SQL communication area where DB2 transfers the SQL execution feedback to the program. SQLCA is a set of variables that will be updated at the end of the execution of every SQL statement. A program that has executable SQL statements may provide one, but no more than one SQLCA.

It simply tells the program whether the execution was successful or not. There are several predefined variables under SQLCA like SQLCODE, which contains the error code. The value '000' in SQLCODE specifies a successful execution.

Syntax:

Following is the syntax to declare SQLCA in the Working Storage section:

Cursors

DB2 supports a mechanism called a cursor. The cursor is used to process a set of rows one by one from the table. It handles multiple row selections at a time. Cursors are data structures that hold all the results of a query.

We can define the cursor in the working storage section or the procedure division. Following are the cursor-related operations:

• Delete
• Open
• Close
• Fetch

Declare Cursor

Cursor declaration can be made in the Working storage section or the procedure division. The first statement is the DECLARE statement, which is a no executable statement.

Syntax:

OPEN

Before using a cursor, the Open statement should be performed. The Open statement prepares the SELECT for execution.

Syntax:

CLOSE

A close statement is used to release all the memory occupied by the cursor. The cursor should be closed before ending a program.

Syntax:

FETCH

Fetch statement is used to identify the cursor and puts the value in the INTO clause. A Fetch statement is coded in the loop as we get one row at a time.

Syntax: