How DB2 Provides for Referential Integrity

The REFERENTIAL INTEGRITY feature of DB2 helps us maintain the relationships between the rows in related tables.

Referential Integrity feature can be used to enforce the integrity of the relationships between a parent table and a dependent table.
To enable this feature , the DBA adds clauses to the CREATE TABLE statements that establish the referential constraints between the parent and the dependent   table.

CREATE TABLE MM01.DEPARTMENT
      (DEPTNO          CHAR(6)    NOT NULL,
       DEPTNAME        CHAR(20)   NOT NULL,
       PRIMARY KEY (DEPTNO))
IN DATABASE DB1

CREATE TABLE MM01.STAFF
      (STAFFNO         CHAR(6)    NOT NULL,
       FNAME           CHAR(20)   NOT NULL,
       LNAME           CHAR(20)   NOT NULL,
       STDEPT          CHAR(6)    NOT NULL,
       PRIMARY KEY (STAFFNO),
       FOREIGN KEY DEPTNO (STDEPT)
       REFERENCES MM01.DEPARTMENT
       ON DELETE CASCADE)
IN DATABASE DB1

In above example, DEPTNO is primary key for DEPARTMENT table and STDEPT is the foreign key in the STAFF table that refers to the DEPTNO in DEPARTMENT table. The ON DELETE option for this foreign key is CASCADE, which means that any deletions of parent rows should be cascaded to the related rows in the STAFF table.
We cant add a row to a dependent table unless it has a foreign key that matches a value in the parent table. And we cant change the primary key of a row in parent table that has a matching foreign keys in a dependent table.

What happens when we try to delete a row in a parent table ? That depends on the setting of ON DELETE option. if the RESTRICT option is on, we cant delete a row in parent table that has matching row in the dependent table. If the CASCADE option is on, then the row in the parent table is deleted along with all the matching rows in the dependent table. And if the SET TO NULL option is on , the row in the parent table is deleted and the foreign keys in all related records in the  dependent table are set to NULL values.

How to UPDATE and DELETE rows in a cursor controlled result table

If in a COBOL program we need to UPDATE or DELETE rows using a cursor-controlled result table, then we use DECLARE CURSOR and OPEN CURSOR statements to open a cursor-controlled table. Next, we use FETCH statement to move through the rows in the table. Then when we reach a row that we want to update or delete, we issue an UPDATE or DELETE statement statement to perform a positioned update or delete.
Consider the below examples :-

A DECLARE CURSOR statement for a customer table is given below -

EXEC SQL
  DECLARE CUSTCURS CURSOR WITH HOLD FOR
      SELECT CUSTNO, FNAME, LNAME, CITY, STATE
      FROM MM01.CUSTOMER
      WHERE INVCUST < '100000' 
      FOR UPDATE OF FNAME, LNAME, CITY, STATE
END-EXEC.

An UPDATE statement that changes the row at the current cursor position -

EXEC SQL
    UPDATE MM01.CUSTOMER
          SET FNAME = :FNAME
              LNAME = :LNAME
              CITY  = :CITY
              STATE = :STATE
          WHERE CURRENT OF CUSTCURS
END-EXEC.

A DELETE statement that removes the row at the current cursor position -

EXEC SQL
    DELETE FROM MM01.CUSTOMER
          WHERE CURRENT OF CUSTCURS
END-EXEC.

The WITH HOLD phrase that is coded in the DECLARE CURSOR statement in above example prevents the cursor from being closed when a commit operation takes place.

Restrictions on UPDATE and DELETE statements :-

1. We cant update or delete a row in read-only table
2. A cursor-controlled table is a read-only table when it uses those DB2 features that are incompatible with updating or deleting. For example, a SELECT statement within a DECLARE CURSOR statement uses the DISTINCT keyword, its resukt table is a read-only.
3. A cursor controlled table is read-only , when the SELECT statement in the DECLARE CURSOR statement sues a UNION, some categories of subqueries, a column function or any of these keywords : DISTINCT, ORDER BY, GROUP BY and HAVING.

How to use CURSORS to process multiple rows.

Consider below SELECT query -

SELECT FNAME, LNAME
     FROM MM01.CUSTOMER
        WHERE STATE = 'MH'

This select statement can possibly return more than one row from the table. Hence we should not code the INTO caluse with this type of statement, and we shouldn't code it inside the Procedure Division.
Instead, we must code it inside DECLARE CURSOR statement.

Hence, to process a result table that contains more than one row in a COBOL program, we must use a cursor. A CURSOR is a pointer that identifies the current-row in a result table. When we user cursor, we work through a result table one row at a time.

Four SQL statements that is used for cursor processing are :
DECLARE CURSOR - Defines a result table and names a cursor for it.
OPEN cursor-name - Creates the result table and positions the cursor before the first row in the table.
FETCH cursor-name -Fetches the next row from the result table.
CLOSE cursor-name - Closes the result table.

Examples :
A DECLARE CURSOR statement that defines a cursor named CUST_CURS

EXEC SQL 
    DECLARE CUST_CURS CURSOR FOR
        SELECT EMPNO, DEPTNO, FNAME, LNAME
        FROM MM01.CUSTOMER
            WHERE DEPTNO = :DEPTNO
END-EXEC.

An OPEN statement that creates the result table defined by the CUST_CURS

EXEC SQL
    OPEN CUST_CURS
END-EXEC.

A FETCH statement that fetches the next row in the CUST_CURS resukt table

