Appendix D: Case Studies – Introduction to Database Management Systems

Appendix D

Case Studies

D.1 INTRODUCTION

In this appendix, we shall examine several case studies to cement our understanding of various DBMS concepts. We shall take up two sets of topics for discussion.

  • One is related to the aspects of database design. We have discussed database design in depth, with detailed coverage of the important concept of normalisation. We shall consider some database structures and see whether they fit these discussions, and if not, how we can modify them suitably.
  • The other is related to programming examples. We shall consider a few programs that utilise the various concepts in DBMS technology. The primary concept in DBMS programming is the usage of cursors. This is especially used very widely in IBM mainframe applications. This is why we have provided a number of programs that are based on the COBOL programming language. They are actually quite close to real-life programs. For the sake of completeness and for those who are not familiar with COBOL, we have also provided a couple of examples in C.

D.2 DATABASE DESIGN

D.2.1 Credit Card Database

Let us assume that the Cardholders Member (CMF) table contains information about credit card holders and their transactions. For this purpose, we have a table called CMF with the layout shown in Fig. D.1.

What are the possible problems with design? Let us list them down.

  1. The CMF table contains information about many facts, such as the cardholders, their credit cards, and also the transactions performed by the cardholders using those cards. This is not desirable, and would lead to many problems as discussed in Fig. D.1.
  2. Suppose that a cardholder performs ten transactions using a card on a business day. Then, we will have ten rows for this in the CMF table. Note that information about the cardholder (such as the name and address) and also the card itself (such as the card name and validity) will also repeat ten times quite unnecessarily.

    Fig. D.1 CMF table layout

  3. We cannot enter information about a cardholder until she makes a transaction. This is not acceptable at all.
  4. Unless a cardholder holds a particular brand of card (Visa), and worse yet, uses it in a transaction, we cannot store the fact that Visa is an available brand of cards. This is also quite unacceptable.
  5. Suppose that a cardholder makes only one transaction with her card, and that transaction for some reason, is invalid. Therefore, we need to delete the row for this transaction from the CMF table. This will mean that there will be no rows for this cardholder in the CMF table anymore. In other words, we have completely lost information about this cardholder!
  6. If some details about the cardholder (such as the name or address) change, then we must search for and modify all the rows for this cardholder in the CMF table. The same is true with the brand of a card.

In order to solve these problems, we will break down the tables into the following tables. We will not deliberately state which normal forms are being applied, and how. We would leave it to the reader to think about it and list down its results, and to validate that the following design indeed follows the principles of normal forms.

Fig. D.2 Modified table design

Note that the underlined columns form the primary keys of the respective tables.

D.2.2 Employee Database

Let us now think of another example. Assume that we need to store information about the employees in an organisation. Then we can think of a possible database structure as shown in Fig. D.3. We have stored information about the employees, their departments, managers, and payroll.

Fig. D.3 Emp table

Let us examine this table structure now and identify the problems therein. We can list them as follows.

  1. The Emp table contains too much of information about too many things (employees, departments, managers, payroll).
  2. A new employee cannot join unless she is assigned a department, manager, location, and is actually paid at least one salary! This is quite unacceptable.
  3. Information about the department, manager, and location of employees would repeat for as many months as the employee works in the organisation. This is because there would be one record per month for as many months as the employee works for (because of the payroll data).
  4. If the name of an employee changes, we have to change it in all the rows for this employee.
  5. If the name of an employee's manager changes, all rows for this employee also need to change! Same is the case with the department name. When we change a department, this needs to be reflected in the rows for all the employees who are assigned to this department.
  6. Even if a department or a location becomes defunct, we cannot delete it, because employees would have worked for it in the past. As a result, there is no way to know which departments and locations are active, and which are not!
  7. There is no need for the Net pay column. This is because, based on the gross pay, incentives, and deductions, we can always calculate the net pay. (Remember the Normal Form associated with this?)

Fig. D.4 Modified table design

As before, we leave it to the reader to verify that the modified table design shown in Fig. D.4 conforms to the principles of Normal Forms.

D.2.3 Weather Database

This example assumes that we want to store the weather information about various cities. What we want to store are details such as the maximum, minimum, and average temperatures on a particular day. We also want to store the temperatures at certain milestones of the day, that is, morning, afternoon, evening, and night. We also want to record whether the day was sunny, cloudy, normal or whether it rained or snowed. Our initial table design is depicted in Fig. D.5.

Fig. D.5 Weather information table

As before, let us try to find out the possible problems with this table. Does this table contain information about too many things like the previous table designs? Not quite. In fact, the table contains information about just one fact, the weather in a particular city on a particular day. Therefore, most of the problems that could have happened otherwise are clearly eliminated right at the inception. There is just one possible problem, depending on how we define the term average.

  • If the average temperature means the average of maximum and minimum temperatures, we need not maintain this column, because based on the maximum and minimum values, we can always compute it.
  • On the other hand, if the average temperature means the average of the morning, afternoon, evening, and night temperatures, then also we need not store it as a separate column. Because we can always derive it based on the values in these columns.
  • However, if the average temperature is calculated based on some other values externally, then we need to maintain this column in our table. This is because we do not have the values from which we can calculate the average temperature.

