CICS Login screen with cobol code

CICS LOGIN MAP FILE

LOG17   DFHMSD TYPE=&SYSPARM,MODE=INOUT,LANG=COBOL,TIOAPFX=YES         
LOG17   DFHMDI SIZE=(24,80),LINE=1,COLUMN=1                            
        DFHMDF POS=(1,60),LENGTH=5,INITIAL='DATE:',ATTRB=(PROT)        
DATE    DFHMDF POS=(1,67),LENGTH=10,INITIAL='--/--/----',ATTRB=(PROT)  
        DFHMDF POS=(2,60),LENGTH=5,INITIAL='TIME:',ATTRB=(PROT)        
TIME    DFHMDF POS=(2,67),LENGTH=8,INITIAL='--:--:--',ATTRB=(PROT)     
        DFHMDF POS=(4,30),LENGTH=20,INITIAL='--------------------',    X
               ATTRB=(PROT)                                            
        DFHMDF POS=(5,30),LENGTH=20,INITIAL=' WELCOME TO LIBRARY ',    X
               ATTRB=(PROT)                                            
        DFHMDF POS=(6,30),LENGTH=20,INITIAL='--------------------',    X
               ATTRB=(PROT)                                            
        DFHMDF POS=(8,20),LENGTH=20,INITIAL='    LOGON SCREEN    ',    X
               ATTRB=(PROT)                                            
        DFHMDF POS=(10,20),LENGTH=20,INITIAL='    MEMBER ID : ',       X
               ATTRB=(PROT)                                            
MEMID   DFHMDF POS=(10,42),LENGTH=5,INITIAL='_____',                   X
               ATTRB=(UNPROT,IC,FSET)                                  
        DFHMDF POS=(10,49),LENGTH=1,INITIAL=' ',ATTRB=(ASKIP)          
        DFHMDF POS=(12,20),LENGTH=20,INITIAL='     PASSWORD  :',       X
               ATTRB=(PROT)                                            
PASS    DFHMDF POS=(12,42),LENGTH=5,INITIAL='_____',                   X
               ATTRB=(DRK,UNPROT,FSET)                                 
        DFHMDF POS=(12,49),LENGTH=1,INITIAL=' ',ATTRB=(PROT)           
        DFHMDF POS=(13,42),LENGTH=5,INITIAL='_____',                   X
               ATTRB=(PROT)                                            
        DFHMDF POS=(15,20),LENGTH=9,INITIAL='MESSAGE :',               X
               ATTRB=(PROT)                                            
MSG     DFHMDF POS=(15,30),LENGTH=20,INITIAL='____________________',   X
               ATTRB=(PROT)                                            
        DFHMDF POS=(20,20),LENGTH=40,ATTRB=(PROT),                     X
               INITIAL='NEW USER --> F2           LOGOFF --> F3'       
        DFHMDF POS=(22,20),LENGTH=40,ATTRB=(PROT),                     X
               INITIAL='EXIT     --> F2    REFRESH/CLEAR --> F3'       
        DFHMSD TYPE=FINAL                                              
        END 

