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.
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.
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.
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.
The input to BIND process is DBRM and output is 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.
Difference between Plan and Package
|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,
JCLS used to execute the COBOL DB2 program
//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
//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.
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.
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
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.
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.
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)
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-
Releases resources at each commit point, unless cursors are held.
Releases resources only when the thread terminates.
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.
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
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.
(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
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
The program/module name which is to be bound, or whose DBRM is to be bound to create the Plan/Package.
The PDS directory name where the DBRM used during BIND is located
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
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.