All other columns in the table seem to be quite perfect. The table does indeed follow the Normal Forms (at least up to 3NF). Therefore, there is no need to change anything in the table design.

Perhaps the only argument can be whether we need so many columns to store information regarding what kind of day it was (rainy, cloudy,…). Instead, we can use a simple flag to indicate this fact. For instance, if the flag contains R, it means that it was a rainy day. Similarly, C could mean cloudy, and so on.

However, another point is that a day need not be sunny for the whole duration. It may vary many times during the course of the whole day! Therefore, it may snow for a while, then become cloudy, then rainy, and then it may get sunny — all in a single day's course! Therefore, we need these many fields. But then we can also have a single indicator field to hold all these combinations.

For instance, we can have a convention of 1 = Snow, 2 = Cloudy, and so on. The indicator field may contain up to four values. So in the case of an extremely volatile day as described above, it would contain 1 2 3 4. On the other hand, on a day where it snows the whole day, it would contain just 1.

Effectively, this decision is not a DBMS decision per say. It is a decision that will have to be taken in any situation. Hence, we shall not discuss it any further, but we mentioned it in detail so that we are aware of all the possibilities.

D.2.4 Cricket Database

Suppose we want to store the performances of players in international cricket. For this purpose, we create a database that has design as shown in Fig. D.6.

Fig. D.6 Cricket Table

This interesting table is now without its flaws. Let us examine the pitfalls in this table design.

  1. Like some of our other databases, there is too much of information in a single table. We are storing information about player, his performance, match data, result, umpires, and referee in the same table. This leads to many problems, some of which are mentioned below.
  2. If the name of a player changes, it has to be changed in several rows of the table.
  3. The details of umpires and referee per row are unnecessary.
  4. The information about toss and result is also unnecessarily duplicated in all the records.

Let us examine how we can solve this problem with the help of the modified database design as shown in Fig. D.7.

Fig. D.7 Modified table design

Would this database design solve our problems? If you notice, each of the three tables is self-sufficient, and does not contain any unwanted data. They are all linked to each other, too. This linking is on the basis of the match numbers and the player codes.

An interesting exercise would be to try and see if this decomposition of the original table into three tables is lossless or lossy. We would leave this exercise for the reader.

D.3 PROGRAMMING EXAMPLES

In this section, we present a few sample programs to demonstrate DBMS concepts as they are used in application programs. We have used the COBOL programming language to demonstrate this, as it is most widely used with DBMS programming on IBM mainframe computers. The programs are self-explanatory, and contain a lot of comments. So even a reader with little COBOL background should be able to understand them quite easily.

D.3.1 Program for SELECT Operation

************************************************************

*                   PROGRAM DOCUMENTATION

*

* THIS IS A COBOL-DB2 PROGRAM TO DEMONSTRATE DB2

* FUNCTIONALITIES.

*

* THE PROGRAM SIMPLY LOCATES A RECORD FROM THE CMF TABLE

* FOR A GIVEN ACCOUNT NUMBER AND DISPLAYS IT ON THE SCREEN.

* THE COLUMNS OF INTEREST HERE ARE THE ACCOUNT NUMBER,

* ACCOUNT NAME, AND THE CREDIT LIMIT FOR THIS ACCOUNT.

*

* IF THE RECORD IS NOT FOUND IN THE TABLE FOR THAT ACCOUNT

* NUMBER, THE PROGRAM DISPLAYS AN APPROPRIATE MESSAGE.

************************************************************

IDENTIFICATION DIVISION.
PROGRAM-ID. DB2ONE.
AUTHOR. ATUL KAHATE.

ENVIRONMENT DIVISION.

   *

INPUT-OUTPUT SECTION.
FILE-CONTROL.

DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

   *

EXEC SQL
   INCLUDE SQLCA
END-EXEC.

EXEC SQL
   INCLUDE DCLCMF
END-EXEC.

PROCEDURE DIVISION.

************************************************************

* 0000-MAINLINE.

* THIS IS A DUMMY PARAGRAPH. IT MERELY CALLS THE MAIN

* PROCESSING LOGIC OF A000-PROCESS PARAGRAPH. IN MORE COMPLEX

* PROGRAMS, IT WOULD CONTAIN MORE COMPLICATED LOGIC.

************************************************************

0000-MAINLINE.

PERFORM A000-PROCESS            THRU A000-EXIT.
GOBACK.

0000-EXIT.

EXIT.

************************************************************

* A000-PROCESS.

* RUN A SELECT QUERY AND DISPLAY THE RESULTS ON THE SCREEN.

************************************************************

A000-PROCESS.

************************************************************

* WRITE A SELECT QUERY. NOTE THAT WE ARE USING A

* SELECT-FROM-INTO SYNTAX. AS A RESULT, THE VALUES FROM THE

* TABLE GET POPULATED IN THE VARIABLES MENTIONED IN THE INTO

* CLAUSE. THESE VARIABLES ARE CALLED AS HOST VARIABLES.

*

* IF THE QUERY EXECUTION IS SUCCESSFUL, THEN THE ACCOUNT NUMBER