COBOL CODE FOR LOGIN SCREEN IF LOGIN SUCCESSFULL SHOWS THE NAVIGATION SCREEN(MAP FILE MISSING HERE) IF LOGIN NOT SUCCESSFULL DISPLAYS THE LOGIN SCREEN WITH MESSAGE LOGIN FAILURE

         IDENTIFICATION DIVISION.     
         PROGRAM-ID.     LOGPROJ.     
         AUTHOR.         KK.          
         DATA DIVISION.               
         WORKING-STORAGE SECTION.     
          COPY LOG17.                 
          COPY NAV17.                 
          COPY DFHAID.                
             EXEC SQL                 
                INCLUDE SQLCA         
             END-EXEC.                
             EXEC SQL                 
                INCLUDE OUTP          
             END-EXEC.                
          01 USERID    PIC 9(5).      
          01 PASS      PIC 9(5).      
          01 WS-N2     PIC X(2).                                   
          01 TSTAMP    PIC X(26).                                  
          01 TIMEE     PIC X(8).                                   
         PROCEDURE DIVISION.                                       
      **************************************************************
         MAIN-PARA.                                                
      **************************************************************
           IF EIBCALEN = 0                                         
                MOVE "ENTER LOGIN DETAILS" TO MSGO OF LOG17O       
            PERFORM SEND-MAP-PARA                                  
           ELSE                                                    
            PERFORM RECV-MAP-PARA                                  
            PERFORM COMPUTE-PARA                                   
           END-IF                                                  
            PERFORM CLOSE-PARA.                                    
      ********************************************************     
         SEND-MAP-PARA.                                            
      ********************************************************  
           EXEC CICS ASKTIME ABSTIME(TSTAMP) END-EXEC.          
           EXEC CICS                                            
                FORMATTIME                                      
                ABSTIME(TSTAMP)                                 
                DATESEP('/')                                    
                DDMMYYYY(DATEO)                                 
                TIMESEP(':')                                    
                TIME(TIMEO)                                     
           END-EXEC.                                            
           EXEC CICS                                            
             SEND MAP('LOG17') MAPSET('LOG17') FROM (LOG17O)    
           END-EXEC.                                            
             MOVE LOW-VALUES TO LOG17O.                         
           MOVE 'AB' TO WS-N2                                   
             EXEC CICS                                          
                RETURN TRANSID('P517')                          
                COMMAREA(WS-N2)                                  
             END-EXEC.                                           
      ********************************************************   
         SEND-NAV-PARA.                                          
      ********************************************************   
           MOVE LOW-VALUES TO NAV17O.                            
           EXEC CICS SEND CONTROL CURSOR(1) ERASE END-EXEC.      
           EXEC CICS ASKTIME ABSTIME(TSTAMP) END-EXEC.           
           EXEC CICS                                             
                FORMATTIME                                       
                ABSTIME(TSTAMP)                                  
                DATESEP('/')                                     
                DDMMYYYY(DATE1O)                                 
                TIMESEP(':')                                     
                TIME(TIME1O)                                     
           END-EXEC.                                             
           EXEC CICS                                             
             SEND MAP('NAV17') MAPSET('NAV17') FROM (NAV17O)       
           END-EXEC.                                               
           MOVE 'AB' TO WS-N2                                      
             EXEC CICS                                             
                RETURN                                             
             END-EXEC.                                             
      ********************************************************     
         RECV-MAP-PARA.                                            
      ********************************************************     
           EXEC CICS                                               
             RECEIVE MAP('LOG17') MAPSET('LOG17') INTO (LOG17I)    
           END-EXEC.                                               
      ********************************************************     
         COMPUTE-PARA.                                             
      ********************************************************     
           EVALUATE EIBAID                                         
      *       WHEN DFHPF2                                          
      *        PERFORM NEWUSER-PARA                              
              WHEN DFHPF3                                        
               PERFORM CLOSE-PARA                                
              WHEN DFHENTER                                      
               PERFORM LOGIN-PARA                                
              WHEN DFHPF4                                        
               MOVE LOW-VALUES TO LOG17O                         
               PERFORM SEND-MAP-PARA                             
           END-EVALUATE.                                         
      ********************************************************   
         LOGIN-PARA.                                             
      ********************************************************   
             MOVE MEMIDI TO HV-MEM-ID                            
             MOVE PASSI TO HV-PASSWORD                           
            EXEC SQL                                             
             SELECT PASSWORD INTO :HV-PASSWORD                   
                  FROM usermf.MEMTABLE                          
                      WHERE MEM_ID = :HV-MEM-ID AND            
                      PASSWORD = :HV-PASSWORD                  
            END-EXEC.                                          
               IF SQLCODE = ZERO                               
                  MOVE "LOGIN SUCCESSFUL" TO MSGO OF  NAV17O   
               PERFORM SEND-NAV-PARA                           
               ELSE                                            
                  MOVE "LOGIN FAILURE   " TO MSGO OF  LOG17O   
               END-IF                                          
               PERFORM SEND-MAP-PARA.                          
      ******************************************************** 
          RETURN-PARA.                                         
      ******************************************************** 
      *    MOVE 'AB' TO WS-N2                                  
      *      EXEC CICS                                         
      *         RETURN TRANSID('P517')                         
      *         COMMAREA(WS-N2)                                
      *      END-EXEC.                                            
      ********************************************************    
          CLOSE-PARA.                                             
      ********************************************************    
             EXEC CICS                                            
              RETURN                                              
             END-EXEC.      

PARTIAL CODE OF CICS DB2 JCL FOR PRECOMPILATION

//usermfJ JOB ,,NOTIFY=&SYSUID,CLASS=A,MSGLEVEL=(1,1),MSGCLASS=H     
//DD1 SET SRCLIB=usermf.JAN30.KK.LMS2         -->  SOURCE LIBRARY    
//DD2 SET DBRMLIB=usermf.JAN30.COBOLPGM.DBRMLIB    -->  DBRM LIBRARY 
//DD3 SET MEM=LOGPROJ                          -->  MEMBER NAME       
//DD4 SET DCLLIB=usermf.JAN30.COBOLPGM.DCLGEN   -->  DCLGEN LIBRARY  
//DD5 SET COPYLIB=usermf.JAN30.COBOLPGM.BMS   -->  SYMBOLIC MAP      
//DD6 SET LOADLIB=MTPLTRG.CICS.LOADLIB         **  DO NOT CHANGE  **  
//DD7 SET BMSLIB=usermf.JAN30.COBOLPGM.BMS    -->  BMS MAP LIB       
//DD8 SET WSPC=500                                                    
//DD9 SET REG=4096K                                                   
//DDA SET LNKPARM='XREF'                                              
//DDB SET WORK=USER02                                                 
//*              

        

CICS BIND PROGRAM

