Steps to Create COBOL DB2 Program

Steps for executing COBOL DB2 program

Step1: Create all these necessary tables
Step2: Create DCLGEN(optional)
Step3: Precompile
Step4: Compile and Link Edit
Step5: DB2 BIND
Step6: Execute/Run the Program

Create all these necessary tables – Before execution of COBOL DB2 program, we have to list all the tables which are using in the program and we should make sure that all the tables creation and insertion should be with records or whatever operations we are going to do in the table.

Create DCLGEN

This is optional, we also call DCLGEN as Declaration Generator. We have to create DCLGEN before that we can also use the equivalent COBOL variable for the DB2 columns.

To begin Go to option 15, 15 is for DB2 V10 and then go to option 2 DCLGEN. Create dataset.

Precompile

It is separated modified source non SQL statement with SQL statement.

It consists of two parts:-

i) Modified Source NON

SQL = COBOL- It is a COBOL program and we have to LINK and EDIT.
The first step to do in this part is compiled and then Link edit, It will create a role model, Which we will use in COBOL DB2 program.

ii) Modified Source SQL = SQL

In this part, we have a DBRM(Database Create Module). Here when the precompiler separates a skill and non-skill statement then the skill statements goes in DBRM. DBRM contains a set of a SQL statement, which are derive from COBOL DB2 source program. DBRM is passed as input to this Bind process, so this DBRM will be input to this BIND process and output will generate output as a Plan.

In the end, we will be having Plan/Package. we will discuss Plan and package further.
In mainframe we use a program DSNHPC for this particular purpose, we will learn about this later.

Compile and link-edit

We use JCL to compile and Link Edit. As you can see in Image, your compile and Link edit will look like this.
DBRMLIMB is very important because this contains the SQL related statement. This program will use to compile the COBOL program. In down there is LINK EDIT for LINK EDIT we are using a program IEWL.

Follow @tutorial_brain

DB2 Bind

We use DB2 Bind for a lot of things, for example, it will check for authorization and syntax error. It will also do query performance, query optimization. It is very very important because whatever precompiler does I based on DCLGEN and BIND process does syntax checking using DB2 catalog tables.

Bind performs the authorization check.
Checks the syntax of the SQL syntax using the DB2 catalog table. This is more important because the syntax check done by precompiler is done using DCLGEN.
Provides the run time instruction for the SQL which is present in DMRM. Each SQL statement is parsed and the best access method is chosen.
It also do DB2 optimization.

Note The most important error and syntax Checking is done by Bind process and it checks for syntax using DB2 catalog table. So, it checks for all the syntax related error for SQL related statements.

The input to BIND process is DBRM and output is PLAN/PACKAGE.
Output- PLAN/Package

What is Plan?

It is an executable module which contains the access path which is produced by the DB2 optimizer. It consists of one or more DBRM or package. DB2 catalog contains the details of the plan.

What is Package?

A package is a single, bound DBRM only. The package also contains the access paths. A package is not directly executable entity. The package needs to be included in a PLAN and we need to execute that PLAN.

Note We can bind a DBRM into a PLAN or we can BIND a package into a collection and then the collection into PLAN. These are an accessed path through which we can access the data from the DB2 tables.

Difference between Plan and Package

PACKAGE	PLAN
These are the non-executable entity	These are Executable entity.
It is a single bound DBRM only.	It is one or more DBRM
Package also contains an access path	Plan is an old way of accessing path
DB2 introduced the concept of package	Previously we had the concept of a plan

The program used for Binding DB1.

//MATEKS JOB MSGCLASS=X,MSGLEVEL=(1,1),CLASS=B,
// REGION=5M,NOTIFY=&SYSUID

JCLS used to execute the COBOL DB2 program

Note In your system, you have to check which JCL pr-compiled is and compiled Link Edit. If you are not very sure please go back to your system admin and ask for the JCL pre-compiled link edit compiled JCL.

PRECOMPILE-COMPILE-LINKEDIT-JCL