* AND CREDIT LIMIT VALUES FOR THE GIVEN ACCOUNT NUMBER ARE

* FETCHED IN THE CORRESPONDING HOST VARIABLES SPECIFIED BELOW.

************************************************************

EXEC SQL
  SELECT CMF_ACT_NO, CMF_ACT_NAME, CMF_ACT_CR_LIMIT
      FROM CMF
      INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
         :HOST-CMF-CR-LIMIT
      WHERE CMF_ACT-NO = 123456789987654
END-EXEC.

************************************************************

* CHECK SQLCODE. IF IT IS 0, IT MEANS THAT THE ABOVE OPERATION

* WAS SUCCESSFUL. HOWEVER, IF IT IS NOT, THEN THE QUERY HAS

* FAILED FOR SOME REASON. DISPLAY AN ERROR MESSAGE ACCORDINGLY.

************************************************************

IF SQLCODE = 0

DISPLAY ‘ACCOUNT NUMBER: ‘ HOST-CMF-ACT-NO

DISPLAY ‘ACCOUNT NAME : ‘ HOST-CMF-ACT-NAME

DISPLAY ‘CREDIT LIMIT : ‘ HOST-CMF-CR-LIMIT

ELSE

DISPLAY ‘*** ERROR IN SELECT OPERATION ***’

DISPLAY ‘SQLCODE : ’ SQLCODE

END-IF.

A000-EXIT.

EXIT.

D.3.2 Program for SELECT with File Operations

************************************************************

*         PROGRAM DOCUMENTATION

*

* THIS IS A COBOL-DB2 PROGRAM TO DEMONSTRATE DB2

* FUNCTIONALITIES.

*

* THE PROGRAM READS A FILE THAT CONTAINS ACCOUNT NUMBER OF OUR

* INTEREST. THE PROGRAM READS ALL OF THEM ONE-BY-ONE IN A

* SEQUENTIAL FASHION. THIS FILE IS CALLED AS IN-FILE.

*

* FOR EACH ACCOUNT NUMBER READ FROM THE IN-FILE, THE PROGRAM

* SEARCHES IT IN THE CMF TABLE. IF THE ACCOUNT IS FOUND ON THE

* CMF TABLE, THEN THE PROGRAM DISPLAYS IT ON THE SCREEN.

* THE COLUMNS OF INTEREST HERE ARE THE ACCOUNT NUMBER,

* ACCOUNT NAME, AND THE CREDIT LIMIT FOR THIS ACCOUNT.

*

* IF THE RECORD IS NOT FOUND IN THE TABLE FOR THAT ACCOUNT

* NUMBER, THE PROGRAM DISPLAYS AN APPROPRIATE MESSAGE.

************************************************************

IDENTIFICATION DIVISION.
PROGRAM-ID. DB2TWO.
AUTHOR. ATUL KAHATE.

ENVIRONMENT DIVISION.

  *

INPUT-OUTPUT SECTION.
FILE-CONTROL.

************************************************************

* THE PROGRAM DECLARES THE IN-FILE AS A SEQUENTIAL FILE THAT

* WOULD BE USED FOR SOME SORT OF PROCESSING.

************************************************************

SELECT IN-FILE ASSIGN TO DISK

ORGANIZATION IS SEQUENTIAL

ACCESS MODE IS SEQUENTIAL

FILE STATUS IS IN-ST.

DATA DIVISION.
FILE SECTION.

************************************************************

* THE FOLLOWING STATEMENTS DECLARE THE DESCRIPTION FOR THE CMF

* FILE. IN-FILE CONTAINS JUST ONE FIELD, THE ACCOUNT NUMBER.

************************************************************

FD IN-FILE.
01 IN-REC.
   05 IN-ACT-NO             PIC 9(16).

WORKING-STORAGE SECTION.

EXEC SQL

   INCLUDE SQLCA

END-EXEC.

 

EXEC SQL

   INCLUDE DCLCMF

END-EXEC.

PROCEDURE DIVISION.

************************************************************

* 0000-MAINLINE.

* THIS PARAGRAPH IS THE MAIN PROCESSING PARAGRAPH. IT CALLS

* THE OTHER PARAGRAPHS THAT PERFORM THE ACTUAL PROCESSING.

************************************************************

0000-MAINLINE.
   PERFORM Z000-INIT                   THRU Z000-EXIT.
   PERFORM A000-PROCESS         THRU A000-EXIT
            UNTIL IN-ST = ‘10’.
   PERFORM C000-CLOSE              THRU C000-EXIT.
   GOBACK.

0000-EXIT.
   EXIT.

************************************************************

* Z000-INIT.

* DO THE INITIALIATIONS, I.E. OPEN THE IN-FILE.

************************************************************

Z000-INIT.

OPEN INPUT IN-FILE.

IF IN-ST = ‘00’
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN OPENING THE IN FILE ***’
   DISPLAY ‘FILE STATUS: ’ IN-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

0000-EXIT.
   EXIT.

************************************************************

* A000-PROCESS.

* READ THE FIRST RECORD FROM THE INPUT FILE. IF THE FILE IS

* EMPTY, PROCESSING WOULD STOP IMMEDIATELY. OTHERWISE, PERFORM