EXEC SQL
    FETCH CUST_CURS
         INTO :EMPNO, :DEPTNO, :FNAME, :LNAME
END-EXEC.

A CLOSE statement that closes the CUST_CURS cursor

EXEC SQL
    CLOSE CUST_CURS
END-EXEC.

DB2 will automatically close the cursor when our program terminates.
When a FETCH statement is executed and there are no more rows in the result table , DB2 returns a value of +100 to SQLCODE field.

We can also code the DISTINCT keyword to exclude duplicate rows from the result table and we could code ORDER BY clause to sort the rows in the result table.

DECLARE CURSOR can be coded in Working-Storage Section.

How to modify the data in a DB2 table

DB2 INSERT Statement :
We can use INSERT statement to insert one or more rows into a DB2 table. The syntax of this statement is different depending on whether we are adding a single row or selected rows.

The syntax of an INSERT statement that ads a single row -

INSERT INTO table-name
    [(column-list)]
     VALUES (value-list) 

Example is given below -

INSERT INTO MM01.CUST1
       ( CUSTNO, FNAME, LNAME, CITY)
VALUES ( :CUSTNO, :FNAME, :LNAME, :CITY) 

To add a single row to a table, we specify  the name of the table we want to add the row to the names of the columns we are supplying data for , and the values for those columns. In above example the names that are preceded by a colon represent fields in the COBOL program called "Host Variables". These variables must be coded in the same sequence as the columns in the column list.

To add more than one row to a table, we include a SELECT statement within the INSERT statement. Then, the SELECT statement retrieves rows from one or more tables based on the conditions we specify, and the INSERT statement will add those rows to another table. See the example below -

INSERT INTO MM01.NEW_CUST
    SELECT *
          FROM MM01.OLD_CUST
          WHERE CUSTNO < '1000' 

In the above example the SELECT statement selects all the columns from the rows in the old customer table with customer number greater than less than 1000 and inserts them into new customer table.

DB2 UPDATE statement :
To change the values of one or more columns in a DB2 table , we use the UPDATE statement. Here we specify the name of the table we want to update, expressions that indicate the columns we want to change and how we want to change them, and a condition that indicates the row or rows we want to change.

The syntax of an INSERT statement that ads a single row -

UPDATE table-name
    SET expression-list
     WHERE selection-condition 

Example is given below -

UPDATE MM01.CUSTOMER
       SET FNAME = :FNAME
           LNAME = :LNAME
           CITY  = :CITY
    WHERE CUSTNO = '27061' 

In above example, the row with customer number equal to '27061' will be updated.

DB2 DELETE statement :
To delete rows from a DB2 table, we use the DELETE statement . In this statement , we specify the table name we want to delete one or more rows from and a condition that indicates the rows we want to delete.

The syntax of an INSERT statement that ads a single row -

DELETE FROM table-1
     WHERE condition 

Example is given below -

DELETE FROM MM01.CUSTOMER
    WHERE CUSTNO = '27061' 

The DELETE statement in above example will delete the row from customer table with customer number equal to 27061.

How to query a single DB2 table

Below syntax shows how to use a SELECT statement to get data from a single DB2 table.

SELECT column-list
FROM table-name
[WHERE selection-condition]
[ORDER BY sort-column [DESC]  [, sort-column [DESC]] ...]

Example is given below to retrieve and sort selected columns and rows from an INVOICE table :

SELECT INVNO, INVDATE, INVTOTAL
FROM MM01.INVOICE
WHERE INVTOTAL > 100
ORDER BY INVDATE DESC

Here query will select rows from INVOICE table only if it has INVTOTAL greater than 100. And it sorts the returned rows by the invoice date in descending sequence.

We can also have a query like below :

SELECT INVNO, INVDATE, INVTOTAL,
       INVTOTAL - PAYTOTAL - CRTOTAL AS BALANCE 
FROM MM01.INVOICE
WHERE INVTOTAL - PAYTOTAL - CRTOTAL > 0
ORDER BY BALANCE DESC

Please note that in this SELECT statement that the last column in the query is BALANCE which is calculated by subtracting the payment total(PAYTOTAL) and the credit total (CRTOTAL) from the invoice total(INVTOTAL). That is, BALANCE doesn't actually exist as column in INVOICE table. This column is called calculated column and only exist in the results of the query.

Inorder to select all the columns from the table, we can have below query:

SELECT * FROM MM01.INVOICE

But for performance stuffs, we should only select the columns that we need for our requirements.

COBOL DB2 Checkpoint Restart Scenario

Suppose, a batch program that basically reads an input file and posts the updates/inserts/deletes to DB2 tables in the database was abended before the end of the job because of some reasons; Is it possible to tell - How many input records were processed? Were any of the updates committed to the database or can the job be started from the beginning? 

Assume that COMMIT logic was not coded for large batch jobs that process millions of records. If an ABEND occurs all database updates will be rolled back and the job can be resubmitted from the beginning. If an ABEND occurs near the end of the process, the rollback of all the updates is performed. Also, DB2 will maintain a large number of locks for a long period of time, reducing concurrency in the system. In fact, the program may ABEND if it tries to acquire more than the installation-defined maximum number of locks.Program without COMMIT logic causes excessive locking. 

To avoid the above difficulties COMMIT-RESTART LOGIC is recommended for all the batch programs performing updates/inserts/deletes. This involves setting up a batch-restart control table to store the last input record processed and other control information. The restart control table can also be used as an instrumentation table to control the execution, commit frequency, locking protocol and termination of batch jobs.