//MATEKSD JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//*  COBOL + DB2 PRECOMPILE AND LINKEDIT.
//*
//DBPREC  EXEC DB2COBCL, 
//			   COPYLIB=MATEKS.COPYLIB,      <= COPYBOOK LIBRARY
//			   DCLGLIB=MATEKS.DCLGEN.COBOL, <= DCLGEN LIBRARY
//			   DBRMLIB=MATEKS.DBRMLIB,      <= DBRM LIBRARY
//			   LOADLIB=MATEKS.LOADLIB,      <= LOAD LIBRARY
//			   SRCLIB=MATEKS.DB2.COBOL,     <= SOURCE LIBRARY
//			   MEMBER=PGM-NAME              <= SOURCE MEMBER

RUN JCL

//MATEKSD JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//*	Run JCL...
//*		
//STEP01   EXEC PGM=IKJEFT01,
//		       DYNAMNBR=20,REGION=4096K
//STEPLIB  DD  DISP=SHR,DSN=DSNA10.DBAG.SDSNEXIT
//         DD  DISP=SHR,DSN=DSNA10.SDSNLOAD
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSTSIN  DD  *
DSN SYSTEM	(DBAG	)
  RUN PROGRAM	(PGM-NAME )	-
  PLAN		    (MKSP01	)	-
  LIBRARY	('MATEKS.LOADLIB')