* THE B000- PARAGRAPH FOR ALL THE RECORDS IN THE INPUT FILE.

************************************************************

A000-PROCESS.

READ IN-FILE.

EVALUATE IN-ST
  WHEN ‘00’
      CONTINUE
  WHEN ‘10’
      DISPLAY ‘*** INPUT FILE IS EMPTY ***’
      GO TO A000-EXIT
  WHEN OTHER
      DISPLAY ‘*** ERROR IN READING THE IN FILE ***’
      DISPLAY ‘FILE STATUS: ’ IN-ST
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
  END-EVALUATE.

PERFORM B000-PROCESS-IN-FILE THRU B000-EXIT
       UNTIL IN-ST = ‘10’.

A000-EXIT.
      EXIT.

************************************************************

* B000-PROCESS-IN-FILE.

* TRY TO FIND A MATCH IN THE CMF DB2 TABLE FOR THE RECORD READ

* FROM THE INPUT FILE. IF A MATCH IS FOUND, DISPLAY THE FIELDS

* OF INTEREST ON THE SCEREN. IF NO MATCH IS FOUND, DISPLAY THE

* ACCOUNT NUMBER FROM THE INPUT FILE FOR WHICH A MISMATCH HAS

* OCCURRED.

*

* IN THE CASE OF AN ERROR, TERMINATE THE PROGRAM.

*

* OTHERWISE, CONTINUE PROCESSING THE INPUT FILE FOR ALL THE

* RECORDS IN THE FILE.

************************************************************

B000-PROCESS-IN-FILE.

EXEC SQL
   SELECT CMF_ACT_NO, CMF_ACT_NAME, CMF_ACT_CR_LIMIT
      FROM CMF
      INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
           :HOST-CMF-CR-LIMIT
      WHERE CMF_ACT_NO = :IN-ACT-NO
END-EXEC.

************************************************************

* CHECK SQLCODE. IF IT IS 0, IT MEANS THAT THE ABOVE OPERATION

* WAS SUCCESSFUL. HOWEVER, IF IT IS NOT, THEN THE QUERY HAS

* FAILED FOR SOME REASON. DISPLAY AN ERROR MESSAGE ACCORDINGLY.

************************************************************

EVALUATE SQLCODE
  WHEN 0
      DISPLAY ‘ACCOUNT NUMBER: ’ HOST-CMF-ACT-NO
      DISPLAY ‘ACCOUNT NAME : ’ HOST-CMF-ACT-NAME
      DISPLAY ‘CREDIT LIMIT : ’ HOST-CMF-CR-LIMIT
  WHEN +100
      DISPLAY ‘*** RECORD NOT FOUND IN CMF TABLE ***’
      DISPLAY ‘INPUT ACT NO : ’ IN-ACT-NO
  WHEN OTHER
      DISPLAY ‘*** ERROR IN SELECT OPERATION ***’
      DISPLAY ‘SQLCODE : ’ SQLCODE
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

************************************************************

* NOW READ THE NEXT RECORD FROM THE INPUT FILE.

************************************************************

READ IN-FILE.

EVALUATE IN-ST
  WHEN ‘00’
      CONTINUE
  WHEN ‘10’
      GO TO B000-EXIT
  WHEN OTHER
      DISPLAY ‘*** ERROR IN READING THE IN FILE ***’
      DISPLAY ‘FILE STATUS: ’ IN-ST
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

B000-EXIT.
   EXIT.

************************************************************

* C000-CLOSE.

* CLOSE THE INPUT FILE.

************************************************************

C000-CLOSE.

CLOSE IN-FILE.

IF IN-ST = ‘00’
  CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN CLOSING THE IN FILE ***’
   DISPLAY ‘FILE STATUS: ’ IN-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

C000-EXIT.
   EXIT.

************************************************************

* Z999-STOP-PROGRAM.

* THERE IS SOME SORT OF AN ERROR IF CONTROL COMES HERE.

* TERMINATE THE PROGRAM IMMEDIATELY.

************************************************************

DISPLAY ‘*** THERE IS AN ERROR IN PROGRAM EXECUTION ***’.
DISPLAY ‘*** PROGRAM IS BEING TERMINATED ABNORMALLY ***’.
DISPLAY ‘*** PLEASE CHECK WHAT HAS HAPPENED ***’.
Z999-EXIT.
   EXIT.

D.3.3 Program for Basic Cursor Operations

************************************************************

*                PROGRAM DOCUMENTATION

*

* THIS IS A COBOL-DB2 PROGRAM TO DEMONSTRATE DB2

* FUNCTIONALITIES.

*

* THE PROGRAM READS ALL THE RECORDS FROM THE CMF TABLE HAVING

* CREDIT LIMIT BELOW 500. IT WRITES ALL THESE RECORDS TO AN

* OUTPUT FILE. THIS FILE IS CALLED AS OUT-FILE.

*

* THE PROGRAM USES THE CONCEPT OF A CURSOR. USING THE CURSOR,

* THE PROGRAM IS ABLE TO BRING ALL THE RECORDS OF INTEREST

* FROM THE TABLE INTO THE PROGRAM'S MEMORY.

