Steps in a COBOL-DB2 Program


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

image

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.

image

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.  High five High five High five High five High five High five High five

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

image

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

image

image

About these ads

About Shibu Thannikkunnath

I'm a System Engineer in mainframe/AS400 Department of IBM
This entry was posted in How To and tagged , , , , , , . Bookmark the permalink.

17 Responses to Steps in a COBOL-DB2 Program

  1. Rahul says:

    thanks for posting with screen shots.

  2. Razza says:

    Could you explain cursors and stored procedures in detail ?

  3. celery says:

    How you do display records with no WHERE CLAUSE?

    Example:
    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.
      1 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-

  4. Aryan says:

    Thanks alot!! The tutorial was really helpful

  5. rex says:

    thanks much..twas really helpful..u wouldnt know how desperately i was looking for something like this..and i have some doubts..
    1.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.

      1. 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.

  6. Pooja says:

    Thanks Shibu..it’s realy helpful to all…

  7. Narendra says:

    i 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
    below 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?

  8. Narendra says:

    can any write the jcl for above problem?

  9. Pragatheesh says:

    Thanks 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.

  10. niruban says:

    thanks a ton…….
    but 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……………

  11. Bee says:

    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.

  12. balkrishna says:

    steps for cobol+db2+cics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s