When I started exploring DB2-COBOL programs over internet, I realized that we haven’t got a lot of tutorials for Cobol-db2 programs over there. So I thought I can share some of my programs with all of you. My Cobol-DB2 program is very small, but it could help us to understand the structure, how to compile, how to bind and how to run very well.
Step 1. DB2 Table structure.
---------+---------+
SEQ NAME
---------+---------+
001 SHIBU
002 SABU
003 BALANKRISHNAN
004 SREEMATHY
This is my table it has only two columns, first 3 var char for sequence numbrs and next 15 var char for Name field.
Create table:
Go to SPUFI create our table using the following SQL statements.
CREATE TABLE MYNAM
( SEQ VARCHAR(03),
NAME VARCHAR(15));
INSERT INTO MYNAM VALUES(‘001’,’SHIBU’);
INSERT INTO MYNAM VALUES(‘002’,’SABU’);
INSERT INTO MYNAM VALUES(‘003’,’BALAKRISHNAN’);
INSERT INTO MYNAM VALUES(‘004’,’SREEMATHY’);
COMMIT;
Step 2. Execute DCL gen to create copybook for table.
Navigate to DB2I primary options and select DCLGEN
Now Enter table name (mynam) in source table name, location for copybook ( 'TSHRCI.PGMG.CICOB(mynam)' in our program )on dataset name field and type ADD in action field .You can leave default values in rest of the fields. Then press enter.
You can verify that, whether copy book generated on the given pds.
Step 3. Code the application. ( cobol db2 sample code)
| ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. MYTAB.
AUTHOR. SHIBU.T
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE MYNAM
END-EXEC.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
01 WS-ROLL-NO PIC X(3) VALUE IS '000'.
01 WS-ROLL-N1 PIC 9(3).
01 WS-NAME PIC X(15).
01 WS-SQLCODE PIC --------9.
PROCEDURE DIVISION.
PARA-00100-MAIN.
DISPLAY 'EXECUTION STARTED.'.
MOVE 0 TO WS-ROLL-N1.
PERFORM UNTIL WS-ROLL-NO = '001'
COMPUTE WS-ROLL-N1 = WS-ROLL-N1 + 1
MOVE WS-ROLL-N1 TO WS-ROLL-NO
PERFORM PARA-00200-FETCH THRU PARA-00200-FETCH-EXIT
END-PERFORM.
STOP RUN.
PARA-00200-FETCH.
DISPLAY 'PARA-00200-FETCH.'.
EXEC SQL
SELECT *
INTO : DCLMYNAM
FROM MYNAM
WHERE SEQ = : WS-ROLL-NO
END-EXEC.
IF SQLCODE = 0
DISPLAY 'EXECUTION SUCESS!'
PERFORM PARA-00300-PRINT THRU PARA-00300-PRINT-EXIT
ELSE
DISPLAY 'FETCH FAILED!, PROGRAMM TERMINATING.'
MOVE SQLCODE TO WS-SQLCODE
DISPLAY 'SQL CODE FROM LAST FETCH: ' WS-SQLCODE
END-IF.
PARA-00200-FETCH-EXIT.
EXIT.
PARA-00300-PRINT.
DISPLAY 'NAME IS >> ' NAME ' <<'.
PARA-00300-PRINT-EXIT.
EXIT. |
Step 4. key elements of COBOL DB2 program.
a) Include tables copy book and SQLCA.
We have to include the copy book of table which we desired to use on our app. syntax is follows
EXEC SQL
INCLUDE MYNAM
END-EXEC.
Also it is important that including SQL communication area copybook into our program. Syntax follows.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
Read more about SQL CA on IBM redbooks
b) Define Host variables and variable for SQL.
Hold on! best practice ahead. 
It will be readlly good if you declare SQLCODE variable as following.
01 WS-SQLCODE PIC --------9.
So whenever a value move into SQLCA, all the negative sql codes will have a – symbol and positive sql codes will be just number, so that we can differentiate both error codes.
Since it is sample program and the intention is to describe the flow of Cobol-DB2 program, I haven’t included the cursors or complex sql statements on our program, I’ll be adding more programs in coming days.
Step 4 Compile and BIND COBOL-DB2 Program
Above Diagram is pretty well describes the whole process of compiling a cobol DB2 program. As I mentioned in the image, precompile step I sno longer required in the latest releases of Cobol, the compiler itself is capable of doing this task.
Step 5. RUN Program
When COBOL-DB2 Program executes, the plan and Package must specified in the SYSIN, When the first sql statement of our program executes, db2 search the collections and consistency token in the provided plan using the provided package name, if the consistency token is not matching gDB2 throw a -805 Error.
Cobol-DB2-Compile-BIND-RUN JCL.
| //R0318BDJ JOB (12345678),MSGCLASS=H,REGION=4M,
// MSGLEVEL=(1,1),CLASS=A,NOTIFY=&SYSUID
//*
//JOBLIB DD DSN=DSN910.DB9G.SDSNEXIT,DISP=SHR
// DD DSN=DSN910.SDSNLOAD,DISP=SHR
//*****************************************************************
//* SQL PREPROC AND COBOL COMPILATION:
//*****************************************************************
//*-NB---SQL PREPROC NOW IS NOW DONE BY THE COBOL COMPILER:
//*****************************************************************
//COB EXEC PGM=IGYCRCTL,
// PARM=(SQL,LIB,NOTERM,NOSEQUENCE,LIB,XREF,DYN,'')
//STEPLIB DD DSN=IGY410.SIGYCOMP,DISP=SHR
// DD DSN=DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB DD DSN=TSHRCI.PGMG.DBRM(MYTAB),DISP=SHR <<Desired DBRM Loc
//SYSIN DD DSN=TSHRCI.PGMG.COBOL(MYTAB),DISP=SHR <<SRCE LIB
//SYSLIB DD DSN=TSHRCI.PGMG.COBOL,DISP=SHR <<CPYBK LIB
//SYSLIN DD DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(800,(500,500))
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT2 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT3 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT4 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT5 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT6 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSUT7 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//*****************************************************************
//* LINKEDIT
//*****************************************************************
//LKED EXEC PGM=IEWL,PARM='XREF',COND=(4,LT,COB)
//SYSLIB DD DISP=SHR,DSN=CEE.SCEELKED
// DD DISP=SHR,DSN=DSN910.SDSNLOAD
// DD DISP=SHR,DSN=DFH320.CICS.SDFHLOAD
// DD DISP=SHR,DSN=ISP.SISPLOAD
// DD DISP=SHR,DSN=GDDM.SADMMOD
//SYSLIN DD DSN=&&LOADSET,DISP=(OLD,DELETE)
// DD DDNAME=SYSIN
//SYSLMOD DD DSN=TSHRCI.LOAD.LIB,DISP=SHR <<desired LOAD LIB
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD SPACE=(1024,(50,50)),UNIT=SYSDA
//SYSIN DD *
NAME MYTAB(R) << Program Name
/*
//*****************************************************************
//* BIND
//*****************************************************************
//BIND EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB DD DSN=TSHRCI.PGMG.DBRM,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9G) <<Change this to your DB2 subsystem>>
BIND PLAN(MYTABP) MEMBER(MYTAB) -
ACT(REP) ISO(CS) ENCODING(EBCDIC)
END
/*
//*****************************************************************
//* RUN PGM
//*****************************************************************
//RUNPGM EXEC PGM=IKJEFT01,DYNAMNBR=20 COND=(4,LT)
//STEPLIB DD DSN=DSN910.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB9G) <<Change it to your db2 subsystem>>
RUN PROGRAM(MYTAB) PLAN(MYTABP) LIB('TSHRCI.LOAD.LIB')
END
//* |
Screen shots
thanks for posting with screen shots.
ReplyDeleteyou are welcome, let me know if you need any particular topic which needs to be covered.
ReplyDeleteCould you explain cursors and stored procedures in detail ?
ReplyDeleteSure Razza, you will be able to see the cursors and SP in the coming days.
ReplyDeleteHow you do display records with no WHERE CLAUSE?
ReplyDeleteExample:
SELECT *
INTO : DCLMYNAM
FROM MYNAM
I'm know that it is not good practice but want to understand how to do
you can do this with Cursors in DB2. The cursors approach is as mentioed below.
ReplyDelete1 declare the cursor with the SQL statement (with out where).
2. Open it
3. Fetch the first record, read remaining records if required
4. Display it,
5. Close the cursor.
Please visit the blog again I'll be posting the Cursor and sample programms soon.
-Shibu.T-
Thanks alot!! The tutorial was really helpful
ReplyDeletethanks much..twas really helpful..u wouldnt know how desperately i was looking for something like this..and i have some doubts..
ReplyDelete1.could u tell me what u are trying to do with that perform-until in main-para..
2.whats that 'DCLMYNAM' in the select query..
thanks again..
Thanks for reading my blog, glad to hear it helped you.
ReplyDelete1. I'm displaying only the name belongs to roll-number 001, we can do this directly by retreiving name of ws-roll-no 001, I was using this program for some other purpose I forgot to change that :)
2. DCLMYNAM is the copybook generated by DCLGEN, the step INCLUDE MYNAM will copy that copybook into our program while compiling. Rather than declaring seperate host variables, I was reusing the DCLGEN variable here
Hope this helps.
Thanks Shibu..it's realy helpful to all...
ReplyDeletei have one file consists of Acc no, Acc name, Amount and one table consists of Acc no, Acc name, Amount and BFT(Y/N). Match the account number of the file with the tabe and Create two output report whose formats are given
ReplyDeletebelow and one output file that contains the account number and their description if BFT Account is Y and
other output file that contains the account number and their description if BFT Account is N .
Create a JCL to execute above program?
can any write the jcl for above problem?
ReplyDeleteThanks a lot Shibu.. It helps a lot.. Will it be possible for you to explain CURSOR concept in detail (mainly about Update CURSOR)? Thanks again for your screen shoots.
ReplyDeletethanks a ton.......
ReplyDeletebut i have a doubt,
is it possible we can use * in embedded sql.......
and also could u give us screen shot for cursor pgm.........
we are really looking forward you...............
Thanks Shibu... I was a cobol programmer for 14 years, but have been laid off for 3 years. Your tutorial refreshed my memory on cobol-db2 programming, it's just what I was looking for. Looking forward to your next tutorial.
ReplyDeleteDear Shibu.
ReplyDeleteReading a post from you at: https://mainframegeek.wordpress.com, I liked your explanation about: Steps in a COBOL-DB2 Program, I just had a doubt, regarding the compilation, link editing and execution JCL. I did your example here, but it gave error 4. It did not influence the final result, which was correct, is there a way to remove this error?
Celso Henrique
celsofigueiredo@gmail.com