//usermfB JOB ,,NOTIFY=&SYSUID,CLASS=A,MSGLEVEL=(1,1),MSGCLASS=H     
//JOBLIB   DD  DISP=SHR,                                              
//            DSN=DSN710.SDSNEXIT                                     
//         DD DISP=SHR,                                               
//            DSN=DSN710.SDSNLOAD                                     
//BIND     EXEC PGM=IKJEFT01,DYNAMNBR=20                              
//DBRMLIB  DD DISP=SHR,                                               
//         DSN=usermf.JAN30.COBOLPGM.DBRMLIB       --> DBRM LIBRARY  
//SYSTSPRT DD SYSOUT=*                                                
//SYSPRINT DD SYSOUT=*                                                
//SYSUMUMP DD SYSOUT=*                                                
//SYSOUT   DD SYSOUT=*                                                
//REPORT   DD SYSOUT=*                                                
//SYSTSIN  DD *                                                       
  DSN SYSTEM(DSN2)                                                    
  BIND PLAN(LOGPROJ) MEMBER(LOGPROJ) ISOLATION(CS) ACTION(REP)        
//*   

    

CICS hello world program

       IDENTIFICATION DIVISION.                   
       PROGRAM-ID.    COBCIC17.                   
       AUTHOR.        KK.                         
       DATE-WRITTEN.  11-11-1111.                 
       SECURITY.                                  
       DATA DIVISION.                             
       WORKING-STORAGE SECTION.                   
        01 WS-TEMP PIC X(30).                     
       PROCEDURE DIVISION.                        
            MOVE 'HELLO  WORLD' TO WS-TEMP.
            EXEC CICS                             
             SEND FROM (WS-TEMP)                  
             ERASE                                
            END-EXEC.                             
            EXEC CICS                             
             RETURN                               
           END-EXEC.



cicscob

//usermfC JOB ,,NOTIFY=&SYSUID,CLASS=A,MSGLEVEL=(1,1),MSGCLASS=H    
//JOBPROC  JCLLIB ORDER=IBMUSER.COMPILER.JCLS                        
//CICSCOB  EXEC CICSCOB1,                                            
//         SRCLIB=usermf.JAN30.KK.LMS,    --> SOURCE LIB NAME       
//         MEM=COBCIC17,                     --> MEMBER              
//         COPYLIB=usermf.JAN30.COBOLPGM.BMS,        --> BMS MAP LIB
//         BMSLIB=usermf.JAN30.COBOLPGM.BMS,  --> BMS MAP LIB       
//         LOADLIB=MTPLTRG.CICS.LOADLIB     **  DO NOT CHANGE  **    

CICS program to add two numbers

MAP

MAP17 DFHMSD TYPE=&SYSPARM,MODE=INOUT,LANG=COBOL,                      X
               TIOAPFX=YES                                             
MAP17 DFHMDI SIZE=(24,80),LINE=1,COLUMN=1                              
      DFHMDF POS=(3,33),LENGTH=10,INITIAL='ADDITION',                  X
               ATTRB=(BRT,PROT)                                        
      DFHMDF POS=(6,26),INITIAL='ENTER THE FIRST NO',ATTRB=(BRT,PROT), X
               LENGTH=20                                               
NO1   DFHMDF POS=(6,48),INITIAL='__',ATTRB=(NORM,UNPROT,IC),LENGTH=2   
      DFHMDF POS=(6,51),INITIAL=' ',ATTRB=(NORM,ASKIP),LENGTH=1        
      DFHMDF POS=(7,26),INITIAL='ENTER THE SEC NO',ATTRB=(BRT,PROT),   X
               LENGTH=20                                               
NO2   DFHMDF POS=(7,48),INITIAL='__',ATTRB=(NORM,UNPROT),LENGTH=2      
      DFHMDF POS=(9,26),INITIAL='RESULT IS',ATTRB=(BRT,PROT),LENGTH=10 
RES   DFHMDF POS=(9,38),INITIAL='__',ATTRB=(NORM,PROT),LENGTH=2        
MAP17 DFHMSD TYPE=FINAL                                                
      END    

Cobol code

         IDENTIFICATION DIVISION.                             
         PROGRAM-ID.     ADDMA17.                             
         AUTHOR.         KK.                                  
         DATA DIVISION.                                       
         WORKING-STORAGE SECTION.                             
          COPY MAP17.                                         
          01 WS-STRING PIC A(75).                             
          01 WS-N1     PIC 99.                                
          01 WS-N2     PIC 99.                                
         PROCEDURE DIVISION.                                  
         A0000-MAINAREA.                                      
           MOVE LOW-VALUES TO MAP17O                          
           PERFORM SEND-MAP-PARA                              
           PERFORM RECV-MAP-PARA                              
           PERFORM COMPUTE-PARA                               
           PERFORM SEND-MAP-PARA                              
           PERFORM CLOSE-PARA.                                   
         SEND-MAP-PARA.                                          
           EXEC CICS                                             
             SEND MAP('MAP17') MAPSET('MAP17') FROM (MAP17O)     
           END-EXEC.                                             
         RECV-MAP-PARA.                                          
           EXEC CICS                                             
             RECEIVE MAP('MAP17') MAPSET('MAP17') INTO (MAP17I)  
           END-EXEC.                                             
         COMPUTE-PARA.                                           
             MOVE NO1I TO WS-N1                                  
             MOVE NO2I TO WS-N2                                  
             ADD WS-N1 TO WS-N2                                  
             MOVE WS-N2 TO RESO.                                 
         CLOSE-PARA.                                             
             EXEC CICS                                           
              RETURN                                             
             END-EXEC.        

