Mainframes 360
The one stop destination for System Z professionals

Sunday, July 11, 2010

COBOL-DB2 Program - Introduction

Q. What are DB2 Storage Areas?
When you write COBOL Programs, that read data records from Files, and store Output results to a File, you must declare Input and Output Storage Areas for the files in the COBOL Program.
Image204[1] 

COBOL has a strict rule – first Declare, then use. Look at the picture above. Firstly whenever, you access data from Files in COBOL, you need to declare(announce) the file-names in COBOL. Moreover, you also declare COBOL Storage Areas for sending data to and receiving data from files. The Input Storage Area is the place in the COBOL Program, where the data arrives and get stored, from a File, out here for the INPUT-FILE it is EMPLOYEE-INPUT-RECORD. When you do a READ INPUT-FILE, the input record QUASAR 1000 20-10-06 is read from the file, and received and stored in EMPLOYEE-INPUT-RECORD Storage Area. Similarly, the Output Storage Area is the place in the COBOL Program, from where the data departs and is output to the File, here for the OUTPUT-FILE it is EMPLOYEE-OUTPUT-RECORD. So, when you do a WRITE EMPLOYEE-OUTPUT-RECORD, whatever’s stored in EMPLOYEE-OUTPUT-RECORD is written to the file.

In the same fashion, when you access data from DB2 Tables in a COBOL Program, you need to first DECLARE(announce) the TABLE in the COBOL Program. Suppose there’s an EMPLOYEE DB2 Table, that you want to access in the COBOL Program. The EMPLOYEE DB2 Table looks like this -

Image206[1]

Before using EMPLOYEE Table, you must DECLARE the SQL EMPLOYEE TABLE at the top of the COBOL Program in the DATA DIVISION. You can DECLARE EMPLOYEE TABLE as shown in the picture below -

Image205[1]

The DECLARE EMPLOYEE TABLE in the COBOL Program makes a statement – hey pal, this COBOL Program uses the EMPLOYEE table, which is broken into EMPID, ENAME, SALARY and JDATE SQL-Fields(columns). Since, this is SQL, and not COBOL Statements, you embed them – put them inside a EXEC SQL and END-EXEC Block.

When you fetch data from these SQL Fields(columns) into the COBOL, there is should also be corresponding COBOL Variables to receive and hold the fetched data. For every SQL-Field, you must declare a corresponding COBOL Variable(Storage Area).  

Image212[1]   

EMPLOYEE-ID is the receiving COBOL Variable which will hold the values fetched from the SQL Field EMPID. The SQL Type INTEGER(4 Bytes) translates to
S9(09) COMP(4 Bytes) in COBOL. EMPLOYEE-NAME is the receiving COBOL Variable, that shall hold the values fetched from the SQL Field ENAME. The SQL Type CHAR(n) translates in COBOL as PIC X(n), where n is the size. EMPLOYEE-NAME is the receiving COBOL variable that shall, store the data fetched from SQL Field SALARY. For the SQL Type DECIMAL(7,2), 7 is the total width, 2 is the digits after the decimal point. Therefore, digits before the decimal point would be
7-2=5. Thus, in COBOL it translates to PIC 9(05)V99 COMP-3. EMPLOYEE-JDATE is the receiving COBOL Variable, for the values retrieved from JDATE SQL Field. Generally, DATE in SQL becomes a PIC X(10) in COBOL, TIME is a PIC X(08), and TIMESTAMP is a PIC X(26).

Specifying the table-name, SQL Fields and the corresponding COBOL Variables at the top of the COBOL Program is called Declaration. You can write DB2 Declaration manually, the way I’ve written, or you can auto-generate it. The tool that auto-generates Declarations, so that you can avoid the manual labour of writing it at the top of the COBOL Program, is called Declaration Generator Tool(DCLGEN Tool).
Q. How do you use DCLGEN tool, to auto-generate Declarations?
The Declarations Generator(DCLGEN) tool can be found inside the DB2I(DB2 Interactive) Menu 2.

Image208[3] 

I have typed the Table-name EMPLOYEE. I have also keyed in the output file(should be a member of a pds/library), in which the auto-generated DB2 Declarations are stored by the DCLGEN Tool. After hitting ENTER, you should get a message, EXECUTION COMPLETE.

Image209[1] 
This is how the DB2 Declarations auto-generated in the AGY0157.DEMO.DCLGEN(EMPLOYEE) file look like -

Image210[1] 
Q. How to use Cursors to retrieve data from DB2 Tables in COBOL?
When you fetch Data from DB2 Tables in COBOL, you cannot directly fetch all the rows from the table in one shot, in one go. Once you execute the query, the results of the Query are fetched from the DB2 Table, and are brought into another temporary, rough-work area – called Cursor Area. The Cursor Area is like a rough scratch-pad area. A marker(pointer) is set to point to the beginning/1st row in Cursor area. Now, when you fetch the data from the cursor, it reads the data-row, to which the marker(pointer) is currently set. After each FETCH, the marker(pointer) is incremented(set) to the next row. This way you FETCH data from the cursor area, row-by-row, until you’ve read all the rows, and reached the end of the Cursor Area.

In this fashion in COBOL-DB2 Programs, whenever the result of a SQL Query is multiple-rows, you must use cursors to fetch the data results row-by-row one at a time.

There are 4 steps to use a Cursor. First, you must DECLARE the Cursor Query. DECLARE C1 CURSOR merely tells, what query to run. Second, you OPEN the cursor. When you OPEN the cursor, the cursor Query is executed, and rows are fetched from the DB2 Table into the Cursor Area. Third, you FETCH data from the Cursor area into the COBOL Program row-by-row, until you reach the End of the Cursor. Fourth and last, you CLOSE the Cursor.

