Learning Objectives
After completing this session, you will be able to:
Develop a COBOL DB2 application program
Execute a COBOL DB2 application program Steps involved in Program Preparation
Following are the steps involved in preparing a COBOL-DB2 application program:
o Precompilation
o Bind
o Compilation
o Link Edit Need for Precompilation
In early days, many companies had different machines for development and production.
Development often took place on an older model machine and COBOL was the language of choice. The LOAD modules were moved onto a bigger production machine and companies rarely had a COBOL license on the production machine. So, DB2 had to allow development on one box with a COBOL compiler but without a DB2 subsystem and allow production runs on a different box with a DB2 subsystem but without a COBOL compiler.
So programmers wrote COBOL programs and embed SQL and during compilation, to look for COBOL errors, they required a system to strip out the SQL, leaving only COBOL and that system is Precompiler.
Tasks of Precompiler
Expansion of “INCLUDE”: If the delimiters are surrounded an INCLUDE statement, the Precompiler goes to the INCLUDE library named in the job control language data definition statement and pulls the included MEMBERNAME into the program. This function is the same as a COBOL COPY MEMBERNAME, but the timing is different.
COBOL Copybooks get copied in at COMPILE time; DB2 INCLUDEs get copied in at precompile time.
Syntax Check: If the delimiters surround an SQL statement, the precompiler does a very basic syntax check to make sure that the column and table names are valid. The Precompiler uses the top part of the DCLGEN (Table Declaration) to validate the SQL syntax. So Precompiler does not need DB2 to run.
Splitting the Program: Most important task performed by the DB2 Precompiler is to split the program into two parts: a COBOL part and a DB2 part. All of the SQL that are
A single program containing two languages, COBOL and SQL, goes into the DB2 Precompiler and two pieces come out.
“Modified Source Code” which is the COBOL with all of the SQL commented out and replaced with the equivalent COBOL Calls.
“DBRM” which contains only SQL
As the “Modified Source Code” and “DBRM” will take different paths to get the runtime executables, in order to identify the correct pair during execution, the precompiler attaches timestamp both in “Modified Source Code” and in “DBRM” and is called “Consistency Token”.
Following figure shows the functionality of Precompiler:
In the JCL, the Precompilation step is as follows:
//**********************************************************************
//* DB2 PRECOMPILE THE COBOL PROGRAM
*
//**********************************************************************
//DB2PCOMP EXEC PGM=DSNHPC,
// PARM='HOST(IBMCOB),XREF,SOURCE,FLAG(I),APOST,NEWFUN(NO)' //STEPLIB DD DISP=SHR,DSN=DSN810.SDSNLOAD
//DBRMLIB DD DISP=SHR,DSN=&DBRMLIB(&LOADMEM)
//SYSCIN DD DSN=&&DSNHOUT,DISP=(NEW,PASS,DELETE),UNIT=SYSDA, // SPACE=(800,(500,500))
//SYSLIB DD DISP=SHR,DSN=&INCLUDE // DD DISP=SHR,DSN=&COBCOPY //SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA //SYSUT2 DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//SYSIN DD DISP=SHR,DSN=&SOURCE(&MEMNAME)
In the JCL, for the Precompilation we need to provide the following items.
o Input:
SYSIN: COBOL-DB2 application program (member name with the PDS) SYSLIB: DCLGEN PDS name
o Output:
DBRMLIB: DBRM member name with the PDS.
SYSCIN: Modified source code. (Generally this has the temporary dataset which will be passed to the compilation step).
Bind
Tasks of Bind
Authorization Check: DB2 must make sure that the programmer has the BIND authority and the SQL authority to perform the requested SQL task.
Syntax Check: BIND task is a bit redundant, like precompile, must also check the syntax of the SQL, but the BIND check is more sophisticated. BIND uses the DB2 CATALOG table information to make sure that the column names are valid, comparisons are numeric-to-numeric, and so on.
Optimization:
The most important BIND task is to come up with run-time instructions for the SQL in the DBRM.
Each SQL statement is parsed and all of the possible methods for retrieving the desired columns and rows from the table are weighed, measured, and evaluated based on possible estimated I/O, CPU, and SORT overhead. After all that input (and more) is weighed and compared, the cheapest, most cost-effective access path is chosen, and the runtime instructions for that one path are created. This process of choosing the optimized access path is called “Optimization”. This is repeated for each SQL statement in the DBRM.
These runtime instructions are stored in the “PLAN”.
Plan
A plan is an executable module containing the access path logic produced by the DB2 optimizer. It can be composed of one or more DBRMs and packages.
There are two ways of doing BIND:
o Bind DBRMs into PLANs
o Bind DBRMs into PACKAGEs
o
Need of Package
In the early releases of DB2, the DBRM was bound into a PLAN. This method worked just fine as long as the program was a standalone program that is, PGMA was bound to a PLANA. When PGMA needed to CALL PGMB, since only one PLAN could be named in an execute statement, the PLAN had to contain run-time instructions for both PGMA and PGMB. This problem was solved by having the BIND instruction for
PLANA consists of a member list; the DBRMs for both PGMA and PGMB were listed as members and if PGMB called PGMC, then the three would be listed as members.
Member lists got longer and longer.
Drawbacks of having a very long list:
o While binding, authorization check, syntax check, optimized access path and the creation of run time instructions for the chosen path is carried out. If the PLAN contains one member, PGMA, this process should be quick. But if there are 500 DBRMs in the member list, the BIND could take hours.
o If one of the programs (PGMA) changes in the list of 500 programs, the changed program must be precompiled and precompile changes the consistency token and creates a new DBRM. That new DBRM must be bound into the PLAN. When the PLAN is bound, all 500 DBRMs (not just the modified PGMA) will be rebound. It could take hours to BIND a PLAN, even though 499 of the 500 programs haven't changed.
o The same case that is, you need to bind the entire list if a new program is added to the list or any existing program is removed from the list.
To overcome these disadvantages, package concept came into picture.
Package
A package is a single, bound DBRM with optimized access paths. By using packages, the table access logic is "packaged" at a lower level of granularity, at the program level. To execute a package, you must first include it in the package list of a plan. Packages are not directly
executed—they are only indirectly executed when the plan in which they are contained executes.
Understanding Plan and Package more clearly
To help differentiate between plans and packages, consider a grocery store analogy.
o Before going to the grocery store, you should prepare a shopping list. As you go through the aisles, when you find an item on your list, you place the item in your shopping cart. After you pay for the items at the check-out register, the clerk places your grocery items in a bag. You can think of the purchased items as DBRMs. The bag is the plan. You have multiple DBRMs (grocery items) in your plan (shopping bag).
o In a package environment, rather than actually removing the items from the shelf, you would mark on your shopping list the location of each item in the store. Upon checking out, you would give the list to the clerk at the counter. The clerk then would place the list in the bag—not the actual items. The plan (bag) contains a list pointing to the physical location of the packages (grocery items) that are still on the shelf. This approach is a good way to compare and contrast the two different environments.
Version
When using packages, you can keep multiple versions of a single package that refer to different versions of the corresponding application program. We can specify a version as a parameter to the DB2 precompiler.
Advantage of Version
The programmer can use a previous incarnation of a program without rebinding.
Before the availability of packages, when programmers wanted to use an old version of a program, they were forced to rebind the program's plan using the correct DBRM.
If the DBRM was unavailable, they had to repeat the entire program preparation process.
Advantages of Package
Reduced bind time and bind overhead: When you are utilizing packages and the SQL within a program changes, only the package for that particular program needs to be rebound. If packages are not used when multiple DBRMs are bound into a plan and the SQL within one of those programs changes, the entire plan must be rebound.
Granularity of bind parameters: With packages, you can specify our bind options at the program level, such as the isolation level and release parameters. By specifying different parameters for specific packages and including these packages into a plan, many combinations of isolation level and release are possible. For example, create a single plan that provides an isolation level of cursor stability (CS) for one of its
Versioning: Packages can be versioned, thus enabling us to have multiple versions of the same package existing at the same time. Simply by running the appropriate load module, DB2 chooses the correct package to execute. DB2 uses a package selection algorithm to execute the correct access path.
Collection
A collection is a user-defined name from 1 to 18 characters that the programmer must specify for every package. A collection is not an actual, physical database object.
Advantages of Collection
By specifying a different collection identifier for a package, the same DBRM can be bound into different packages. This capability permits program developers to use the same program DBRM for different packages, enabling easy access to tables that have the same structure (DDL) but different owners.
You could use collections to separate programs for different application areas, such as payroll and inventory.
BIND PLAN(PLANNAME) MEMBER(PROGRAM) QUALIFIER(USERID) - ACTION(REP) RETAIN ISOLATION(UR) -
VALIDATE(BIND) END
/*
In the JCL, for the “Bind a DBRM into a Plan” you need to provide the following items:
DBRMLIB: DBRM PDS
SYSTSIN:
o DSN S or DSN SYSTEM: DB2 subsystem
o PLAN: Name of the plan to be bound
o MEMBER: DBRM Member name
o QUALIFIER: USERID
o ACTION: Add for new plan. If the plan exists, then it will be replaced. Otherwise a new plan is added.
In the JCL, if you want to bind more than one DBRM into a same plan, specify the DBRM member list in MEMBER as follows:
BIND PLAN(PLANNAME) MEMBER(PROGRAM1 PROGRAM2 PROGRAM3) QUALIFIER(USERID) -
ACTION(REP) RETAIN ISOLATION(UR) - VALIDATE(BIND)
END
/*
In the JCL, if you want to bind a DBRM into a package, specify the PACKAGE name and DBRM member in MEMBER as follows.
ACTION(REP) RETAIN ISOLATION(UR) - VALIDATE(BIND)
END
/*
In the JCL, if you want to bind a list of packages into a plan, specify them in PKLIST as follows:
Example:
BIND PLAN (PLAN NAME) PKLIST(PACKAGE1, PACKAGE2) QUALIFIER(USERID) - ACTION(REP) RETAIN ISOLATION(UR) -
QUALIFIER(F5925DBA) END
A DBRM bind to a package
DSN SYSTEM(DSNG) (Give Sub System) BIND PACKAGE(F5938_T_PROV ) - (Give Package Name) OWNER(F5925AP1) -
QUALIFIER(F5925DBA) -
MEMBER(F5938PPZ) - (Give DBRM Name) VALIDATE(BIND) -
ISOLATION(CS) - RELEASE(COMMIT) - ACTION(REP ) - END
A DBRM bind to a package with the collection and is bound to a plan.
In order to specify all the packages in the collection, we can specify as F6418_PB_F6418ACR.*
BIND PLAN(F6418PBP) - OWNER(F6418DB) - QUALIFIER(F6418DB) -
PKLIST(F6418_PB_F6418ACR.F6418ACR, - (Collection ID:
F6418_PB_F6418ACR)
F6418_PB_F6418BA1.F6418BA1) - ACT(REP) -
ISOLATION(CS) - ACQUIRE(USE) - RELEASE(COMMIT) - VALIDATE(BIND)
============================================================
BIND PACKAGE (F6418_PB_F6418ACR.F6418ACR) - ACT(ADD) -
ISOLATION(CS) - QUALIFIER(F6418DB) - OWNER(F6418DB) - MEMBER(F641803T) - RELEASE(COMMIT) -
VALIDATE(BIND)
Packages and Plans Storage
Actual Plans and Packages are stored in DB2 Directory.
Information about the Plans and the Packages are stored in the DB2 catalog.
Compile
In the JCL, the compilation for the COBOL is as follows:
//**********************************************************************
//* COBOL COMPILE * //**********************************************************************
//COBCOMP EXEC PGM=IGYCRCTL,COND=(4,LT,DB2PCOMP),
// PARM='LIB,XREF,OFFSET,MAP,RENT,RES,NODYNAM,TEST' //SYSIN DD DSN=&&DSNHOUT,DISP=(OLD,DELETE)
//SYSLIB DD DSN=&COBCOPY,DISP=SHR
//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
In the JCL, for compilation you need to provide the following items:
SYSIN: Modified source code
SYSLIB: COBOL Copybook PDS
SYSLIN: Object Module that Is generally specified as a temporary dataset
Link Edit
In the JCL, the link edit for the COBOL is as follows:
//**********************************************************************
//* LINK EDIT THE PROGRAM * //**********************************************************************
//LINKEDIT EXEC PGM=IEWL,COND=(4,LT,COBCOMP),
// PARM='MAP,LIST,XREF,LET,AMODE=31,RMODE=ANY,RENT,REUS' //SYSLIB DD DISP=SHR,DSN=DSN810.SDSNLOAD
// DD DISP=SHR,DSN=CEE.SCEELKED // DD DISP=SHR,DSN=CEE.SCEERUN // DD DISP=SHR,DSN=ISP.SISPLOAD // DD DISP=SHR,DSN=&LOADLIB
//SYSLIN DD DSN=&&LOADSET,DISP=(OLD,DELETE) //SYSLMOD DD DISP=SHR,DSN=&LOADLIB(&LOADMEM) //SYSPRINT DD SYSOUT=*
In the JCL, for link edit you need to provide the following items:
SYSLIN: Object Module
SYSLMOD: Load module member name with the PDS
Execution
At run time, the load module starts up and hits a paragraph containing a CALL to DB2. This CALL contains information such as a description of the consistency token, the content of the SQL host variables, and so on. The CALL invokes the COBOL-DB2 interface program, which connects to DB2 and finds the corresponding plan (or package) with the consistency token and the execution takes place. If there is no correct plan or package with the matching timestamp anywhere in DB2, then you get -805 or -818 error code.
The JCL for execution is as follows:
//COBDB2RN EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=THYD018.DB2.LOADLIB,DISP=SHR Load Library name // DD DSN=CEE.SCEERUN,DISP=SHR
// DD DSN=DSN810.SDSNLOAD,DISP=SHR
//DBRMLIB DD DSN=THYD018.DB2.DBRMLIB,DISP=SHR DBRM Name //SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1) Subsystem name
RUN PROGRAM(FIRSTCD)- Load module name
PLAN(THYPL018) - Plan name END
/*
Try It Out
Problem Statement:
Prepare and execute the program which you have created in Session # 21 (COBOL DB2 application program, to get the hourly rate for a particular rate category supplied by the user).
Code:
Program Preparation:
//THYD018C JOB MSGCLASS=X,MSGLEVEL=(1,1),CLASS=B, // REGION=5M,NOTIFY=&SYSUID //*
//**********************************************************************
//* INPUT AREA * //**********************************************************************
// SET MEMNAME=FIRSTCD <- PROGRAM NAME // SET LOADMEM=FIRSTCD <- LOAD MODULE NAME // SET DBRMLIB=THYD018.DB2.DBRMLIB <- DBRM LIBRARY // SET INCLUDE=THYD018.DCLGEN.COPYLIB <- DB2 DCLGEN // SET COBCOPY=THYD018.DB2.COPYLIB <- COBOL COPYBOOK
//* DB2 PRECOMPILE THE COBOL PROGRAM * //SYSCIN DD DSN=&&DSNHOUT,DISP=(NEW,PASS,DELETE),UNIT=SYSDA, // SPACE=(800,(500,500))
//SYSLMOD DD DISP=SHR,DSN=&LOADLIB(&LOADMEM) //STEPLIB DD DSN=THYD018.DB2.LOADLIB,DISP=SHR // DD DSN=CEE.SCEERUN,DISP=SHR // DD DSN=DSN810.SDSNLOAD,DISP=SHR //DBRMLIB DD DSN=THYD018.DB2.DBRMLIB,DISP=SHR //SYSTSPRT DD SYSOUT=*
Refer File Name: ProgramPreparation_FIRSTCD_Session#27_Slide#7&
ProgramExecution_FIRSTCD_Session#27_Slide#12 to obtain soft copy of the program code
Summary
The following figure summarizes the program preparation and execution of a COBOL-DB2 application program:
The developed COBOL-DB2 source program is precompiled to get the COBOL portion of “Modified Source Code” and DB2 portion of “Database Request Module (DBRM)”. A
“Consistency Token” is created both in modified source code and in DBRM.
“Modified Source Code” is compiled to get “Object Module”.
“Object Module” is link edited to get “Load Module” which is the runtime executable of COBOL portion.
DBRM is bound either to a package and then to a plan or directly to a plan to get the runtime executable of DB2 portion.
Actual plan/package is stored in DB2 Directory.
Information about the plan/package is stored in DB2 Catalog.
During execution, when the load module encounters the DB2 call, the corresponding plan is retrieved using the consistency token which is created during the
precompilation and the execution takes place.
Test Your Understanding
1. What is the need for Precompilation?
2. Explain the tasks involved in Precompilation.
3. Explain the tasks involved in Bind.
4. What is a plan?
5. What is a package?
6. What is a version?
7. What is a collection?
8. What are the advantages of package over the plan?
9. What happens during the execution of COBOL-DB2 application program?
Exercises
Prepare and execute the COBOL-DB2 application program which you have developed in the previous session which is as follows:
COBOL-DB2 application program which accepts “Patient Number” and displays Patient Information as follows:
Patient Number: xxxx
Patient Name: <First name> <Last name>
Gender: <x>
Date of Birth: <DD/MM/YYYY>