DB2 programs in COBOL assorted

SELECT

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SAMPLE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
            EXEC SQL
              INCLUDE SQLCA
            END-EXEC.
            EXEC SQL
              INCLUDE EMPLOYEE
            END-EXEC.
       01 S-CODE PIC S9(3) SIGN LEADING SEPARATE.
       PROCEDURE DIVISION.
            EXEC SQL
              SELECT EMPNO, SALARY INTO  :EMPNO, :SALARY
                                 FROM TECH202.EMPLOYEES
                                 WHERE EMPNO = "E001"
            END-EXEC.
            MOVE SQLCODE TO S-CODE.
            DISPLAY 'ENO=' EMPNO.
            DISPLAY 'SALARY=' SALARY.
            DISPLAY S-CODE.
            STOP RUN.

--------------------------------------------------------------------

INSERT

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SAMPLE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
            EXEC SQL
              INCLUDE SQLCA
            END-EXEC.
            EXEC SQL
              INCLUDE EMPLOYEE
            END-EXEC.
       01 S-CODE PIC S9(3) SIGN LEADING SEPARATE.
       01 WS-SAL PIC 9(6).
       PROCEDURE DIVISION.
      *      EXEC SQL
      *        INSERT INTO TECH202.EMPLOYEES VALUES
      *                           ("E002", "RAJA", "D001", 10000)
      *      END-EXEC.
            ACCEPT EMPNO.
            ACCEPT ENAME.
            ACCEPT DEPTNO.
            ACCEPT WS-SAL.
            MOVE  WS-SAL TO SALARY.
            EXEC SQL
              INSERT INTO TECH202.EMPLOYEES VALUES
                                 (:EMPNO, :ENAME, :DEPTNO, :SALARY)
            END-EXEC.
            MOVE SQLCODE TO S-CODE.
            DISPLAY S-CODE.
            STOP RUN.

--------------------------------------------------------------------

UPDATE

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SAMPLE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
            EXEC SQL
              INCLUDE SQLCA
            END-EXEC.
            EXEC SQL
              INCLUDE EMPLOYEE
            END-EXEC.
       01 S-CODE PIC S9(3) SIGN LEADING SEPARATE.
       01 WS-SAL PIC 9(6).
       PROCEDURE DIVISION.
            MOVE  70000 TO SALARY.
            EXEC SQL
              UPDATE TECH202.EMPLOYEES SET SALARY = :SALARY
                              WHERE EMPNO = "E001"
            END-EXEC.
            IF SQLCODE = 0
               DISPLAY 'UPDATE SUCCESSFUL'
            ELSE
               MOVE SQLCODE TO S-CODE
               DISPLAY S-CODE
            END-IF.
            STOP RUN.

--------------------------------------------------------------------

DELETE

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SAMPLE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
            EXEC SQL
              INCLUDE SQLCA
            END-EXEC.
            EXEC SQL
              INCLUDE EMPLOYEE
            END-EXEC.
       01 S-CODE PIC S9(3) SIGN LEADING SEPARATE.
       PROCEDURE DIVISION.
            EXEC SQL
              DELETE FROM TECH202.EMPLOYEES WHERE EMPNO = "E002"
            END-EXEC.
            IF SQLCODE = 0
               DISPLAY 'DELETE SUCESSFUL'
            ELSE
               MOVE SQLCODE TO S-CODE
               DISPLAY S-CODE
            END-IF
            STOP RUN.

DB2 programs assorted

CREATE