END
/*
//SYSOUT   DD  SYSOUT=*

VARIOUS PARAMETER USED IN JCL BIND PROCESS

//MATEKSD JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID 
//*
//*  DB2 BIND JCL.
//*
//BIND     EXEC PGM=IKJEFT01
//STEPLIB  DD DISP=SHR,DSN=DSNA10.DBAG.SDSNEXIT 
//	       DD DISP=SHR,DSN=DSNA10.SDSNLOAD
//DBRMLIB  DD DSN=MATEKS.DBRMLIB,DISP=SHR 
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD *
DSN	SYSTEM (DBAG	)
BIND	MEMBER	  (PGM-NAME) -
        PLAN	  (MKSP01 )  -
		ACTION	  (REP)      -
		ISOLATION (CS)       -
		VALIDATE  (BIND)     -
		RELEASE	  (COMMIT)   -
		OWNER	  (MATEKS)   -
		QUALIFIER (MATEKS)   -
		ENCODING  (1047)
END
/*
//*

There are some important parameters in the Bind process. Although, it is not necessary that all shops will use these parameters in the Bind JCL but let us see these important JCL Parameters.

Follow @tutorial_brain

Isolation level

The isolation level that is associated with an application process determines the degree to which the data that is being accessed by that process is locked or isolated from other concurrently executing processes. The isolation level is in effect for the duration of a unit of work. Isolation Level decides the level of locking used along with LOCK parameters on the DB2 objects.

The database manager supports 4 isolation levels.

a) Repeatable read (RR)

The repeatable read isolation level locks all the rows that an application references during a unit of work (UOW).

b) Read stability (RS)

The read stability isolation level locks only those rows that an application retrieves during a unit of work. RS ensures that any qualifying row read during a UOW cannot be changed by other application processes until the UOW completes and that any change to a row made by another application process cannot be read until the change is committed by that process. Under RS, access to uncommitted data and non-repeatable reads are not possible.

c) Cursor stability (CS)

The cursor stability isolation level locks any row being accessed during a transaction while the cursor is positioned on that row. This lock remains in effect until the next row is fetched or the transaction terminates. However, if any data in the row was changed, the lock is held until the change is committed.
Under CS, access to the uncommitted data of other applications is not possible.
CS is the default isolation level. It is suitable when you want maximum concurrency and needs to see only committed data.

d) Uncommitted read (UR)

The uncommitted read isolation level allows an application to access the uncommitted changes of other transactions. Moreover, UR does not prevent another application from accessing a row that is being read, unless that application is attempting to alter or drop the table.
This isolation level is suitable if you run queries against read-only tables, or if you issue SELECT statements only, and seeing data that has not been committed by other applications is not a problem.

Tables, views, and indexes that are being created or dropped by other transactions are not available while the transaction is processing. Any other changes by other transactions can be read before they are committed or rolled back.

Validate

The VALIDATE option determines whether to recheck, at run time, errors of the types “OBJECT NOT FOUND” and “NOT AUTHORIZED” that are found during bind or rebind.
VALIDATE can have 2 types. When to validate authorization and table constraints

-> BIND => at BIND time
-> RUN => at run time

a) RUN

If you specify the VALIDATE(RUN) bind option, and the application to be bound contains an error with a SET host-variable assignment statement, the binding process still issues only warning messages, no error messages.

b)BIND

Indicates that if not all objects or needed privileges exist at bind time, the process issues error messages, and does not bind or rebind the plan or package, except that:

For BIND PACKAGE only, if you use the option SQLERROR(CONTINUE), the bind succeeds, but the SQL statements in it that have errors cannot execute.
With VALIDATE(BIND), DB2 does not check authorization for the LOCK TABLE statement and some CREATE, ALTER, and DROP statements until run time.

ACQUIRE

When to acquire high-level locks, ALLOCATE (when the plan is allocated at run time) or USE (when the first SQL is encountered at run time during Execution)

RELEASE

The RELEASE option determines when to release resources that a program uses, either at each commit point or when the program terminates.

It has 3 types-

a)COMMIT
Releases resources at each commit point, unless cursors are held.

b)DEALLOCATE
Releases resources only when the thread terminates.

c)INHERITFROMPLAN
Enables a local package to inherit the value of the RELEASE option from the plan, regardless of whether the package was bound remotely or locally.

QUALIFIER

This is like Table Qualifier when using Unqualified embedded SQL. The QUALIFIER option determines the implicit qualifier for unqualified names of tables, views, indexes, and aliases contained in the plan or package. It can be the same as the owner as well.
In case, this is same as OWNER, You can think of this like the Authorization ID of the owner of the plan or package

OWNER

The Authorization ID of the owner of the plan or package. The DB2 User/ID/Group which has appropriate table authorizations and BIND authorizations to create the Access path (plan/package) is also called as OWNER.

EXPLAIN

(YES/NO) -> If we want the DB2 to store information about the access path generated by the DB2 optimizer in OWNER.PLAN_TABLE then YES else Type NO

PACKAGELIST

We use this, when Plan is created and is used to relate to one or more Packages so that when a Package is re-bound due to some changes, Plan doesn’t need to be re-bound again

MEMBER

The program/module name which is to be bound, or whose DBRM is to be bound to create the Plan/Package.

LIBRARY

The PDS directory name where the DBRM used during BIND is located

FLAG (E/C/W/I/)

Level of messages to be displayed as a result of BIND,
e.g. Warning, Information, Critical, Error, etc..
FLAG(I) – Information displays all messages, and if FLAG(E) we use, only Error messages if encountered during BIND are displayed

ENCODING

Values – ( ASCII )( EBCDIC )( UNICODE )( ccsid )
EBCDIC is the only valid option for a plan or package that is precompiled prior to DB2® Version 7. If you specify ccsid on any plan or package precompiled prior to DB2 Version 7, the value of ccsid must match the EBCDIC CCSID specified on the installation panel DSNTIPF (the SYSTEM EBCDIC CCSID). You can specify ASCII, UNICODE, or ccsid, where ccsid is a value other than the SYSTEM EBCDIC CCSID for any plan or package precompiled on DB2 Version 7 or later. You might select this option when a data source, such as a terminal emulator, uses a CCSID that is not the same as the SYSTEM EBCDIC CCSID. For example, a user has a terminal emulator with a CCSID of 1047, but the SYSTEM EBCDIC CCSID is 37.

In this case, the plan or package being used by that user should be bound with ENCODING (1047).
ENCODING also affects the content of the data that gets return by the SQL statement DESCRIBE. DB2 will return column names, label names, or both (if requested) in the specified application encoding scheme.

Defaults: The default package application encoding scheme is not inherited from the plan application encoding option. The default for a package that is bound on a remote DB2 for z/OS® system is the remote server’s default application encoding scheme. Similarly, when a plan or package is run on a remote DB2 for z/OS server, the specified ENCODING option gets ignore. Instead, the remote server’s encoding scheme is used.