Whenever you perform any SQL Operation, like an OPEN cursor, FETCH data from the cursor, or a CLOSE cursor, the DB2 System returns back a 3-digit status-code to indicate the Success or Failure of the SQL operation. This 3-digit status-code is stored in a Communication Area shared between DB2 and your COBOL Program. This Shared communication-area is called SQLCA. The SQLCA is a 01-level variable(storage-area) in COBOL, that is broken down into two parts – SQLCODE and SQLSTATE.
Q. Could you show me a COBOL Program to read data-rows from a DB2 Table?
First, you need to declare the Cursor Query. DECLARE C1 CURSOR FOR is the statement used to declare a cursor. I am going to declare a cursor, which can retrieve Employee-id, name, salary and joining-date from the Employee-table.

Image213[1]

You also need to supply the SQLCA COBOL Variable, that shall hold the results of the OPEN, FETCH and CLOSE Cursor operations. You want to be sure, that OPEN, FETCH and CLOSE Cursor operations go clean, without any errors, so you need SQLCA to check on that. You generally type an INCLUDE SQLCA entry.

Now, you begin type executable COBOL Instructions in the PROCEDURE DIVISION. Essentially, the task of fetching data-rows from the DB2 Table can be divided into 3-paragraphs 1. OPENing the Cursor 2. FETCHing data from the Cursor row-by-row and 3. CLOSEing Cursor. I have typed the MAIN Paragraph in the PROCEDURE DIVISION like this -

Image214[1] 

Line 37 performs the Open paragraph, to open the cursor.

When you do a FETCH in SQL, it returns(retrieves) back the row, the cursor is currently pointing to. Every-time you FETCH, the current-row is returned, and the cursor is incremented(set to the next row). A SQLCODE=+0 signals a successful FETCH. This sequential row-after-row process of FETCH’ing, continues on and on. But, how do you know, how many rows you want to fetch – 5, 10, 15, how many to be precise. You don’t!

The situation where the cursor points to the Last row, and there is no more data to get, is detected using the special SQLCODE=+100. A SQLCODE=+100 is the special, exceptional condition, where the cursor points to the last row, and there are no more data-rows to FETCH. You should do as many FETCH’s until, you hit SQLCODE=+100. 

Line 38 in the PROCEDURE DIVISION, performs FETCH paragraph to get one row at a time, repeatedly until SQLCODE is set to +100.

Line 39 performs the Close, to close the cursor.

To Open a Cursor(and execute the Cursor query), in SQL you type OPEN cursor-name. I have coded 100-OPEN Paragraph below, which executes the SQL instruction, OPEN EMP_CSR on Line 44. When you OPEN EMP_CSR, it means

SELECT EMPID,ENAME,SALARY,JDATE
FROM EMPLOYEE

query will run. The results will be stored in a SQL Area – EMP_CSR. The contents pf the Cursor Area EMP_CSR are

--> 1    RAM    1000    2010-06-09
    2    RAJ    2000    2005-01-03
    3    RAKESH 3000    2005-02-27

A marker or a pointer is set to the first row in the Cursor-Area. This pointer position, tells what’s the next row to be read?

Image215[1]

To fetch(receive) data-rows from the SQL Cursor-area into the COBOL Program, in SQL you type

FETCH cursor-area-name
INTO  COBOL-variables

The 200-FETCH Paragraph from Lines 48-56 fetches 1 single row from the Cursor Area EMP_CSR. So, this reads the row [1   RAM    1000    2010-06-09]. But, when this data arrives in the COBOL Program, where is it stored? So you also supply a list of COBOL Variables corresponding to each SQL Data-value received. In the picture below, EMPLOYEE-ID COBOL Variable will receive the value 1, EMPLOYEE-NAME COBOL Variable will receive the value RAM,  EMPLOYEE-SALARY COBOL Variable will receive the value 1000, and EMPLOYEE-JDATE COBOL Variable will receive the value 2010-06-09. FETCH cursor-area-name is a SQL Instruction. When you want to use COBOL Variables(non-SQL Variables) like EMPLOYEE-ID, EMPLOYEE-NAME etc. in a SQL Instruction, you prefix them with a :(Colon). I have DISPLAYed the recently fetched EMPLOYEE-INPUT-RECORD. After the FETCH Operation is completed successfully, the SQLCODE is set to +0. The pointer is incremented and set to the next row in the cursor area.

    1    RAM    1000    2010-06-09
--> 2    RAJ    2000    2005-01-03
    3    RAKESH 3000    2005-02-27


The 200-FETCH Paragraph is performed over and over again, till you’ve read the last row, until SQLCODE=+100. The 2nd FETCH will store 2 in EMPLOYEE-ID, RAJ in EMPLOYEE-NAME, 2000 in EMPLOYEE-SALARY and 2005-01-03 in EMPLOYEE-JDATE.


Image216[1]

After you’re done with retrieving/working with all the data rows in the cursor-area, you must CLOSE the Cursor-area. To close the SQL Cursor-area, in SQL you type -

CLOSE cursor-area-name

The 300-CLOSE Paragraph on Lines 59-63 executes the CLOSE EMP_CSR SQL Instruction to close the cursor.

Image217[1]

To many people who are thrown to work at a mainframe computer on their first job, they feel lost. Mainframe people seem to speak a completely different language and that doesn't make life easy. What's more, the books and manuals are incredibly hard to comprehend.

"What on earth is a Mainframe?" is an absolute beginner's guide to mainframe computers. We'll introduce you to the hardware and peripherals. We'll talk about the operating system, the software installed on a mainframe. We'll also talk about the different people who work on a mainframe. In a nutshell, we'll de-mystify the mainframe.

Readers based in India, can buy the e-book for Rs. 50 only or the print book. International readers based in the US and other countries can click here to purchase the e-book.