DFSORT - Identify invalid dates

You can use DFSORT's TOGREG or TOJUL functions to identify invalid input dates. Dates with values outside of the valid range (for example, a month not between 01-12) will be shown as asterisks making them easy to identify. For example, if you had the following input records with 'yyyymmdd' dates:

Betten 20091021
Vezinaw 20091101
Casad 00000000
Boenig 20091325
Kolusu 20090931
Yaeger 20090731

You could use these DFSORT control statements to display an additional column with asterisks for any invalid dates:

OPTION COPY
OUTREC OVERLAY=(30:16,8,Y4T,TOGREG=Y4T)

SORTOUT would have these records:

BETTEN 20091021 20091021
VEZINAW 20091101 20091101
CASAD 00000000 00000000
BOENIG 20091325 ********
KOLUSU 20090931 ********
YAEGER 20090731 20090731

If you wanted to display only the records with invalid dates, you could use these DFSORT control statements:

OPTION COPY
OUTREC OVERLAY=(30:16,8,Y4T,TOGREG=Y2T)
OUTFIL INCLUDE=(30,1,CH,EQ,C'*'),BUILD=(1,25)

SORTOUT would then have these records:

BOENIG 20091325
KOLUSU 20090931

Convert between different types of dates using DFSORT

DFSORT's TOJUL and TOGREG functions make it easy to convert between various types of Julian, Gregorian,4-digit year, 2-digit year, CH, ZD, and PD dates.

1. The following DFSORT job converts a 'yyyymmdd' date to a 'yyyyddd' date:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
20090520
20100106
20100921
20081217
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC BUILD=(1,8,Y4T,TOJUL=Y4T)
/*

SORTOUT would have these records:

2009140
2010006
2010264
2008352

2. The following DFSORT job converts a 'yymmdd' date to a 'yyyy-ddd' date:

//S2 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
ABC 090520
DEF 100106
GHI 100921
JKL 081217
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION Y2PAST=1990
SORT FIELDS=(5,6,Y2T,A)
OUTREC OVERLAY=(5:5,6,Y2T,TOJUL=Y4T(-))
/*

SORTOUT would have these sorted records:

JKL 2008-352
ABC 2009-140
DEF 2010-006
GHI 2010-264

3. The following DFSORT job converts a P'dddyyyy' date starting in position 21 to a 'mm-dd-yyyy' date starting in position 51:

//S3 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSIN DD *
OPTION COPY
OUTFIL OVERLAY=(51:21,4,Y4X,TOGREG=Y4W(-))
/*

Here's an example of the input and converted fields:

21            51
P'2122008' -> 07-30-2008
P'0722010' -> 03-13-2010

4. The following DFSORT job converts a 'yyyy-mm-dd' date to a P'mmddyy' date.

//S4 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
2009-05-20
2010-01-06
2010-09-21
2008-12-17
//SORTOUT DD DSN=... output file
//SYSIN DD *
SORT FIELDS=(1,10,CH,A)
OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,10,UFF,TO=ZD,LENGTH=8)),
IFTHEN=(WHEN=INIT,BUILD=(1,8,Y4T,TOGREG=Y2Y))
/*

Here's what the sorted and converted fields would look like:

P'121708'
P'052009'
P'010610'
P'092110'

5. The following DFSORT job converts a 'mm/dd/yyyy' date to a 'yyyyddd' date:

//S5 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
04/08/2006ABC
11/15/2008DEF
09/30/2010GHI
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
OUTREC IFTHEN=(WHEN=INIT,
BUILD=(1,10,UFF,TO=ZD,LENGTH=8,9:11,3)),
IFTHEN=(WHEN=INIT,BUILD=(1,8,Y4W,TOJUL=Y4T,9,3))
/*

SORTOUT would have these records:

2006098ABC
2008320DEF
2010273GHI

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.