************************************************************

IDENTIFICATION DIVISION.
PROGRAM-ID. DB2THREE.
AUTHOR. ATUL KAHATE.

************************************************************

*                PROGRAM DOCUMENTATION

*

* THIS IS A COBOL-DB2 PROGRAM TO DEMONSTRATE DB2

* FUNCTIONALITIES.

*

* THE PROGRAM READS ALL THE RECORDS FROM THE CMF TABLE HAVING

* CREDIT LIMIT BELOW 500. IT WRITES ALL THESE RECORDS TO AN

* OUTPUT FILE. THIS FILE IS CALLED AS OUT-FILE.

*

* THE PROGRAM USES THE CONCEPT OF A CURSOR. USING THE CURSOR,

* THE PROGRAM IS ABLE TO BRING ALL THE RECORDS OF INTEREST

* FROM THE TABLE INTO THE PROGRAM'S MEMORY.

************************************************************

ENVIRONMENT DIVISION.

*

INPUT-OUTPUT SECTION.
FILE-CONTROL.

************************************************************

* THE PROGRAM DECLARES THE OUT-FILE AS A SEQUENTIAL FILE THAT

* WOULD BE USED FOR SOME SORT OF PROCESSING.

************************************************************

SELECT OUT-FILE ASSIGN TO DISK
ORGANIZATION IS SEQUENTIAL
ACCESS MODE IS SEQUENTIAL
FILE STATUS IS OUT-ST.

DATA DIVISION.
FILE SECTION.

************************************************************

* THE FOLLOWING STATEMENTS DECLARE THE DESCRIPTION FOR THE OUT

* FILE. WE WRITE THE ACCOUNT NUMBER, NAME, AND CREDIT LIMIT.

************************************************************

FD OUT-FILE.
01 OUT-REC.
   05 OUT-ACT-NO                   PIC 9(16).
   05 OUT-ACT-NAME             PIC X(30).
   05 OUT-ACT-CR-LIMIT       PIC 9(05).

WORKING-STORAGE SECTION.

01 WS-WRITE-COUNT PIC       9(10) VALUE 0.

EXEC SQL
   INCLUDE SQLCA
END-EXEC.

EXEC SQL
   INCLUDE DCLCMF
END-EXEC.

************************************************************

* DECLARE A CURSOR ON THE CMF TABLE TO BRING RECORDS WHERE

* CREDIT LIMIT IS LESS THAN 500.

************************************************************

EXEC SQL
   DECLARE CMFCURSOR AS
      SELECT CMF_ACT_NO, CMF_ACT_NAME, CMF_ACT_CR_LIMIT
      FROM CMF
      WHERE CMF_ACT_CR_LIMIT < 500
END-EXEC.

PROCEDURE DIVISION.

************************************************************

* 0000-MAINLINE.

* THIS PARAGRAPH IS THE MAIN PROCESSING PARAGRAPH. IT CALLS

* THE OTHER PARAGRAPHS THAT PERFORM THE ACTUAL PROCESSING.

************************************************************

0000-MAINLINE.

PERFORM Z000-INIT             THRU Z000-EXIT.
PERFORM A000-PROCESS             THRU A000-EXIT
         UNTIL SQLCODE = ‘100’.
PERFORM C000-CLOSE          THRU C000-EXIT.
GOBACK.

0000-EXIT.
   EXIT.

************************************************************

* Z000-INIT.

* DO THE INITIALIATIONS, I.E. OPEN THE OUT-FILE AND THE CURSOR.

************************************************************

Z000-INIT.

OPEN OUTPUT OUT-FILE.

IF OUT-ST =  00 
 CONTINUE

ELSE
   DISPLAY ‘*** ERROR IN OPENING THE OUT FILE ***’
   DISPLAY ‘FILE STATUS: ’ OUT-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

EXEC SQL
   OPEN CMFCURSOR
END-EXEC.

IF SQLCODE = 0
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN OPENING CMF CURSOR ***’
   DISPLAY ‘SQLCODE : ’ SQLCODE
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

Z000-EXIT.
   EXIT.

************************************************************

* A000-PROCESS.

* FETCH THE FIRST ROW FROM THE CURSOR. IF NO SUCH ROW EXISTS,

* PROCESSING WOULD STOP IMMEDIATELY. OTHERWISE, PERFORM

* THE B000- PARAGRAPH FOR ALL THE ROWS IN THE CURSOR.

************************************************************

A000-PROCESS.
   EXEC SQL
      FETCH CMFCURSOR
         INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
            :HOST-CMF-CR-LIMIT
   END-EXEC.

EVALUATE SQLCODE
  WHEN 0
      CONTINUE
  WHEN 100
      DISPLAY ‘*** NO MATCHING ROWS IN CMF TABLE ***’
      GO TO A000-EXIT
  WHEN OTHER
      DISPLAY ‘*** ERROR IN FETCHING CMF CURSOR ***’
      DISPLAY ‘SQLCODE : ’ SQLCODE
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
  END-EVALUATE.

PERFORM B000-PROCESS-CURSOR THRU B000-EXIT
      UNTIL SQLCODE = +100.

A000-EXIT.
   EXIT.

