Scribd Logo
Upload

Welcome to Scribd!

  • 429 views

    Cobol Db2 Program Dclgen

    Uploaded by

    Sumana Chakraborty
    This document provides steps for writing a COBOL program to access a DB2 database table. It includes declaring the table structure, defining host variables, including SQLCA, and writing a SQL statement to retrieve a record by name. The key steps are: 1. Declare the DB2 table structure in COBOL. 2. Define host variables to store retrieved data. 3. Include SQLCA to check SQL statement execution. 4. Write a SELECT statement to retrieve a record matching an input name into host variables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Cobol Db2 Program Dclgen

    Uploaded by

    Sumana Chakraborty
    429 views5 pages
    This document provides steps for writing a COBOL program to access a DB2 database table. It includes declaring the table structure, defining host variables, including SQLCA, and writing a SQL statement to retrieve a record by name. The key steps are: 1. Declare the DB2 table structure in COBOL. 2. Define host variables to store retrieved data. 3. Include SQLCA to check SQL statement execution. 4. Write a SELECT statement to retrieve a record matching an input name into host variables.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides steps for writing a COBOL program to access a DB2 database table. It includes declaring the table structure, defining host variables, including SQLCA, and writing a SQL statement to retrieve a record by name. The key steps are: 1. Declare the DB2 table structure in COBOL. 2. Define host variables to store retrieved data. 3. Include SQLCA to check SQL statement execution. 4. Write a SELECT statement to retrieve a record matching an input name into host variables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    429 views5 pages

    Cobol Db2 Program Dclgen

    Uploaded by

    Sumana Chakraborty
    This document provides steps for writing a COBOL program to access a DB2 database table. It includes declaring the table structure, defining host variables, including SQLCA, and writing a SQL statement to retrieve a record by name. The key steps are: 1. Declare the DB2 table structure in COBOL. 2. Define host variables to store retrieved data. 3. Include SQLCA to check SQL statement execution. 4. Write a SELECT statement to retrieve a record matching an input name into host variables.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 5

    Writing a COBOL DB2 Program.

    Let us assume we are writing a cobol program to read EMPLOYEE


    table and get the details of employee with the name XXXXXXX.
    Let us go in step wise..
    create the following table in db2 or assume it is there in db2 database.
    EMPLYEE
    EMPID
    1000
    1001
    1002

    STEP 1.

    EMPNAME
    XXXXXXX
    YYYYYYY
    ZZZZZZZ

    DEPARTMENT
    XX
    YY
    ZZ

    SALARY
    10000
    9000
    20000

    DESIGNATION
    SE
    SE
    MA

    We need to declare the table structure in the


    WORKING-STORAGE SECTION or LINKAGE SECTION.
    EXEC SQL
    DECLARE DSNXXX.EMPLOYEE
    ( EMPID
    EMPNAME
    DEPARTMENT
    SALARY
    DESIGNATION
    END-EXEC.

    CHAR(10)
    NOT NULL,
    CHAR(30)
    NOT NULL,
    CHAR(2)
    NOT NULL,
    DECIMAL(10,2)
    NOT NULL,
    CHAR(4)
    NOT NULL )

    we can use DB2 tool called DCLGEN to generate this declaration


    for us and can include that copy book here.
    if you create a copybook using DCLGEN. Use following sntax to include
    EXEC SQL
    INCLUDE
    END-EXEC.

    STEP 2.

    < copybookname >

    Declare host variables in WORKING-STORAGE SECTION.


    HOST VARIABLES - A host variable is a data item declared in cobol to use
    it in embedded SQL.
    For EMPLOYEE table, host variable declaration is look like as follows...
    01

    EMPOYEE-RECORD.
    05 HV-EMPID
    05 HV-EMPNAME
    05 HV-DEPARTMENT
    05 HV-SALARY
    05 HV-DESIGNATION

    PIC X(10).
    PIC X(30).
    PIC X(2).
    PIC S9(8)V99 COMP-3.
    PIC CHAR(4).

    also

    If you use db2 tool DCLGEN, it will automatically creates this structure
    along with table declaration specified in step1.

    STEP 3.

    Include SQLCA as follows in WORKING-STORAGE SECTION.


    EXEC SQL
    INCLUDE SQLCA
    END-EXEC.
    What is SQLCA?
    SQLCA

    - SQL communication area.


    When a SQL statement executes, DB2 places a value in SQLCODE AND
    SQLSTATE host variables or any other fields of SQLCA. based on
    the values in these variables we can know whether sql ran
    sucessfully or not.
    SQLCA contains a declartion of fields like SQLCODE,SQLSTATE and
    SQLERRD etc....

    STEP 4.

    Add a sql statement in procdure division to get the details of employee


    with the name XXXXXXX.

    DISPLAY ' PROGRAM STARTED .... '


    .........
    EXEC SQL
    SELECT
    SALARY
    INTO :HV-SALARY
    FROM EMPLOYEE
    WHERE EMPNAME = 'XXXXXXX'
    END-EXEC.
    IF SQLCODE = 0
    DISPLAY ' SQL EXECUTED SUCESSFULLY '
    DISPLAY ' EMPLOYEE SALARY IS
    '

    HV-SALARY

    ELSE
    DISPLAY ' SQL FAILED '
    DIAPLY ' SQL CODE
    '

    SQLCODE

    END-IF.
    ....
    ....
    DISPLAY ' PROGRAM ENDED'.

    Here SQLCODE = 0 means, sql ran sucessfully without any issues. Hence

    we are displaying the HV-SALARY into the spool.


    If SQLCODE NOT = 0 , there is issue in executing the sql statement.

    Now we have compeleted coding a cobol-db2 program. our next step is to


    compile the program.
    SAMPLE COMPILE JCL
    //DB2COMP (XXX,XXX),'COMPILE JCL',
    //
    CLASS=A,MSGCLASS=A,NOTIFY=&SYSUID
    //********************************************************************
    //* COMPILATION, LINK EDIT AND THE BIND STEP FOR A COBOL DB2 PROGRAM *
    //* WILL BE DONE BY SUBMITTING THIS JOB.
    *
    //* THE DB2 REGIONS AND CORRESPONDING PARAMETERS NEEDS TO BE CHANGED *
    //* WITH RESPECT TO THE PROGRAM
    *
    //********************************************************************
    //*
    PRECOMPILE DB2 PROGRAM
    *
    //*-------------- LOCATION OF DBRM LIBRARY -------------------------*
    //********************************************************************
    //PC
    EXEC PGM=DSNHPC,
    //
    PARM='HOST(COB2),APOST,SOURCE',
    //
    REGION=4096K
    //DBRMLIB DD DISP=SHR,
    //
    DSN=DEV.SURESH.DBRM(DB2PROG)
    <------------------------ (1)
    //STEPLIB DD DISP=SHR,
    //
    DSN=SYSX.DB2.XXX.XXXXX
    //********************************************************************
    //*SYSIN -----------INPUT COBOL DB2 PROGRAM LOCATION-----------------*
    //********************************************************************
    //SYSIN
    DD DISP=SHR,
    //
    DSN=DEV.SURESH.SRC(DB2PROG)
    <---------------------- (2)
    //SYSCIN
    DD DISP=(MOD,PASS),
    //
    DSN=&&TEMP,
    //
    SPACE=(800,(500,500)),
    //
    UNIT=SYSDA
    //********************************************************************
    //*
    DCLGEN MEMBER LOCATION
    *
    //*SYSLIB-----------------INPUT SOURCE LIBRARY FOR SQL---------------*
    //********************************************************************
    //SYSLIB
    DD DISP=SHR,
    //
    DSN=DEV.SURESH.DCL
    <---------------------- (3)
    //
    DD DISP=SHR,
    //
    DSN=DEV.SURESH.CPY
    //SYSPRINT DD SYSOUT=T
    //SYSTERM DD SYSOUT=T
    //SYSUDUMP DD SYSOUT=*
    //SYSUT1
    DD SPACE=(800,(500,500),,,ROUND),
    //
    UNIT=SYSDA
    //SYSUT2
    DD SPACE=(800,(500,500),,,ROUND),
    //
    UNIT=SYSDA
    //*
    //********************************************************************
    //*
    COMPILATION
    *
    //********************************************************************
    //*
    //COB
    EXEC PGM=IGYCRCTL,
    //
    COND=(4,LT,PC),
    //
    PARM=('SIZE(4000K),BUFSIZE(32760),LIST,LIB,MAP,OBJECT',
    //
    'DATA(31),XREF,RENT'),

    //
    REGION=4M
    //STEPLIB DD DISP=SHR,
    //
    DSN=XXXX.XXXXXX
    //SYSIN
    DD DISP=(OLD,DELETE),
    //
    DSN=&&TEMP
    //SYSLIN
    DD DISP=(MOD,PASS),
    //
    DSN=&&LOADTMP,
    //
    SPACE=(800,(500,500)),
    //
    UNIT=SYSDA
    //********************************************************************
    //*--------------SOURCE LIBRARIES FOR COBOL DB2 CODE (COPY LIBRARIES)*
    //********************************************************************
    //SYSLIB
    DD DISP=SHR,
    //
    DSN=DEV.SURESH.DCL
    <----------------- (4)
    //
    DD DSN=DEV.SURESH.CPY,DISP=SHR
    //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
    //*
    //*
    //********************************************************************
    //*
    LINK EDIT
    *
    //********************************************************************
    //*
    //LKED
    EXEC PGM=IEWL,
    //
    COND=((4,LT,COB),(4,LT,PC)),
    //
    PARM='XREF'
    //SYSLIB
    DD DISP=SHR,
    //
    DSN=SXXX.SXXXXXXX
    //
    DD DISP=SHR,
    //
    DSN=XXXX.DB2.XXX.XXXXLOAD
    //
    DD DISP=SHR,
    //
    DSN=SYS1.VSCLLIB
    //SYSLIN
    DD DISP=(OLD,DELETE),
    //
    DSN=&&LOADTMP
    //*
    DD DDNAME=SYSIN
    //********************************************************************
    //*----------------LOCATION OF LOAD LIBRARY--------------------------*
    //SYSLMOD DD DISP=SHR,
    //
    DSN=DEV.SURESH.LOADLIB(DB2PROG)
    <------------ (5)
    //SYSPRINT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //SYSUT1
    DD SPACE=(1024,(50,50)),
    //
    UNIT=SYSDA
    //*
    //********************************************************************
    //*
    BIND - BIND THE DB2 PACKAGE
    *
    //********************************************************************

    //BIND
    EXEC PGM=IKJEFT01,
    //
    COND=(4,LT),
    //
    REGION=4096K
    //STEPLIB DD DISP=SHR,
    //
    DSN=XXX4.DB2.XXXX.XXXXLOAD
    //DBRMLIB DD DISP=SHR,
    //
    DSN=DEV.SURESH.DBRM(DB2PROG)
    <--------------- (6)
    //SYSPRINT DD SYSOUT=*
    //SYSTSPRT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //SYSTSIN DD *
    DSN SYSTEM (DEVDB
    )
    BIND MEMBER
    (DB2PROG) PACKAGE
    (PACKG11) LIBRARY
    ('DEV.SURESH.DBRM') <---------------- (7)
    ACTION
    (REP) ISOLATION (CS) VALIDATE (BIND)RELEASE
    (COMMIT) OWNER
    (SURESH) QUALIFIER (DEVQUALI)
    END
    /*
    **************************** Bottom of Data ****************************
    (1)

    When we precompiled, precompiler will create the DBRM, it will


    be placed in the pds specified here.

    (2)

    Location of COBOL-DB2 program

    (3)

    Needs to speficiy DCLGEN member locations

    (4)

    Needs to specify DCLGEN and COPYBOOK locations here

    (5)

    Load module location, load module will be created here. this


    location needs to be given in run jcl.

    (5) & (6) - specify the location of DBRM, ( same location used in step1 ).

    You might also like