--DROPPING THE EXISTING TABLES IF ANY
 --DROP TABLE COURSE;
 --DROP TABLE STUDENT;
 --DROP TABLE ENROLMENT;
 COMMIT;

 --CREATING  COURSE TABLE
   CREATE TABLE COURSE (
   CID INTEGER NOT NULL,
   CNAME VARCHAR(25) CONSTRAINT CNAME_NNULL CHECK(CNAME IS NOT NULL),
   DURATION SMALLINT CONSTRAINT DURATION_CHK CHECK(DURATION <=10),
   WEIGHTAGE SMALLINT,
   CTYPE VARCHAR(5) DEFAULT 'FP',
   CONSTRAINT COURSE_PKEY PRIMARY KEY(CID)
   )IN DBENR.ENR;

  --ALTER TABLE ADDING A CONSTRAINT

   ALTER TABLE COURSE ADD CONSTRAINT
   COURSE_CHK CHECK(CTYPE IN('FP','RBT'));

  --CREATING AN UNIQUE INDEX ON PRIMARY KEY COLUMN

   CREATE UNIQUE INDEX COURSE_IND ON COURSE(CID);

  --ALTERING TABLE COURSE ADDING COLUMN PREREQ


   ALTER TABLE COURSE ADD PREREQ INTEGER;

  --ALTERING TABLE ADDING  A SELF REFERENTIAL FOREIGN KEY

   ALTER TABLE COURSE ADD CONSTRAINT C_FKEY FOREIGN KEY(PREREQ)
   REFERENCES COURSE(CID) ON DELETE CASCADE;
   
 -- CREATING STUDENT TABLE
   CREATE TABLE STUDENT (
   STID INTEGER NOT NULL,
   SNAME VARCHAR(25) CONSTRAINT SNAME_NNUL CHECK(SNAME IS NOT NULL),
   SEMAIL VARCHAR(25),
   BATCH VARCHAR(25) CONSTRAINT BATCH_NNULL CHECK(BATCH IS  NOT NULL),
   CONSTRAINT STUDENT_PKEY PRIMARY KEY(STID)
   )IN DBENR.ENR;



--CREATING AN UNIQUE INDEX ON STUDENT TABLE

   CREATE UNIQUE INDEX STUD_IND ON STUDENT(STID);

 -- CREATING ENROLMENT TABLE
   CREATE TABLE ENROLMENT (
   STID INTEGER NOT NULL,
   CID INTEGER NOT NULL,
   EDATE DATE WITH DEFAULT,
   MARKS SMALLINT CONSTRAINT MARKS_CHK CHECK(MARKS BETWEEN 0 AND 100),
   CONSTRAINT ENRL_FKS FOREIGN KEY(STID) REFERENCES STUDENT(STID)
   ON DELETE CASCADE,
   CONSTRAINT ENRL_FKC FOREIGN KEY(CID) REFERENCES COURSE(CID) ON DELETE
   CASCADE,
   CONSTRAINT ENRL_PKEY PRIMARY KEY(STID,CID)
   ) IN DBENR.ENR;


--CREATING AN UNIQUE INDEX ON ENROLLMENT TABLE FOR THE PRIMARY KEY

   CREATE UNIQUE INDEX ENROL_IND ON ENROLMENT(STID,CID);

---------------------------------------------------------------------

 INSERT

--INSERTING DATA INTO TABLE COURSE

insert into course values(1,'CHSSC',4,2,'FP',NULL);
insert into course values(2,'PF',9,4,'FP',1);
insert into course values(3,'RDBMS',7,3,'FP',2);
insert into course values(4,'Introduction to JAVA',5,NULL,
'RBT',NULL);
insert into course values(5,'Sockets in JAVA',2,NULL,
'RBT',4);
insert into course values(6,'Mobile in JAVA',3,NULL,
'RBT',5);
insert into course values(7,'SDM+UID',2,1,'FP',2);
insert into course values(8,'C/S',1,1,'FP',3);
insert into course values(9,'IWT',2,1,'FP',8);
insert into course values(10,'AOA',3,2,'FP',2);


--INSERTING DATA INTO TABLE STUDENT

insert into student values(1001,'Ramesh','ramesh_cv@gmail.com',
'mys-jan03-lc1');
insert into student values(1002,'Pratap','pratap_pa@gmail.com',
'mys-dec04-lc2');
insert into student values(1003,'Rajesh','rajesh_kk@gmail.com',
'mys-jan03-lc1');
insert into student values(1004,'Rakesh','rakesh_ba@gmail.com',
'mys-dec04-lc2');
insert into student values(1005,'Ganesh','ganesh_sh@gmail.com',
'mys-dec05-lc2');
insert into student values(1006,'Mahesh','mahesh_ob@gmail.com',
'mys-jan05-lc1');
insert into student values(1007,'Suresh','suresh_k@gmail.com',
'mys-dec04-lc2');
insert into student values(1008,'Venkatesh','venkatesh_v@gmail.com',
'mys-jan03-lc1');
insert into student values(1009,'Ajeesh','ajeesh_gp@gmail.com',
'mys-jan04-lc2');
insert into student values(1010,'Shibu','shibu_kumar@gmail.com',
'mys-jan03-lc1');
--INSERTING DATA INTO TABLE ENROLMENT
-- DATES ARE INSERTED IN THE FORMAT 'mm/dd/yyyy'