************************************************************

* B000-PROCESS-CURSOR.

* FETCH CURSOR AND WRITE RECORD TO THE OUTPUT FILE. REPEAT FOR

* ALL THE MATCHING ROWS IN THE TABLE (I.E. TILL THE CURSOR

* BRINGS IN MORE ROWS FOR PROCESSING).

************************************************************

B000-PROCESS-CURSOR.

************************************************************

* CHECK SQLCODE. IF IT IS 0, IT MEANS THAT THE ABOVE OPERATION

* WAS SUCCESSFUL. HOWEVER, IF IT IS NOT, THEN THE QUERY HAS

* FAILED FOR SOME REASON. DISPLAY AN ERROR MESSAGE ACCORDINGLY.

************************************************************

EVALUATE SQLCODE
 WHEN 0
      MOVE HOST-CMF-ACT-NO TO OUT-ACT-NO
      MOVE HOST-CMF-ACT-NAME TO OUT-ACT-NAME
      MOVE HOST-CMF-ACT-CR-LIMIT TO OUT-ACT-CR-LIMIT
 WHEN +100
      GO TO B000-EXIT
 WHEN OTHER
      DISPLAY ‘*** ERROR IN FETCHING CMF CURSOR ***’
      DISPLAY ‘SQLCODE : ’ SQLCODE
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

************************************************************

* NOW WRITE THE RECORD TO THE OUTPUT FILE.

************************************************************

WRITE OUT-REC.

EVALUATE OUT-ST
 WHEN ‘00’
      ADD 1 TO WS-WRITE-COUNT
 WHEN OTHER
      DISPLAY ‘*** ERROR IN WRITING TO THE OUTPUT FILE ***’
      DISPLAY ‘FILE STATUS: ’ OUT-ST
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

EXEC SQL
   FETCH CMFCURSOR
      INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
         :HOST-CMF-CR-LIMIT
END-EXEC.

B000-EXIT.
   EXIT.

************************************************************

* C000-CLOSE.

* CLOSE THE OUTPUT FILE, CURSOR AND DISPLAY THE RECORD COUNT.

************************************************************

C000-CLOSE.

CLOSE OUT-FILE.

IF OUT-ST = ‘00’
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN CLOSING THE OUT FILE ***’
   DISPLAY ‘FILE STATUS: ‘ OUT-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

EXEC SQL
   CLOSE CMFCURSOR
END-EXEC.

IF SQLCODE = 0
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN CLOSING CMF CURSOR ***’
   DISPLAY ‘SQLCODE : ‘ SQLCODE
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

DISPLAY ‘=== NUMBER OF RECORDS WRITTEN TO OUT FILE === ‘.
DISPLAY WS-WRITE-COUNT.
DISPLAY ‘===’.

C000-EXIT.
   EXIT.

************************************************************

* Z999-STOP-PROGRAM.

* THERE IS SOME SORT OF AN ERROR IF CONTROL COMES HERE.

* TERMINATE THE PROGRAM IMMEDIATELY.

************************************************************

DISPLAY ‘*** THERE IS AN ERROR IN PROGRAM EXECUTION ***’.
DISPLAY ‘*** PROGRAM IS BEING TERMINATED ABNORMALLY ***’.
DISPLAY ‘*** PLEASE CHECK WHAT HAS HAPPENED ***’.

Z999-EXIT.
   EXIT.

D.3.4 Program for Advanced Cursor Operations

************************************************************

*               PROGRAM DOCUMENTATION

*

* THIS IS A COBOL-DB2 PROGRAM TO DEMONSTRATE DB2

* FUNCTIONALITIES.

*

* WE ASSUME THE FOLLOWING. CMF TABLE CONTAINS THE ACCOUNT

* NUMBERS, NAMES, AND THE TOTAL VALUE OF TRANSACTIONS FOR THE

* ACCOUNT/CARD HOLDER AS OF DATE. THERE IS ANOTHER TABLE

* CALLED AS THE TRANS TABLE. THIS TABLE CONTAINS THE

* TRANSACTIONS PERFORMED BY THE CARDHOLDER. THE SUM OF THE

* AMOUNTS OF ALL THESE TRANSACTIONS MUST MATCH WITH THE TOTAL

* VALUE STORED IN THE CMF TABLE FOR THIS CARDHOLDER. IF IT

* NOT, THEN THERE IS AN ERROR. WE NEED TO WRITE THE NUMBERS,

* NAMES, TOTAL AS IN CMF TABLE AND TOTAL AS IN TRANS TABLE FOR

* ALL MISMATCHES TO A FILE.

************************************************************

IDENTIFICATION DIVISION.
PROGRAM-ID. DB2FOUR.
AUTHOR. ATUL KAHATE.

ENVIRONMENT DIVISION.

*

INPUT-OUTPUT SECTION.
FILE-CONTROL.

************************************************************

* THE PROGRAM DECLARES THE OUT-FILE AS A SEQUENTIAL FILE THAT

* WOULD BE USED TO REPORT THE MISMATCHES.

************************************************************

SELECT OUT-FILE ASSIGN TO DISK
ORGANIZATION IS SEQUENTIAL
ACCESS MODE IS SEQUENTIAL
FILE STATUS IS OUT-ST.

