COBOL DB2 Program 10 Best Points You Need

In Mainframe, it is possible to run a SQL query without writing a COBOL program. You can do it by using, SPUFI, QMF and IBM Studio.

SQL query must begin with EXEC SQL and end with END-EXEC. Second, your SQL query uses COBOL host variables.

Host variables in COBOL:

Host variables appear with a colon (:) prefix in SQL statements. Host variables must be defined properly in COBOL; they are the exact COBOL equivalent of the column attribute.
DELETE statements do not require host variables since they delete all the rows (the set) identified in the WHERE clause. SELECT statements, on the other hand, always require host variables.
INSERT statements usually require host variables, but you can also specify data in a VALUES clause.
UPDATE statements also require host variables, but only for columns whose data you are going to change. For example, the following SQL query uses an UPDATE statement to change the sex of employee


Notice the syntax for structures in SQL. SEX OF DCLEMPLOYEE is COBOL syntax. Inside a SQL statement, SEX OF DCLEMPLOYEE becomes DCLEMPLOYEE-SEX. DCLEMPLOYEE is the structure name (probably an 01 level).

DCL followed by the table name is a common naming convention for this structure or I/O area.

How to generate Host Variables in COBOL program:

Database software often includes utilities that generate these structures for different languages (for example, DCLGEN is the DB2 utility).

Static and Dynamic SQL:

EXEC SQL DECLARE statement, because it is completely commented out by the SQL preprocessor, which executes before the COBOL compiler.

In DB2, the SQL preprocessor stores the EXEC SQL DECLARE information in a file called a database request module (DBRM).

This is called static SQL. In mainframe CICS/DB2 applications, almost all SQL is static SQL.

Later, before executing the program, you use a BIND utility to turn the DBRM(s) into a plan. The plan is a deliverable, like the CICS BMS map and COBOL load module, and programs require it in order to execute. A partial plan is called a packet. You can combine packets to build a plan.

Creating a DBRM with a SQL preprocessor and building a plan is required only by DB2. Since access paths to the data are chosen before the program executes, program performance is greatly enhanced. Other databases process the SQL entirely at runtime, although they are likely to be less efficient than DB2.

In dynamic SQL, the SQL statement is unknown until execution time. Dynamic SQL is less efficient but more flexible than static SQL. If your program needs to accept a SQL statement typed by an end user, execute it, and display the results, you must use dynamic SQL.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.