insert into enrolment values(1001,1,'01/09/2004',88);
insert into enrolment values(1001,2,'02/04/2004',79);
insert into enrolment values(1001,3,'04/06/2004',98);
insert into enrolment values(1001,4,'07/25/2004',99);
insert into enrolment values(1002,8,'06/28/2003',78);
insert into enrolment values(1002,7,'09/24/2003',67);
insert into enrolment values(1002,3,'03/19/2004',56);
insert into enrolment values(1002,4,'12/03/2004',78);
insert into enrolment values(1003,2,'11/02/2003',78);
insert into enrolment values(1003,4,'03/22/2004',80);
insert into enrolment values(1003,1,'09/26/2004',79);
insert into enrolment values(1004,5,'02/23/2003',79);
insert into enrolment values(1004,7,'07/15/2004',78);
insert into enrolment values(1004,2,'08/02/2004',66);
insert into enrolment values(1005,1,'08/29/2004',86);
insert into enrolment values(1005,5,'06/28/2003',69);
insert into enrolment values(1007,8,'08/24/2004',66);
insert into enrolment values(1008,6,'07/23/2003',86);
insert into enrolment values(1009,6,'08/13/2004',69);
 

--------------------------------------------------------------------

QUERY

--printing all the records of student,course and enrollment
select * from student;
select * from course;
select * from enrolment;
--find the count of enrolments for the course id 2.
select count(*) from enrolment group by cid having cid = 2;

--find the maximum marks of any student who are
--enrolled for course with id 1

select max(marks) from enrolment where cid=1;

--find the titles of the courses which does not have
--any enrolled students

select cname from course a where not exists
 (select * from enrolment b where a.cid = b.cid) ;


--print all the student names which start with Ra

select sname from student where sname like 'Ra%' ;

--print all the student names whose email ends with kk


select sname from student where semail like '%kk' ;


--print the course id's from both course and enrolment

select cid from course
union
select cid from enrolment;

--print the student ids who have enrolled for more than
-- two courses


select stid from enrolment group by stid having count(*) >2;

--print the student ids who have enrolled for more than
-- one course using correlated sub query

select distinct stid from enrolment a where cid in
( select cid from enrolment b where a.stid <> b.stid );

--print the course id and number of students
--enrolled for every course
--if no students enrolled the count should be zero


select a.cid, count(b.stid) from
course a left outer join enrolment b
on a.cid = b.cid group by b.cid, a.cid ;

--print all the student names who are enrolled for
--atleast one course

select distinct sname from student a, enrolment b where a.stid = b.stid;

--print the course id and product of duration* weightage for
--all courses which have duration*weightage > 25
--order by duration*weightage

select cid, duration * weightage from course
where (duration*weightage) > 25 order by duration*weightage;


--print the second maximum marks for all students,across all courses

select max(marks) from enrolment where marks
 < (select max(marks) from enrolment) ;

--print the students who have enrolled for the same course
select a.stid , b.stid from enrolment a, enrolment b
where a.cid = b.cid and a.stid < b.stid;

--print the courses which have duration more than 3 days
select cid from course where duration > 3;

--print the enrollments where students got
--marks between 55 and 65
select * from enrolment where marks between 55 and 65 ;

--print all the enrollments where marks can be any one of 66,86,99
select * from enrolment where marks in (66,86,99);

---------------------------------------------------------------------

OTHERS

--DROP DUMMY TABLE IF IT EXISTS ALREADY
--DROP TABLE DUMMY;
--COMMIT;
--CREATING A DUMMY TABLE
CREATE TABLE DUMMY
(
   COL1 VARCHAR(1) NOT NULL,
   CONSTRAINT PK_DUMMY PRIMARY KEY(COL1)
) IN DBENR.ENR;
CREATE UNIQUE INDEX IDX_DUMMY ON DUMMY(COL1);
--INSERT A ROW IN DUMMY TABLE
--DUMMY TABLE IS USED FOR TESTING THE BUILT-IN FUNCTIONS
--IN DB2
INSERT INTO DUMMY VALUES('A');
--GETTING THE CURRENT DATE
SELECT CURRENT DATE FROM DUMMY;

--GETTING THE CURRENT TIME
SELECT CURRENT TIME FROM DUMMY;
--GETTING THE CURRENT TIME STAMP
SELECT CURRENT TIMESTAMP FROM DUMMY;
--GETTING THE CURRENT TIMEZONE
SELECT CURRENT TIMEZONE FROM DUMMY;

--DATA CONVERSION FUNCTIONS
--CONVERTING STRING TO DECIMAL
SELECT DECImal('1245.50',7,2,'.') FROM DUMMY;
--CONVERTING STRING TO INTEGER
SELECT INTEGER('100') FROM DUMMY;
--CONVERTING STRING TO DATE
SELECT DATE('2007-01-28') FROM DUMMY;
--CONVERTING STRING TO DOUBLE
SELECT DOUBLE('123.456') FROM DUMMY;
--conversion from any numeric,date,time to character

SELect char(DATE(CURRENT DATE)) from DUMMY;
select char(TIME(CURRENT TIME)) from DUMMY;
select char(CURRENT DATE) from DUMMY;
select char(TIMESTAMP(CURRENT TIMESTAMP)) from DUMMY;

--removing spaces from a string
select ltrim(CNAME) from COURSE;
select rtrim(CNAME) from COURSE;