DATA DIVISION.
FILE SECTION.

************************************************************

* THE FOLLOWING STATEMENTS DECLARE THE DESCRIPTION FOR THE OUT

* FILE. WE WRITE THE ACCOUNT NUMBER, NAME, AND CREDIT LIMIT.

************************************************************

FD OUT-FILE.
01 OUT-REC.
   05 OUT-ACT-NO                    PIC 9(16).
   05 OUT-ACT-NAME              PIC X(30).
   05 OUT-CMF-TOTAL            PIC 9(10).
   05 OUT-TRANS-TOTAL        PIC 9(10).

WORKING-STORAGE SECTION.
01 WS-WRITE-COUNT             PIC 9(10) VALUE 0.
01 WS-TOTAL                            PIC 9(10) VALUE 0.
01 EOF-CMF                               PIC X(01) VALUE ‘N’.

EXEC SQL
   INCLUDE SQLCA
END-EXEC.

EXEC SQL
   INCLUDE DCLCMF
END-EXEC.

EXEC SQL
   INCLUDE DCLTRANS
END-EXEC.

************************************************************

* DECLARE A CURSOR ON THE CMF TABLE.

************************************************************

EXEC SQL
   DECLARE CMFCURSOR AS
      SELECT CMF_ACT_NO, CMF_ACT_NAME, CMF_TOTAL
      FROM CMF
END-EXEC.

PROCEDURE DIVISION.

************************************************************

* 0000-MAINLINE.

* THIS PARAGRAPH IS THE MAIN PROCESSING PARAGRAPH. IT CALLS

* THE OTHER PARAGRAPHS THAT PERFORM THE ACTUAL PROCESSING.

************************************************************

0000-MAINLINE.
   PERFORM Z000-INIT                   THRU Z000-EXIT.
   PERFORM A000-PROCESS          THRU A000-EXIT
            UNTIL EOF-CMF = ‘Y’.
   PERFORM C000-CLOSE                THRU C000-EXIT.
   GOBACK.

0000-EXIT.
   EXIT.

************************************************************

* Z000-INIT.

* DO THE INITIALIATIONS, I.E. OPEN THE OUT-FILE AND THE CURSOR.

************************************************************

Z000-INIT.

OPEN OUTPUT OUT-FILE.

IF OUT-ST = ‘00’
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN OPENING THE OUT FILE ***’
   DISPLAY ‘FILE STATUS: ‘ OUT-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

EXEC SQL
   OPEN CMFCURSOR
END-EXEC.

IF SQLCODE = 0
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN OPENING CMF CURSOR ***’
   DISPLAY ‘SQLCODE : ‘ SQLCODE
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

Z000-EXIT.
   EXIT.

************************************************************

* A000-PROCESS.

* FETCH THE FIRST ROW FROM THE CURSOR. IF NO SUCH ROW EXISTS,

* PROCESSING WOULD STOP IMMEDIATELY. OTHERWISE, PERFORM

* THE B000- PARAGRAPH FOR ALL THE ROWS IN THE CURSOR.

************************************************************

A000-PROCESS.
   EXEC SQL
      FETCH CMFCURSOR
            INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
               :HOST-CMF-TOTAL
   END-EXEC.

EVALUATE SQLCODE
   WHEN 0
      CONTINUE
   WHEN 100
      DISPLAY ‘*** NO ROWS IN CMF TABLE ***’
      GO TO A000-EXIT
   WHEN OTHER
      DISPLAY ‘*** ERROR IN FETCHING CMF CURSOR ***’
      DISPLAY ‘SQLCODE : ‘ SQLCODE
      PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

   PERFORM B000-PROCESS-CURSOR THRU B000-EXIT
         UNTIL EOF-CMF = ‘Y’.
A000-EXIT.
   EXIT.

************************************************************

* B000-PROCESS-CURSOR.

* FETCH CURSOR AND DO THE NECESSARY PROCESSING. REPEAT FOR

* ALL THE MATCHING ROWS IN THE TABLE (I.E. TILL THE CURSOR

* BRINGS IN MORE ROWS FOR PROCESSING).

************************************************************

B000-PROCESS-CURSOR.

************************************************************

* CHECK SQLCODE. IF IT IS 0, IT MEANS THAT THE SQL OPERATION

* WAS SUCCESSFUL. HOWEVER, IF IT IS NOT, THEN THE QUERY HAS

* FAILED FOR SOME REASON. DISPLAY AN ERROR MESSAGE ACCORDINGLY.

************************************************************

EVALUATE SQLCODE
   WHEN 0

************************************************************

* FIND THE SUM OF TRANSACTIONS FOR THIS ACOCUNT HOLDER FROM THE

* TRANS TABLE. IF IT MATCHES WITH THE TOTAL FETCHED FROM THE

* CMF TABLE, SKIP THIS RECORD. IF IT DOES NOT, WRITE TO OUTPUT

* FILE AS AN ERROR.

************************************************************

   PERFORM B100-COMPARE-TOTALS THRU B100-EXIT
WHEN +100
   MOVE ‘Y’ TO EOF-CMF
   GO TO B000-EXIT
