DB2 Precompile-Compilation-Bind Process
INTRODUCTION
Static SQL - COBOL PRE-compile process
For COBOL-Static SQL program, program needs to be
precompiled before doing actual
compilation of that program.
Following are the 4 steps to compile a COBOL-DB2
program
STEP 1 - DB2 PRECOMPILE Process
The DB2 Precompile performs three functions.
First, it checks the SQL in
the program for errors. Second, it adds
working storage areas and source
code compatible statements that are used to
invoke DB2. One of the working
storage areas contains a literal "timestamp"
called a consistency token.
Finally, all of the SQL statements are
extracted from the program source
and placed into a member called the DataBase
Request Module, or DBRM, which
also contains the consistency token.
STEP 2 - COMPILE
The modified source from the precompile is
then compiled. The code is
checked for errors, and a compiled version of
the code is created.
STEP 3 - LINK-EDIT
The compiled code is link-edited along with
statically called source language
and DB2 run-time modules to create a Load
Module. Imbedded in this
module is the same consistency token that was
generated in the precompile.
If multiple DB2 programs are statically
linked together, the resulting load
module contains a consistency token for each
one.
STEP 4 - DB2 BIND
The bind process reads the DBRM that was
created in the precompile and
prepares an access path to the data. This
access path, along with the
consistency token, is stored in the DB2
catalog as a Package. Every Package
is bound into a Package List, or Collection.
The name of the Collection is
specified by the PACKAGE parameter. A
Collection is a group of Packages
that are included in one or more Plans. The
QUALIFIER parameter of the
bind is used to direct the SQL to the
specific set of DB2 objects (tables,
views, aliases or synonyms) qualified by this
name.
EXECUTING COBOL-DB2 PROGRAM
When COBOL-DB2 Program executes, the plan
name must be specified.
For online CICS programs, the plan name is
specified by Tran ID in
the Resource Control Table (RCT). For a batch
program, the plan name is
specified in the SYSTSIN input DD. The
packages for all DB2 programs
executed under a Tran ID or batch job step
must be included in collection
bound into this plan. When the first SQL
statement of each program is
executed, DB2 searches the collections within
the plan using the package name
and consistency token from the load module.
If an exact match is not
found, a -805 SQLCODE is returned.
COBOL – DB2 PROGRAMMING
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 EMPNAME DEPARTMENT SALARY DESIGNATION
1000 XXXXXXX XX 10000 SE
1001 YYYYYYY YY 9000 SE
1002 ZZZZZZZ ZZ 20000 MA
STEP 1. We need to declare the table structure in the
WORKING-STORAGE SECTION or LINKAGE SECTION.
EXEC SQL
DECLARE DSNXXX.EMPLOYEE
( EMPID CHAR(10) NOT NULL,
EMPNAME CHAR(30) NOT NULL,
DEPARTMENT CHAR(2) NOT NULL,
SALARY DECIMAL(10,2) NOT NULL,
DESIGNATION CHAR(4) NOT NULL )
END-EXEC.
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 < copybookname >
END-EXEC.
STEP 2. 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 PIC X(10).
05 HV-EMPNAME PIC X(30).
05 HV-DEPARTMENT PIC X(2).
05 HV-SALARY PIC S9(8)V99 COMP-3.
05 HV-DESIGNATION PIC CHAR(4).
If you use db2 tool DCLGEN, it will automatically creates this
structure also
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.
COBOL DB2 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 ).