--getting DIFFERENCE IN YEARS

SELECT YEAR(CURRENT DATE)-YEAR(DATE('1997-02-02')) FROM DUMMY;

--GETTING DIFFERENCE IN DAYS
SELECT DAYS(CURRENT DATE) - DAYS(DATE('1997-01-01')) FROM DUMMY;

--GETTING THE CURRENT USER

SELECT USER FROM DUMMY;


--PRINTING YEAR,DAY,MONTH OF A DATE

select YEAR(current date) FROM DUMMY;
select DAY(current date) from DUMMY;
SELECT MONTH(CURRENT DATE) FROM DUMMY;

--printing hour,minute and seconds
select HOUR(CURRENT TIME) from DUMMY;
select MINUTE(CURRENT TIME) from DUMMY;
select SECOND(CURRENT TIME) from DUMMY;



--PRINTING A VALUE FOR A NULL VALUE

select CNAME,VALUE(PREREQ,0) FROM COURSE;

--querying the catalog tables

SELECT * FROM SYSIBM.SYSPLAN;

COBOL Program to edit a KSDS using PS entries and reflect the result in PS file

KSDS BEFORE EDITING

10012   DEFAS   ACCT    00008   111111111    
10013   GHIQW   FINS    10102   342-51235    
10015   ASFER   KKKKK   11111   122222222    
10019   DSASA   EREWT   31564   124331354    
10020   SAFDF   SAFAS   35256   12-243253    
10021   QQQQQ   ZZZZZ   25232   666666666    

PS FILE BEFORE EXECUTING

10011   ABCSA   SALES   50000   004-12345   I       
10012   DEFAS   ACCT    80000   123-98754   U       
10013   GHIQW   FINS    10102   342-51235   D       
10014   WE2EW   PROG    20030   192-24213   I       
10015   ASFER   DEVOL   24322   1244-2421   U       
10016   SFART   TEST    15326   213215423   U       
10017   SDNGF   DJDHA   14223   126723463   D       
10018   QWESD   AZXCV   15005   12-432153   I       
10019   DSASA   EREWT   31564   124331354   I       
10020   SAFDF   SAFAS   35256   12-243253   D       
10021   AZDFZ   ZAFSA   25232   214215324   U       
10022   ZADFA   LPNHG   36437   079684456   I       
10023   MNGGB   GFGUY   35642   186545364   U       
10024   FEGFR   MNBVC   86543   132434343   I       
10025   IOHGY   HJGDF   90865   537375879   D       
10026   VCGHV   VNCFD   64859   323546644   I   

KSDS after executing

10011...ABCSA...SALES...50000...004-12345...............
10012...DEFAS...ACCT ...80000...123-98754...............
10014...WE2EW...PROG ...20030...192-24213...............
10015...ASFER...DEVOL...24322...1244-2421...............
10018...QWESD...AZXCV...15005...12-432153...............
10019   dsasa   erewt   31564   124331354              
10021...AZDFZ...ZAFSA...25232...214215324...............
10022...ZADFA...LPNHG...36437...079684456...............
10024...FEGFR...MNBVC...86543...132434343...............
10026...VCGHV...VNCFD...64859...323546644...............

PS after executing

10011   ABCSA   SALES   50000   004-12345   F   SUCCESS            
10012   DEFAS   ACCT    80000   123-98754   F   RECORD UPDATED     
10013   GHIQW   FINS    10102   342-51235   F   RECORD DELETED     
10014   WE2EW   PROG    20030   192-24213   F   SUCCESS            
10015   ASFER   DEVOL   24322   1244-2421   F   RECORD UPDATED     
10016   SFART   TEST    15326   213215423   P   RECORD NOT FOUND   
10017   SDNGF   DJDHA   14223   126723463   P   RECORD NOT FOUND   
10018   QWESD   AZXCV   15005   12-432153   F   SUCCESS            
10019   DSASA   EREWT   31564   124331354   P   REC ALREADY PRESENT
10020   SAFDF   SAFAS   35256   12-243253   F   RECORD DELETED     
10021   AZDFZ   ZAFSA   25232   214215324   F   RECORD UPDATED     
10022   ZADFA   LPNHG   36437   079684456   F   SUCCESS            
10023   MNGGB   GFGUY   35642   186545364   P   RECORD NOT FOUND   
10024   FEGFR   MNBVC   86543   132434343   F   SUCCESS            
10025   IOHGY   HJGDF   90865   537375879   P   RECORD NOT FOUND   
10026   VCGHV   VNCFD   64859   323546644   F   SUCCESS 

i - insert

u - update

d - delete

f - on success of operation

p - on failure of operation

f -