WHEN OTHER
   DISPLAY ‘*** ERROR IN FETCHING CMF CURSOR ***’
   DISPLAY ‘SQLCODE : ‘ SQLCODE
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

EXEC SQL
   FETCH CMFCURSOR
      INTO :HOST-CMF-ACT-NO, :HOST-CMF-ACT-NAME,
         :HOST-CMF-TOTAL
END-EXEC.

B000-EXIT.
   EXIT.

************************************************************

* B100-COMPARE-TOTALS.

* COMPARE THE TOTAL FROM THE CMF WITH THE TOTALS CALCULATED ON

* THE TRANS TABLE.

************************************************************

B100-COMPARE-TOTALS.

EXEC SQL
   SELECT SUM (TRANS-AMT)
   INTO :WS-TOTAL
   FROM TRANS
   WHERE TRANS_ACT_NO = :HOST-CMF-ACT-NO
   GROUP BY TRANS_ACT_NO
END-EXEC.

EVALUATE SQLCODE
  WHEN 0
    IF WS-SUM = CMF-ACT-TOTAL
     CONTINUE
   ELSE
     PERFORM B110-WRITE THRU B110-EXIT
   END-IF
 WHEN 100
     CONTINUE
  WHEN OTHER
      DISPLAY ‘*** ERROR IN SELECT ON TRANS TABLE ***’
      DISPLAY ‘SQLCODE : ‘ SQLCODE
       PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

B100-EXIT.
   EXIT.

************************************************************

* B110-WRITE.

* WRITE MISMATCH RECORD TO THE OUTPUT FILE.

************************************************************

B110-WRITE.

MOVE HOST-CMF-ACT-NO             TO OUT-ACT-NO.
MOVE HOST-CMF-ACT-NAME       TO OUT-ACT-NAME.
MOVE CMF-ACT-TOTAL                 TO OUT-CMF-TOTAL.
MOVE WS-SUM                                  TO OUT-TRANS-TOTAL.

WRITE OUT-REC.

EVALUATE OUT-ST
   WHEN ‘00’
      ADD 1 TO WS-WRITE-COUNT
   WHEN OTHER
       DISPLAY ‘*** ERROR IN WRITING TO THE OUTPUT FILE ***’
       DISPLAY ‘FILE STATUS: ‘ OUT-ST
       PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-EVALUATE.

B110-EXIT.
   EXIT.

************************************************************

* C000-CLOSE.

* CLOSE THE OUTPUT FILE, CURSOR AND DISPLAY THE RECORD COUNT.

************************************************************

C000-CLOSE.

CLOSE OUT-FILE.

IF OUT-ST = ‘00’
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN CLOSING THE OUT FILE ***’
   DISPLAY ‘FILE STATUS: ‘ OUT-ST
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

EXEC SQL
   CLOSE CMFCURSOR
END-EXEC.

IF SQLCODE = 0
   CONTINUE
ELSE
   DISPLAY ‘*** ERROR IN CLOSING CMF CURSOR ***’
   DISPLAY ‘SQLCODE : ‘ SQLCODE
   PERFORM Z999-STOP-PROGRAM THRU Z999-EXIT
END-IF.

DISPLAY ‘=== NUMBER OF RECORDS WRITTEN TO OUT FILE === ‘.
DISPLAY WS-WRITE-COUNT.
DISPLAY ‘===’.

C000-EXIT.
   EXIT.

************************************************************

* Z999-STOP-PROGRAM.

* THERE IS SOME SORT OF AN ERROR IF CONTROL COMES HERE.

* TERMINATE THE PROGRAM IMMEDIATELY.

************************************************************

DISPLAY ‘*** THERE IS AN ERROR IN PROGRAM EXECUTION ***’.
DISPLAY ‘*** PROGRAM IS BEING TERMINATED ABNORMALLY ***’.
DISPLAY ‘*** PLEASE CHECK WHAT HAS HAPPENED ***’.

Z999-EXIT.
EXIT.

D.3.5 Program in C for SELECT Operation

/* Program to print the details of an account if it is found on the CMF table */

#include <stdio.h>
#include <file.h>
#include <string.h>

#include “dclcmf.h"

EXEC SQL INCLUDE SQLCA;

main ()
{

/* Execute the SQL query */
EXEC SQL

           SELECT CMF_ACT_NO, CMF_ACT_NAME, CMF_ACT_CR_LIMIT

FROM CMF

INTO :HOST_CMF_ACT_NO, :HOST_CMF_ACT_NAME,

:HOST_CMF_CR_LIMIT

                    WHERE CMF_ACT_NO = 123456789987654;

/* Check SQLCODE and take an appropriate action */

if (SQLCODE == 0)

{

        printf("\nACCOUNT NUMBER: %d", HOST_CMF_ACT_NO);

        printf("\nACCOUNT NAME : %s", HOST_CMF_ACT_NAME);

        printf("\nCREDIT LIMIT : %d", HOST_CMF_CR_LIMIT);

}

else

{

        printf("\n*** ERROR IN SELECT OPERATION ***");

        printf("\nSQLCODE : %d", SQLCODE);

}

}