Learning Objectives
After completing this session, you will be able to:
Write a simple COBOL DB2 application program
Embedded SQL
In order to access or modify the data in a DB2 database, as a first step, you need to include SQL statements within the COBOL program. These SQL statements which are being embedded in the COBOL program are referred as “Embedded SQL”. To make the system understand the concept that the SQL is a different language from the host language (COBOL) of the application program, you need to enclose all embedded SQL statements in an EXEC SQL block.
Delimit the embedded SQL as follows.
EXEC SQL
put text of SQL statement here END-EXEC.
You must code the EXEC SQL and END-EXEC delimiter clauses in your application program in column 12 through 72, whether they are in the Data Division or in the Procedure Division.
You should not code the embedded SQL in a COPY member.
DCLGEN
While executing the SQL statements in the embedded SQL, you need to move the data from the program to DB2 or/and from DB2 to the program. In order to receive the data that is returned for a row or hold the data to update or add a row to a table, we need to define a structure called “Host Structure”. Although we can code this structure by ourselves, it is easier to let DB2 develop it from the data definitions of the database.
Deceleration Generator (DCLGEN) is the utility that comes with DB2 which generates the host structure for a table. The output of DCLGEN is stored as a member of a PDS (Partitioned Data Set).
The output of DCLGEN has two parts:
o First part is “Table Declaration”: This is the “SQL DECLARE TABLE” statement which names the table and defines each of its columns.
o Second part is “Host Structure”: This contains the COBOL definition of the host variables which can be used for a table in the application program.
When we use DCLGEN for creating the COBOL definitions for the host structure, we can be sure that the COBOL definitions correspond correctly to the DB2 data types.
Include the DCLGEN output in the WORKING-STORAGE section in order to include table declaration and host structure. You need to use include command for including the DCLGEN output. This is similar to COPY command. This must be delimited by EXEC SQL and END-EXEC. You need to specify the DCLGEN member name in which you have created the DCLGEN output.
EXEC SQL
INCLUDE <declgen-member-name>
END-EXEC.
Options while creating DCLGEN
Following is the list of options available while creating DCLGEN through DB2I:
o ACTION:
ADD: For new DCLGEN member
REPLACE: For modifying the existing member
o COLUMN LABEL:
Tells DCLGEN whether to include labels that are declared on any columns of the table or view as comments in the data declarations. (The SQL statement LABEL creates column labels to use as supplements to column names.)
YES: To include column labels.
NO: To ignore column labels. This is the default.
o STRUCTURE NAME:
You can specify 01 level variable for the host structure.
This is optional and if you do not specify, DCLGEN will create a system generated structure name.
o FIELD NAME PREFIX:
Specifies a prefix that DCLGEN uses to form field names in the output.
For example, if you choose ABCDE, the field names generated are ABCDE1, ABCDE2, and so on.
If you leave this field blank, then the field names are the same as the column names in the table or view.
o DELIMIT DBCS: Tells DCLGEN whether to delimit DBCS (Double Byte Character String) table names and column names in the table declaration.
o COLUMN SUFFIX:
Tells DCLGEN whether to form field names by attaching the column name as a suffix to the value you specify in FIELD NAME PREFIX.
For example, if you specify YES, the field name prefix is NEW, and the column name is EMPNO, the field name is NEWEMPNO.
If you specify YES, you must also enter a value in FIELD NAME PREFIX.
o INDICATOR VARS: Tells DCLGEN whether to generate an array of indicator variables for the host variable structure.
o RIGHT MARGIN: Specifies the break point for statement tokens that must be wrapped to one or more subsequent records.
You will create a DCLGEN for a table TB_RATE in
“THYD018.DCLGEN.COPYLIB(DRATE)”
Go to DB2I and select Option 2 in DB2I Primary Option Menu as follows:
Option 2 for DCLGEN
In DCLGEN menu, enter appropriate values to create the DCLGEN.
Table Name
Dataset in which DCLGEN is created
Once appropriate values are entered here press enter, we’ll get the following message for the successful creation of DCLGEN.
DCLGEN is created in “THYD018.DCLGEN.COPYLIB(DRATE)” as follows:
Example:
******************************************************************
* DCLGEN TABLE(TB_RATE) * * LIBRARY(THYD018.DCLGEN.COPYLIB(DRATE)) * * ACTION(REPLACE) * * LANGUAGE(COBOL) * * QUOTE * * ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS * ******************************************************************
EXEC SQL DECLARE TB_RATE TABLE ( RATE_CAT CHAR(1) NOT NULL, RATE_PER_HOUR INTEGER NOT NULL ) END-EXEC.
******************************************************************
* COBOL DECLARATION FOR TABLE TB_RATE * Message for the successful creation of DCLGEN
10 RATE-PER-HOUR PIC S9(9) USAGE COMP.
******************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 2 * ******************************************************************
Host Variables
An area of storage is allocated by the host language (COBOL) and referenced in an SQL statement and this area is called “Host Variable”. Host structure describes the rows in the table and the host variables are the fields that receive the data that is returned for a row or hold data to update or add a row to a table. We must define host variables in the DATA DIVISION of our program in the WORKING-STORAGE section or in the LINKAGE section. As we’ve seen before, this we’ve achieved by including DCLGEN output in the WORKING-STORAGE section.
When you use host variables in SQL statements, prefix them with a colon (:). When the same variable is referenced by the COBOL program outside the embedded SQL, do not prefix the variable with a colon.
You can use host variables in the following ways:
o As output data areas in the INTO clause of the SELECT and FETCH statements
o As input data areas for the SET clause of the UPDATE statement
o As input data areas for the VALUES clause of the INSERT statement
o As search fields in the WHERE clause for SELECT, INSERT, UPDATE, and DELETE statements
o As literals in the SELECT list of a SELECT statement
Example:
INSERT INTO TB_DEPT (DEPT_NO) VALUES (:HOSTVAR-DEPTNO) END-EXEC.
SQLCA
While executing the application program, we need the information of describing the success or failure of the execution of an embedded SQL statement. For this purpose, you must include a structure called SQLCA (SQL Communication Area) in each DB2 application program. You do so by coding the following statement in our WORKING-STORAGE section:
EXEC SQL
INCLUDE SQLCA END-EXEC.
Following is the COBOL layout of the expanded SQLCA:
01 SQLCA.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMPUTATIONAL.
05 SQLCODE PIC S9(9) COMPUTATIONAL.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMPUTATIONAL.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMPUTATIONAL.
05 SQLWARN.
10 SQLWARN0 PIC X(1).
10 SQLWARN1 PIC X(1).
10 SQLWARN2 PIC X(1).
10 SQLWARN3 PIC X(1).
10 SQLWARN4 PIC X(1).
10 SQLWARN5 PIC X(1).
10 SQLWARN6 PIC X(1).
10 SQLWARN7 PIC X(1).
05 SQLEXT.
10 SQLWARN8 PIC X(1).
10 SQLWARN9 PIC X(1).
10 SQLWARNA PIC X(1).
10 SQLSTATE PIC X(5).
SQLCODE
The field SQLCODE in SQLCA, contains the return code passed by DB2 to the application program. The return code provides information about the execution of the last SQL statement.
SQLCODE Value Meaning
Zero Statement successful
Positive Statement successful, but with some exceptional condition
+100 Row not found or end of data
Negative Statement failed and serious error detected
Try It Out
Problem Statement:
Write a COBOL DB2 application program, to get the hourly rate for a particular rate category supplied by the user.
END-EXEC.
DISPLAY '---'.
2000-EXIT.
EXIT.
9999-DB2-ERROR-ROUTINE.
DISPLAY '---'.
DISPLAY 'ERROR DETECTED'.
DISPLAY WS-ERROR-MESSAGE.
DISPLAY '---'.
9999-EXIT.
EXIT.
Refer File Name: First_RatePerHour_Program_Session#21_Slide#12 to obtain soft copy of the program code
Summary
While developing COBOL DB2 application program, you need to perform the following tasks:
1. Embed the SQL statements and delimit them with EXEC SQL and END-EXEC
2. Include DCLGEN output for the tables used in the program in the WORKING-STORAGE section, which:
i. Explicitly declares the table ii. Defines the host structure
3. Include SQLCA in the WORKING-STORAGE section
4. Check SQLCODE field in SQLCA after executing an SQL statement Test Your Understanding
1. What is ESQL (Embedded SQL)?
2. What is a DCLGEN?
3. What is a host variable?
4. What is SQLCA?
5. What is SQLCODE?
Exercises
Develop a COBOL-DB2 application program which accepts “Patient Number” and displays Patient Information as follows:
Patient Number: xxxx
Patient Name: <First name> <Last name>
Gender: <x>
Date of Birth: <DD/MM/YYYY>