***************************************************************
*****************create ksds************************************
***************************************************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP01
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DEFINE CLUSTER (NAME(TECH301.KSDS.BASE) -
CISZ(4096) -
TRACKS(1,2) -
FREESPACE(10 20) -
KEYS(5 0) -
RECORDSIZE(80 80) -
INDEXED )
/*
//***********load the vsam file from ps file**************************
//STEP02 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
REPRO IDS(TECH301.VSAM.INFILE) -
ODS(TECH301.KSDS.BASE)
/*
//
***************************************************************
*****************create aix************************************
***************************************************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP01
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DEFINE AIX(NAME(TECH301.KSDS.AIX) -
RELATE(TECH301.KSDS.BASE) -
KEYS(10,5) -
CISZ(4096) -
TRACKS(2,3) -
FREESPACE(10,20) -
UPGRADE )
/*
***********load the vsam file from ps file**************************
//STEP02 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
BLDINDEX IDS(TECH301.KSDS.BASE) -
ODS(TECH301.KSDS.AIX)
/*
***********load the path file from aix file**************************
//STEP03 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DEFINE PATH(NAME(TECH301.KSDS.PATH) -
PATHENTRY (TECH301.KSDS.AIX) -
UPDATE)
/*
*********** print the data to spool *******************************
//STEP04 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
PRINT IDS(TECH301.KSDS.BASE) -
DUMP
/*
***************************************************************
*****************create esds************************************
***************************************************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP01
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DEFINE CLUSTER (NAME(TECH301.ESDS.BASE) -
CISZ(4096) -
TRACKS(1,2) -
RECORDSIZE(80 80) -
NONINDEXED )
/*
***********load the vsam file from ps file**************************
//STEP02 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
REPRO IDS(TECH301.VSAM.INFILE) -
ODS(TECH301.ESDS.BASE)
/*
***************************************************************
*****************create RRDS************************************
***************************************************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP01
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DEFINE CLUSTER (NAME(TECH301.RRDS.BASE) -
CISZ(4096) -
TRACKS(1,2) -
RECORDSIZE(80 80) -
NUMBERED )
/*
***********load the vsam file from ps file**************************
//STEP02 EXEC PGM=IDCAMS *
//SYSPRINT DD SYSOUT=* *
//SYSOUT DD SYSOUT=* *
//SYSIN DD * *
REPRO IDS(TECH301.VSAM.INFILE) - *
ODS(TECH301.RRDS.BASE) *
/* *
***********delete the vsam files *********************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID *
//STEP01 EXEC PGM=IDCAMS *
//SYSPRINT DD SYSOUT=* *
//SYSOUT DD SYSOUT=* *
//SYSIN DD * *
DELETE TECH301.VSAM11.PS *
/* *
************************************************************* *
Symbolic parameter
*******************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP03.STEP02
//PROC1 PROC
//STEP01 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
//STEP02 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME1.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
// PEND
//STEP03 EXEC PROC=PROC1
//*STEP01.DD1 DD DSN=TECH301.MUGHUN77.PS
******************************************************************
Sort program using include
*************************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP03.STEP02
//PROC1 PROC
//STEP01 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
//STEP02 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME1.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
// PEND
//STEP03 EXEC PROC=PROC1
//*STEP01.DD1 DD DSN=TECH301.MUGHUN77.PS
*****************************************************************
Restart program
*********************************
//TECH301D JOB ,,CLASS=A,NOTIFY=&SYSUID,RESTART=STEP03.STEP02
//PROC1 PROC
//STEP01 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
//STEP02 EXEC PGM=IEFBR14
//DD1 DD DSN=TECH301.NAME1.PS,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(10,20),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
// PEND
//STEP03 EXEC PROC=PROC1
//*STEP01.DD1 DD DSN=TECH301.MUGHUN77.PS
********************************************************************
db2 table
SELECT * FROM CESTAB01
--OP TABLE CESTABO1;
--CREATE TABLE CESTAB01(APP_NO INT PRIMARY KEY NOT NULL,
--CUST_FIRST_NAME CHAR(20),
--CUST_SECOND_NAME CHAR(20),
--CUST_GENDER CHAR(6),
--CUST_ADD1 CHAR(20),
--CUST_ADD2 CHAR(20),
--CUST_ADD3 CHAR(20),
--CUST_STATE CHAR(10),
--CUST_P_CODE INT,
--CUST_STATUS CHAR(3),
--PROD_NUM CHAR(3),
--APP_DATE DATE,
--REFERAL_NUM CHAR(2),
--CREDIT_LIMIT DECIMAL(8,2),
--APP_STATUS CHAR(2),
--AGENT_CODE CHAR(4),
--CUST_SS_NUM CHAR(12),
--PREV_APP_STAT CHAR(2));
--CREATE UNIQUE INDEX CESTAB01 ON CESTAB01(APP_NO);
--DELETE FROM CREDIT_CARD;
--SELECT * FROM CREDIT_CARD
cobol file
IDENTIFICATION DIVISION.
PROGRAM-ID. INSERT1.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS WS-STATUS.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 INAPP-NO PIC 9(8).
02 INCUST-FIRST-NAME PIC A(20).
02 INCUST-SECOND-NAME PIC A(20).
02 INCUST-GENDER PIC A(10).
02 INCUST-ADD1 PIC A(20).
02 INCUST-ADD2 PIC A(20).
02 INCUST-ADD3 PIC A(20).
02 INCUST-STATE PIC A(10).
02 INCUST-P-CODE PIC 9(6).
02 INCUST-STATUS PIC A(3).
02 INPROD-NUM PIC X(3).
02 INAPP-DATE PIC X(10).
02 INREFERAL-NUM PIC X(2).
02 INCREDIT-LIMIT PIC 9(6)V99.
02 INAPP-STATUS PIC X(2).
02 INAGENT-CODE PIC X(4).
02 INCUST-SS-NUM PIC X(12).
02 INPREV-APP-STAT PIC X(2).
02 PIC X(20).
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE DCGEN
END-EXEC.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
77 L PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM INSERT-PARA UNTIL WS-STATUS = 10.
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY "SQLCODE:" WS-SQLCODE.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY 'FILE STATUS' WS-STATUS.
CLOSE-PARA.
CLOSE INFILE.
INSERT-PARA.
READ INFILE.
DISPLAY "APP-NO=" INAPP-NO.
IF WS-STATUS = 00
MOVE INAPP-NO TO APP-NO
MOVE INCUST-FIRST-NAME TO CUST-FIRST-NAME
MOVE INCUST-SECOND-NAME TO CUST-SECOND-NAME
MOVE INCUST-GENDER TO CUST-GENDER
MOVE INCUST-ADD1 TO CUST-ADD1
MOVE INCUST-ADD2 TO CUST-ADD2
MOVE INCUST-ADD3 TO CUST-ADD3
MOVE INCUST-STATE TO CUST-STATE
dclgen program
******************************************************************
* DCLGEN TABLE(CESTAB01) *
* LIBRARY(TECH301.DB2.COPYLIB.PDS(DCGEN)) *
* ACTION(REPLACE) *
* LANGUAGE(COBOL) *
* QUOTE *
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS *
******************************************************************
EXEC SQL DECLARE CESTAB01 TABLE
( APP_NO INTEGER NOT NULL,
CUST_FIRST_NAME CHAR(20),
CUST_SECOND_NAME CHAR(20),
CUST_GENDER CHAR(6),
CUST_ADD1 CHAR(20),
CUST_ADD2 CHAR(20),
CUST_ADD3 CHAR(20),
CUST_STATE CHAR(10),
CUST_P_CODE INTEGER,
CUST_STATUS CHAR(3),
PROD_NUM CHAR(3),
APP_DATE DATE,
REFERAL_NUM CHAR(2),
CREDIT_LIMIT DECIMAL(8, 2),
APP_STATUS CHAR(2),
AGENT_CODE CHAR(4),
CUST_SS_NUM CHAR(12),
PREV_APP_STAT CHAR(2)
) END-EXEC.
******************************************************************
* COBOL DECLARATION FOR TABLE CESTAB01 *
******************************************************************
01 DCLCESTAB01.
10 APP-NO PIC S9(9) USAGE COMP.
10 CUST-FIRST-NAME PIC X(20).
10 CUST-SECOND-NAME PIC X(20).
10 CUST-GENDER PIC X(6).
10 CUST-ADD1 PIC X(20).
10 CUST-ADD2 PIC X(20).
10 CUST-ADD3 PIC X(20).
10 CUST-STATE PIC X(10).
10 CUST-P-CODE PIC S9(9) USAGE COMP.
10 CUST-STATUS PIC X(3).
10 PROD-NUM PIC X(3).
10 APP-DATE PIC X(10).
10 REFERAL-NUM PIC X(2).
10 CREDIT-LIMIT PIC S9(6)V9(2) USAGE COMP-3.
10 APP-STATUS PIC X(2).
10 AGENT-CODE PIC X(4).
10 CUST-SS-NUM PIC X(12).
10 PREV-APP-STAT PIC X(2).
******************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 18 *
******************************************************************
DB2 COBOL
PRECOMPILER PROGRAM
***************************** Top of Data ************************
//TECH301A JOB CLASS=C,MSGCLASS=X,
// NOTIFY=&SYSUID,REGION=0M
// JCLLIB ORDER=TECH301.DB2PGMS.PDS
//STEPDB EXEC DB2PROC,MEM=STUDENT
//PC.DBRMLIB DD DSN=TECH301.DB2.DBRMLIB.PDS(STUDENT),DISP=SHR
//PC.SYSIN DD DSN=TECH301.DB2PGMS.PDS(STUDENT),DISP=SHR
//PC.SYSLIB DD DSN=TECH301.DB2.COPYLIB.PDS,DISP=SHR
//LKED.SYSLMOD DD DSN=TECH301.DB2.LOADLIB.PDS(STUDENT),DISP=SHR
//SYSPRINT DD SYSOUT=*
//
**********************************************************************
cobol program
*************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. STD.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE DB1A
END-EXEC.
77 WS-SQLCODE PIC -999.
PROCEDURE DIVISION.
EXEC SQL
SELECT ROLLNO,STD_NAME,STD_DEPT,STD_FEES,STD_SEX,STD_SEM
INTO :ROLLNO,
:STD-NAME,
:STD-DEPT,
:STD-FEES,
:STD-SEX,
:STD-SEM
FROM STUDENT_DETAIL WHERE STD-NAME="DEEPAN"
END-EXEC.
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY WS-SQLCODE.
DISPLAY ROLLNO.
DISPLAY STD-NAME.
STOP RUN.
********************** Bottom of Data *****************
bind run program
****************************************************
//TECH301A JOB 'IBMMFS','MAC',CLASS=C,MSGCLASS=X,
// NOTIFY=&SYSUID,REGION=0M
//DB2TSO EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=DSN810.SDSNLOAD,DISP=SHR
// DD DSN=DSN810.SDSNEXIT,DISP=SHR
// DD DSN=DSN810.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=A
//SYSPRINT DD SYSOUT=A
//SYSUDUMP DD SYSOUT=A
//SYSIN DD *
/*
//SYSTSIN DD *
DSN SYSTEM(DB8G)
BIND PLAN(TECH301) MEM(STUDENT) -
LIBRARY('TECH301.DB2.DBRMLIB.PDS') -
RELEASE(COMMIT) ISOLATION(CS) -
VALIDATE(BIND) -
ACTION(REPLACE) OWNER(TECH301)
RUN PROGRAM(STUDENT) PLAN(TECH301) -
LIB('TECH301.DB2.LOADLIB.PDS')
END
/*
//
**************************** Bottom of Data *********
Insert
insert program
IDENTIFICATION DIVISION.
PROGRAM-ID. INSERT1.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS IS WS-STATUS.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 app_no PIC 9(20).
02 cust_NAME PIC X(20).
02 cust_add_1 PIC X(20).
02 cust_add_2 PIC x(5).
02 SEX PIC X(5).
02 SEM PIC 9(1).
02 F PIC X(49).
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE DB1A
END-EXEC.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM INSERT-PARA UNTIL WS-STATUS = 10.
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY "SQLCODE:" WS-SQLCODE.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY 'FILE STATUS' WS-STATUS.
CLOSE-PARA.
CLOSE INFILE.
INSERT-PARA.
READ INFILE.
DISPLAY "ROLL NO=" ROLLNO.
IF WS-STATUS = 00
MOVE ROLL TO ROLLNO
MOVE NAME TO STD-NAME
MOVE DEPT TO STD-DEPT
MOVE FEES TO STD-FEES
MOVE SEX TO STD-SEX
MOVE SEM TO STD-SEM
EXEC SQL
INSERT INTO STUDENT_DETAIL
(ROLLNO,
STD_NAME,
STD_DEPT,
STD_FEES,
STD_SEX,
STD_SEM)
VALUES(:ROLLNO,
:STD-NAME,
:STD-DEPT,
:STD-FEES,
:STD-SEX,
:STD-SEM)
END-EXEC
END-IF.
EVALUATE SQLCODE
WHEN 0
WHEN 0
DISPLAY 'ROLLNO :' ROLLNO
DISPLAY 'STD-NAME :' STD-NAME
DISPLAY 'STD-DEPT :' STD-DEPT
DISPLAY 'STD-FEES :' STD-FEES
DISPLAY 'STD-SEX :' STD-SEX
DISPLAY 'STD-SEM :' STD-SEM
WHEN OTHER
MOVE SQLCODE TO WS-SQLCODE
DISPLAY 'SQLCODE :' WS-SQLCODE
END-EVALUATE.
DB2
DB2 programs..
************************
basic concept..Create table..
************************
--CREATE TABLE STUDENT_DETAILS(ROLLNO SMALLINT,
-- STD_NAME CHAR(15),
-- STD_DEPT CHAR(5),
-- STD_FEES INT,
-- STD_SEX CHAR(5),
-- STD_SEM CHAR(2))
--INSERT INTO STUDENT_DETAILS VALUES(10001,'MUGUNTHAN','MCA',22500,
-- 'MALE','I');
--INSERT INTO STUDENT_DETAILS VALUES(10101,'DEEPAN','ECE',25000,
-- 'MALE','II');
--INSERT INTO STUDENT_DETAILS VALUES(10002,'PRAVEEN','MCA',22500,
-- 'MALE','II');
--INSERT INTO STUDENT_DETAILS VALUES(10103,'SURESH','MCA',22500,
-- 'MALE','II');
SELECT * FROM STUDENT_DETAILS;
--UPDATE STUDENT_DETAILS SET STD_FEES=20000 WHERE ROLLNO=10101;
--DROP TABLE STUDENT_DETAILS;
--DELETE FROM STUDENT_DETAILS WHERE STD_NAME = 'MUGUNTHAN'
******************************************************************************
setting primary key.
**********************************************************************************
CREATE TABLE STUDENT_DETAILS (ROLLNO SMALLINT NOT NULL,
STD_NAME CHAR(15),
STD_DEPT CHAR(5),
STD_FEES INT,
STD_SEX CHAR(5),
STD_SEM CHAR(2),
PRIMARY KEY (ROLLNO));
--CREATE UNIQUE INDEX ST ON STUDENT_DETAILS(ROLLNO);
***************************************************************************************
1. SELECT STD_NAME FROM STUDENT_DETAILS
2. SELECT DISTINCT STD_DEPT FROM STUDENT_DETAILS **(diffrent department from student_table is selected).
3. SELECT * FROM STUDENT_DETAILS WHERE STD_DEPT = 'MCA'
4. SELECT * FROM STUDENT_DETAILS WHERE STD_FEES > 22400 AND STD_DEPT = 'MCA'
5. SELECT * FROM STUDENT_DETAILS WHERE STD_NAME LIKE 'M%' ** (It will print all the name starting from M)
6. SELECT STD_NAME FROM STUDENT_DETAILS WHERE STD_FEES IN ( 22500,30000,26000 )
7. SELECT EMPNO, EMPNAME FROM EMPOYEE WHERE SALARY BETWEEN 15000 AND 40000
8. SELECT STD_NAME FROM STUDENT_DETAILS WHERE ROLLNO IS NOT NULL
9.SELECT SUM(STD_FEES) FROM STUDENT_DETAILS ** (to sum all the values)
10. SELECT MAX(STD_FEES) FROM STUDENT_DETAILS **(max fees)
11.SELECT MIN(STD_FEES) FROM STUDENT_DETAILS **(TO FIND THE MINIMUM)
11.SELECT AVG(STD_FEES) FROM STUDENT_DETAILS **(TO FIND THE AVERAGE)
13. SELECT MAX(STD_FEES) FROM STUDENT_DETAIL WHERE STD_FEES <>
( SELECT MAX(STD_FEES) FROM STUDENT_DETAIL ); ** (to select the second maximum value)
CES PROJECT SOURCE CODE
*******************************************************************************************************
TO IDENTIFY THE BLANK SPACE.
*********************** Top of Data *********************************************************************
ID DIVISION.
PROGRAM-ID. CESCOB01.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS WS-STATUS.
SELECT REJECT ASSIGN TO OUTDD1
FILE STATUS WS-STATUS1.
SELECT OUTFILE ASSIGN TO OUTDD2
FILE STATUS WS-STATUS2.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 IN-APP-NUM PIC 9(8).
02 IN-CUST-NAME1 PIC X(20).
02 IN-CUST-NAME2 PIC X(20).
02 IN-CUST-GENDER PIC X(10).
02 IN-CUST-ADD1 PIC X(20).
02 IN-CUST-ADD2 PIC X(20).
02 IN-CUST-ADD3 PIC X(20).
02 IN-CUST-STATE PIC X(10).
02 IN-CUST-PIN PIC 9(6).
02 IN-CUST-STATUS PIC X(3).
02 IN-PROD-NUM PIC 9(3).
02 IN-APP-DATE PIC X(10).
02 IN-REFERAL-NUM PIC 9(2).
02 IN-CREDIT-LIMIT PIC 9(6)V9(2).
02 IN-APP-STATUS PIC X(2).
02 IN-AGENT-CODE PIC 9(4).
02 IN-CUST-SS-NUM PIC X(12).
02 IN-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD REJECT.
01 OUT-REC1.
02 R-APP-NUM PIC 9(8).
02 R-CUST-NAME1 PIC X(20).
02 R-CUST-NAME2 PIC X(20).
02 R-CUST-GENDER PIC X(10).
02 R-CUST-ADD1 PIC X(20).
02 R-CUST-ADD2 PIC X(20).
02 R-CUST-ADD3 PIC X(20).
02 R-CUST-STATE PIC X(10).
02 R-CUST-PIN PIC 9(6).
02 R-CUST-STATUS PIC X(3).
02 R-PROD-NUM PIC 9(3).
02 R-APP-DATE PIC X(10).
02 R-REFERAL-NUM PIC 9(2).
02 R-CREDIT-LIMIT PIC 9(6)V9(2).
02 R-APP-STATUS PIC X(2).
02 R-AGENT-CODE PIC 9(4).
02 R-CUST-SS-NUM PIC X(12).
02 R-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD OUTFILE.
01 OUT-REC2.
02 OUT-APP-NUM PIC 9(8).
02 OUT-CUST-NAME1 PIC X(20).
02 OUT-CUST-NAME2 PIC X(20).
02 OUT-CUST-GENDER PIC X(10).
02 OUT-CUST-ADD1 PIC X(20).
02 OUT-CUST-ADD2 PIC X(20).
02 OUT-CUST-ADD3 PIC X(20).
02 OUT-CUST-STATE PIC X(10).
02 OUT-CUST-PIN PIC 9(6).
02 OUT-CUST-STATUS PIC X(3).
02 OUT-PROD-NUM PIC 9(3).
02 OUT-APP-DATE PIC X(10).
02 OUT-REFERAL-NUM PIC 9(2).
02 OUT-CREDIT-LIMIT PIC 9(6)V9(2).
02 OUT-APP-STATUS PIC X(2).
02 OUT-AGENT-CODE PIC 9(4).
02 OUT-CUST-SS-NUM PIC X(12).
02 OUT-PRE-APP-STAT PIC X(2).
02 PIC X(20).
WORKING-STORAGE SECTION.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
77 WS-STATUS1 PIC 99.
77 WS-STATUS2 PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM READ-PARA UNTIL WS-STATUS = 10.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY ' INFILE STATUS IS : ' WS-STATUS.
OPEN OUTPUT REJECT.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
OPEN OUTPUT OUTFILE.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
CLOSE-PARA.
CLOSE INFILE REJECT OUTFILE.
DISPLAY ' FILE STATUS IS : ' WS-STATUS.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
READ-PARA.
READ INFILE.
DISPLAY ' APPLICATION-NUMBER : ' IN-APP-NUM.
DISPLAY ' CREDIT LIMIT :' IN-CREDIT-LIMIT.
IF WS-STATUS = 00
IF IN-APP-NUM = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-NAME1 = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-NAME2 = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-GENDER = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-ADD1 = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-ADD2 = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-ADD3 = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-STATE = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-PIN = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-STATUS = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-PROD-NUM = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-APP-DATE = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-REFERAL-NUM = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CREDIT-LIMIT = 00000000
DISPLAY IN-CREDIT-LIMIT
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-APP-STATUS = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-AGENT-CODE = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-CUST-SS-NUM = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE IF IN-PRE-APP-STAT = SPACES
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
ELSE
MOVE IN-REC TO OUT-REC2
WRITE OUT-REC2
END-IF.
*****************************************************************************************************
ADDRESS VALIDATION USING CUSTOMER PIN CODE
******************************************************************************************************
ID DIVISION.
PROGRAM-ID. CESCOB02.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS WS-STATUS.
SELECT REJECT ASSIGN TO OUTDD1
FILE STATUS WS-STATUS1.
SELECT OUTFILE ASSIGN TO OUTDD2
FILE STATUS WS-STATUS2.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 IN-APP-NUM PIC 9(8).
02 IN-CUST-NAME1 PIC X(20).
02 IN-CUST-NAME2 PIC X(20).
02 IN-CUST-GENDER PIC X(10).
02 IN-CUST-ADD1 PIC X(20).
02 IN-CUST-ADD2 PIC X(20).
02 IN-CUST-ADD3 PIC X(20).
02 IN-CUST-STATE PIC X(10).
02 IN-CUST-PIN PIC 9(6).
02 IN-CUST-STATUS PIC X(3).
02 IN-PROD-NUM PIC 9(3).
02 IN-APP-DATE PIC X(10).
02 IN-REFERAL-NUM PIC 9(2).
02 IN-CREDIT-LIMIT PIC 9(6)V9(2).
02 IN-APP-STATUS PIC X(2).
02 IN-AGENT-CODE PIC 9(4).
02 IN-CUST-SS-NUM PIC X(12).
02 IN-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD REJECT.
01 OUT-REC1.
02 R-APP-NUM PIC 9(8).
02 R-CUST-NAME1 PIC X(20).
02 R-CUST-NAME2 PIC X(20).
02 R-CUST-GENDER PIC X(10).
02 R-CUST-ADD1 PIC X(20).
02 R-CUST-ADD2 PIC X(20).
02 R-CUST-ADD3 PIC X(20).
02 R-CUST-STATE PIC X(10).
02 R-CUST-PIN PIC 9(6).
02 R-CUST-STATUS PIC X(3).
02 R-PROD-NUM PIC 9(3).
02 R-APP-DATE PIC X(10).
02 R-REFERAL-NUM PIC 9(2).
02 R-CREDIT-LIMIT PIC 9(6)V9(2).
02 R-APP-STATUS PIC X(2).
02 R-AGENT-CODE PIC 9(4).
02 R-CUST-SS-NUM PIC X(12).
02 R-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD OUTFILE.
01 OUT-REC2.
02 OUT-APP-NUM PIC 9(8).
02 OUT-CUST-NAME1 PIC X(20).
02 OUT-CUST-NAME2 PIC X(20).
02 OUT-CUST-GENDER PIC X(10).
02 OUT-CUST-ADD1 PIC X(20).
02 OUT-CUST-ADD2 PIC X(20).
02 OUT-CUST-ADD3 PIC X(20).
02 OUT-CUST-STATE PIC X(10).
02 OUT-CUST-PIN PIC 9(6).
02 OUT-CUST-STATUS PIC X(3).
02 OUT-PROD-NUM PIC 9(3).
02 OUT-APP-DATE PIC X(10).
02 OUT-REFERAL-NUM PIC 9(2).
02 OUT-CREDIT-LIMIT PIC 9(6)V9(2).
02 OUT-APP-STATUS PIC X(2).
02 OUT-AGENT-CODE PIC 9(4).
02 OUT-CUST-SS-NUM PIC X(12).
02 OUT-PRE-APP-STAT PIC X(2).
02 PIC X(20).
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE CESTAB02
END-EXEC.
EXEC SQL
DECLARE CESTAB02 CURSOR WITH HOLD FOR
SELECT CUST_P_CODE,
CUST_ADD_3,
CUST_STATE
FROM CESTAB02
END-EXEC.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
77 WS-STATUS1 PIC 99.
77 WS-STATUS2 PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM READ-PARA UNTIL WS-STATUS = 10.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY ' INFILE STATUS IS : ' WS-STATUS.
OPEN OUTPUT REJECT.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
OPEN OUTPUT OUTFILE.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
EXEC SQL
OPEN CESTAB02
END-EXEC.
CLOSE-PARA.
CLOSE INFILE REJECT OUTFILE.
DISPLAY ' FILE STATUS IS : ' WS-STATUS.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
EXEC SQL
CLOSE CESTAB02
END-EXEC.
READ-PARA.
READ INFILE.
EXEC SQL
FETCH CESTAB02
INTO :CUST-P-CODE
END-EXEC.
IF WS-STATUS = 00
IF CUST-P-CODE = IN-CUST-PIN
MOVE IN-REC TO OUT-REC2
WRITE OUT-REC2
ELSE
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
END-IF
END-IF.
**************************************************************************************************************
CONDITION TO CHECK SS-NUMBER OF CUSTOMER
**************************************************************************************************************
ID DIVISION.
PROGRAM-ID. CESCOB03.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS WS-STATUS.
SELECT REJECT ASSIGN TO OUTDD1
FILE STATUS WS-STATUS1.
SELECT OUTFILE ASSIGN TO OUTDD2
FILE STATUS WS-STATUS2.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 IN-APP-NUM PIC 9(8).
02 IN-CUST-NAME1 PIC X(20).
02 IN-CUST-NAME2 PIC X(20).
02 IN-CUST-GENDER PIC X(10).
02 IN-CUST-ADD1 PIC X(20).
02 IN-CUST-ADD2 PIC X(20).
02 IN-CUST-ADD3 PIC X(20).
02 IN-CUST-STATE PIC X(10).
02 IN-CUST-PIN PIC 9(6).
02 IN-CUST-STATUS PIC X(3).
02 IN-PROD-NUM PIC 9(3).
02 IN-APP-DATE PIC X(10).
02 IN-REFERAL-NUM PIC 9(2).
02 IN-CREDIT-LIMIT PIC 9(6)V9(2).
02 IN-APP-STATUS PIC X(2).
02 IN-AGENT-CODE PIC 9(4).
02 IN-CUST-SS-NUM PIC X(12).
02 IN-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD REJECT.
01 OUT-REC1.
02 R-APP-NUM PIC 9(8).
02 R-CUST-NAME1 PIC X(20).
02 R-CUST-NAME2 PIC X(20).
02 R-CUST-GENDER PIC X(10).
02 R-CUST-ADD1 PIC X(20).
02 R-CUST-ADD2 PIC X(20).
02 R-CUST-ADD3 PIC X(20).
02 R-CUST-STATE PIC X(10).
02 R-CUST-PIN PIC 9(6).
02 R-CUST-STATUS PIC X(3).
02 R-PROD-NUM PIC 9(3).
02 R-APP-DATE PIC X(10).
02 R-REFERAL-NUM PIC 9(2).
02 R-CREDIT-LIMIT PIC 9(6)V9(2).
02 R-APP-STATUS PIC X(2).
02 R-AGENT-CODE PIC 9(4).
02 R-CUST-SS-NUM PIC X(12).
02 R-PRE-APP-STAT PIC X(2).
02 PIC X(20).
FD OUTFILE.
01 OUT-REC2.
02 OUT-APP-NUM PIC 9(8).
02 OUT-CUST-NAME1 PIC X(20).
02 OUT-CUST-NAME2 PIC X(20).
02 OUT-CUST-GENDER PIC X(10).
02 OUT-CUST-ADD1 PIC X(20).
02 OUT-CUST-ADD2 PIC X(20).
02 OUT-CUST-ADD3 PIC X(20).
02 OUT-CUST-STATE PIC X(10).
02 OUT-CUST-PIN PIC 9(6).
02 OUT-CUST-STATUS PIC X(3).
02 OUT-PROD-NUM PIC 9(3).
02 OUT-APP-DATE PIC X(10).
02 OUT-REFERAL-NUM PIC 9(2).
02 OUT-CREDIT-LIMIT PIC 9(6)V9(2).
02 OUT-APP-STATUS PIC X(2).
02 OUT-AGENT-CODE PIC 9(4).
02 OUT-CUST-SS-NUM PIC X(12).
02 OUT-PRE-APP-STAT PIC X(2).
02 PIC X(20).
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE CESTAB03
END-EXEC.
EXEC SQL
DECLARE CESTAB03 CURSOR WITH HOLD FOR
SELECT CUST_SS_NUM
FROM CESTAB03
END-EXEC.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
77 WS-STATUS1 PIC 99.
77 WS-STATUS2 PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM READ-PARA UNTIL WS-STATUS = 10.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY ' INFILE STATUS IS : ' WS-STATUS.
OPEN OUTPUT REJECT.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
OPEN OUTPUT OUTFILE.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
EXEC SQL
OPEN CESTAB03
END-EXEC.
CLOSE-PARA.
CLOSE INFILE REJECT OUTFILE.
DISPLAY ' FILE STATUS IS : ' WS-STATUS.
DISPLAY ' REJECT FILE STATUS IS : ' WS-STATUS1.
DISPLAY ' OUTFILE FILE STATUS IS : ' WS-STATUS2.
EXEC SQL
CLOSE CESTAB03
END-EXEC.
READ-PARA.
READ INFILE.
EXEC SQL
FETCH CESTAB03
INTO :CUST-SS-NUM
END-EXEC.
IF WS-STATUS = 00
IF CUST-SS-NUM = IN-CUST-SS-NUM
MOVE IN-REC TO OUT-REC2
WRITE OUT-REC2
ELSE
MOVE IN-REC TO OUT-REC1
WRITE OUT-REC1
END-IF
END-IF.
******************************************************************************************************
TABLE CREATION--SPUFI
******************************************************************************************************
--CREATE TABLE CESTAB01(APP_NUM INT NOT NULL,
-- CUST_FIRST_NAME CHAR(20),
-- CUST_SECOND_NAME CHAR(20),
-- CUST_GENDER CHAR(6),
-- CUST_ADD_1 CHAR(20),
-- CUST_ADD_2 CHAR(20),
-- CUST_ADD_3 CHAR(20),
-- CUST_STATE CHAR(10),
-- CUST_P_CODE INT,
-- CUST_STATUS CHAR(3),
-- PROD_NUM SMALLINT,
-- APP_DATE DATE,
-- REFERAL_NUM SMALLINT,
-- CREDIT_LIMIT DEC(8,2),
-- APP_STATUS CHAR(2),
-- AGENT_CODE SMALLINT,
-- CUST_SS_NUM CHAR(12),
-- PREV_APP_STATUS CHAR(2),
-- PRIMARY KEY (APP_NUM));
--CREATE UNIQUE INDEX CESTAB01 ON CESTAB01(APP_NUM);
SELECT * FROM CESTAB03;
--DROP TABLE BANK_APPL;
--DELETE FROM CESTAB03;
--CREATE TABLE CESTAB02(CUST_P_CODE INT,
-- CUST_ADD_3 CHAR(20),
-- CUST_STATE CHAR(10))
--CREATE TABLE CESTAB03(CUST_SS_NUM CHAR(12),
-- CUST_FIRST_NAME CHAR(20),
-- CUST_SECOND_NAME CHAR(20),
-- CUST_GENDER CHAR(6))
*********************************************************************************************************
MAP SET CREATION --CICS PROGRAM
*********************************************************************************************************
CESMAP DFHMSD TYPE=&SYSPARM, *
MODE=INOUT, *
LANG=COBOL, *
STORAGE=AUTO, *
TIOAPEX=YES, *
CTRL=(FREEKB,ALARM)
MAIN DFHMDI SIZE=(24,80), *
LINE=1, *
COLUMN=1, *
JUSTIFY=LEFT, *
CTRL=ALARM *
DFHMDF POS=(5,10),LENGTH=12,INITIAL='APPLICATION', *
ATTRB=(BRT,PROT)
DFHMDF POS=(5,40),LENGTH=6,INITIAL='DATE :', *
ATTRB=(BRT,PROT)
FIELD1 DFHMDF POS=(5,46),LENGTH=10, *
FIELD1 DFHMDF POS=(5,46),LENGTH=10, *
ATTRB=(BRT,UNPROT,IC,NUM)
DFHMDF POS=(5,57),LENGTH=1,ATTRB=ASKIP
DFHMDF POS=(6,40),LENGTH=6,INITIAL='TIME :', *
ATTRB=(BRT,PROT)
FIELD2 DFHMDF POS=(6,46),LENGTH=8, *
ATTRB=(BRT,UNPROT,NUM)
DFHMDF POS=(6,55),LENGTH=1,ATTRB=ASKIP
DFHMDF POS=(10,15),LENGTH=10,INITIAL='APP_NO :' *
ATTRB=(BRT,PROT)
FIELD3 DFHMDF POS=(10,25),LENGTH=8,ATTRB=(BRT,UNPROT,NUM)
DFHMDF POS=(10,34),LENGTH=1,ATTRB=ASKIP
DFHMDF POS=(12,15),LENGTH=10,INITIAL='CARDTYPE :' *
ATTRB=(BRT,PROT)
FIELD4 DFHMDF POS=(12,25),LENGTH=10,ATTRB=(BRT,UNPROT)
DFHMDF POS=(12,35),LENGTH=1,ATTRB=ASKIP
DFHMDF POS=(14,15),LENGTH=10,INITIAL='CR_LIMIT :' *
ATTRB=(BRT,PROT)
FIELD5 DFHMDF POS=(14,25),LENGTH=10,ATTRB=(BRT,UNPROT,NUM)
DFHMDF POS=(14,35),LENGTH=1,ATTRB=ASKIP
DFHMDF POS=(16,15),LENGTH=10,INITIAL='APPROVE :' *
ATTRB=(BRT,PROT)
FIELD6 DFHMDF POS=(16,25),LENGTH=1,ATTRB=(BRT,UNPROT)
DFHMDF POS=(16,27),LENGTH=1,ATTRB=ASKIP
insert program
IDENTIFICATION DIVISION.
PROGRAM-ID. INSERT1.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INDD
FILE STATUS IS WS-STATUS.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 IN-REC.
02 app_no PIC 9(20).
02 cust_NAME PIC X(20).
02 cust_add_1 PIC X(20).
02 cust_add_2 PIC x(5).
02 SEX PIC X(5).
02 SEM PIC 9(1).
02 F PIC X(49).
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE DB1A
END-EXEC.
77 WS-SQLCODE PIC -999.
77 WS-STATUS PIC 99.
PROCEDURE DIVISION.
PERFORM OPEN-PARA.
PERFORM INSERT-PARA UNTIL WS-STATUS = 10.
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY "SQLCODE:" WS-SQLCODE.
PERFORM CLOSE-PARA.
STOP RUN.
OPEN-PARA.
OPEN INPUT INFILE.
DISPLAY 'FILE STATUS' WS-STATUS.
CLOSE-PARA.
CLOSE INFILE.
INSERT-PARA.
READ INFILE.
DISPLAY "ROLL NO=" ROLLNO.
IF WS-STATUS = 00
MOVE ROLL TO ROLLNO
MOVE NAME TO STD-NAME
MOVE DEPT TO STD-DEPT
MOVE FEES TO STD-FEES
MOVE SEX TO STD-SEX
MOVE SEM TO STD-SEM
EXEC SQL
INSERT INTO cestab01
(app_no,
cust-first-name,
cust-second-name,
cust-gender,
cust-add1,
cust-add2,
cust-add3,
cust-state,
cust-p-code,
cust-status,
prod-num,
app-date,
referal-num,
credit-limit,
app-status,
agent-code,
cust-ss-num,
prev-app-stat
)
VALUES( :app_no,
:cust-first-name,
:cust-second-name,
:cust-gender,
:cust-add1,
:cust-add2,
:cust-add3,
:cust-state,
:cust-p-code,
:cust-status,
:prod-num,
:app-date,
:referal-num,
:credit-limit,
:app-status,
:agent-code,
:cust-ss-num,
:prev-app-stat
)
END-EXEC
END-IF.
EVALUATE SQLCODE
WHEN 0
WHEN 0
DISPLAY 'app-no :' app-no
DISPLAY 'Cust-first-name :' Cust-first-name
DISPLAY 'cust-second-name :' cust-second-name
DISPLAY 'credit-limit :' credit-limit
DISPLAY 'cust-gender :' cust-gender
DISPLAY 'app-status :' app-status
WHEN OTHER
MOVE SQLCODE TO WS-SQLCODE
DISPLAY 'SQLCODE :' WS-SQLCODE
END-EVALUATE.
address validation
IDENTIFICATION DIVISION.
PROGRAM-ID. address.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE dcgen
END-EXEC.
exec sql
decalre dcgen cursor for
select cust_p_code,cust_add1,cust_state
from cestab01
end-exec
77 WS-SQLCODE PIC -999.
77 a pic 9(3).
77 b pic x(20).
77 c pic x(10).
PROCEDURE DIVISION.
perform open-para.
perform fetch-para until sqlcode=100.
perform close-para.
stop run.
open-para.
exec sql
open dcgen
end-exec.
fetch-para.
exec sql
fetch dcgen into :cust-p-code,
:cust-add1,
:cust-state
end -exec.
close-para.
exec sql
close dcgen
end-exec.
MOVE SQLCODE TO WS-SQLCODE.
DISPLAY WS-SQLCODE.
DISPLAY cust-first-name.
DISPLAY cust-second-NAME.
STOP RUN.