p - processes

       IDENTIFICATION DIVISION.              
       PROGRAM-ID. PROJ.                     
       AUTHOR. KK.                           
       ENVIRONMENT DIVISION.                 
       INPUT-OUTPUT SECTION.                 
       FILE-CONTROL.                         
             SELECT EFILE1 ASSIGN            
                 TO DD1                      
             ORGANIZATION IS SEQUENTIAL      
             ACCESS MODE IS SEQUENTIAL       
             FILE STATUS IS FS1.             
             SELECT EFILE2 ASSIGN            
                 TO DD2                      
             ORGANIZATION IS INDEXED         
             ACCESS MODE IS RANDOM             
             RECORD KEY IS EMPID2              
             FILE STATUS IS FS2.               
       DATA DIVISION.                          
       FILE SECTION.                           
        FD EFILE1.                             
        01 EREC1.                                       
           02 EMPID PIC X(5).                  
           02 FILLER PIC X(3).                 
           02 ENAME PIC X(5).                  
           02 FILLER PIC X(3).                 
           02 DEPT PIC X(5).                   
           02 FILLER PIC X(3).                 
           02 SALARY PIC X(5).           
           02 FILLER PIC X(3).           
           02 PNO PIC X(9).              
           02 FILLER PIC X(3).           
           02 STATUS1 PIC X.             
           02 FILLER PIC X(3).           
           02 REMARKS PIC X(20).         
           02 FILLER PIC X(12).          
        FD EFILE2.                       
        01 EREC2.                            
           02 EMPID2 PIC X(5).           
           02 FILLER PIC X(3).           
           02 ENAME2 PIC X(5).           
           02 FILLER PIC X(3).     
           02 DEPT2 PIC X(5).      
           02 FILLER PIC X(3).     
           02 SALARY2 PIC X(5).    
           02 FILLER PIC X(3).     
           02 PNO2 PIC X(9).       
           02 FILLER PIC X(39).    
       WORKING-STORAGE SECTION.    
        01 FS1 PIC 9(2).           
        01 FS2 PIC 9(2).           
        01 EOF PIC XX.             
        01 EREC3.                  
           02 EMPID3 PIC X(5).     
           02 FILLER PIC X(3).     
           02 ENAME3 PIC X(5).     
           02 FILLER PIC X(3).     
           02 DEPT3 PIC X(5).      
           02 FILLER PIC X(3).                  
           02 SALARY3 PIC X(5).                 
           02 FILLER PIC X(3).                  
           02 PNO3 PIC X(9).                    
           02 FILLER PIC X(39).                 
       PROCEDURE DIVISION.                      
             OPEN I-O EFILE1                    
             OPEN I-O EFILE2                    
             PERFORM 0001-FILE-IN UNTIL FS1 = 10
             CLOSE EFILE1                       
             CLOSE EFILE2                       
             STOP RUN.                          
       0001-FILE-IN.                            
             DISPLAY "FILE OPEN" FS1            
             READ EFILE1                        
                 AT END                         
                     DISPLAY 'READ SUCCESS'     
                 NOT AT END                      
                  DISPLAY "FILE 1"               
                  MOVE EMPID TO EMPID2           
                  MOVE ENAME TO ENAME2           
                  MOVE DEPT TO DEPT2             
                  MOVE SALARY TO SALARY2         
                  MOVE PNO TO PNO2               
                  DISPLAY PNO                    
                  DISPLAY STATUS1                
                  DISPLAY EMPID                  
                     PERFORM PARA1               
                     REWRITE EREC1               
             END-READ                            
       PARA1.                                    
               DISPLAY 'ENTERED PARA1'           
               IF STATUS1 = 'I'                  
               DISPLAY 'ENTERED STATUS = I'      
                  WRITE EREC2                               
                    INVALID KEY                             
                      DISPLAY 'INSERT UNSUCESSFULL'         
                       MOVE "REC ALREADY PRESENT" TO REMARKS
                       MOVE 'P' TO STATUS1                  
                      NOT INVALID KEY                       
                       MOVE "SUCCESS" TO REMARKS            
                       MOVE 'F' TO STATUS1                  
                       DISPLAY 'INSERT SUCCESSFULL'         
                  ELSE IF STATUS1 = 'U'                     
                     REWRITE EREC2                          
                      INVALID KEY                           
                       DISPLAY 'UPDATE UNSUCESSFULL'        
                       MOVE "RECORD NOT FOUND" TO REMARKS   
                       MOVE 'P' TO STATUS1                  
                      NOT INVALID KEY                       
                       MOVE "RECORD UPDATED" TO REMARKS     
                       MOVE 'F' TO STATUS1                
                       DISPLAY 'UPDATE SUCCESSFULL'       
                  ELSE IF STATUS1 = 'D'                   
                     DELETE EFILE2                        
                      INVALID KEY                         
                       DISPLAY 'DELETE UNSUCESSFULL'      
                       MOVE "RECORD NOT FOUND" TO REMARKS 
                       MOVE 'P' TO STATUS1                
                      NOT INVALID KEY                     
                       MOVE "RECORD DELETED" TO REMARKS   
                       MOVE 'F' TO STATUS1                
                       DISPLAY 'DELETE SUCCESSFULL'       
                  END-IF                                  
                  END-IF                                  
                  END-IF.