DB2 precompilation process

DB2 INDEX DB2 Bind Process

Advertisements


SQL (Structured Query Language) is used to retrieve the data from the database (DB2).

In order to retrieve the data from DB2 in the program, SQL queries needs to be coded in the program.

The SQL queries should needs to code in between EXEC SQL and END-EXEC.

If the SQL queries are not coded in between EXEC SQL and END-EXEC, COBOL compiler will treat them as COBOL statements and tries to recognize and will throw the compilation error.


The compilation process of COBOL + DB2(pre compilation process):


The compilation process of COBOL + DB2 program is different from normal COBOL program compilation.

If the program has DB2 statements in it, then pre-compiler step needs to be added to the compilation JCL.

The Compilation flow for COBOL + DB2 like below.


Diagram:



PRECOMPILER:


Pre-compiler is mainly responsible for to divide the COBOL, DB2 statements separately.

It takes COBOL+DB2 source code as input and separates the COBOL & DB2 statements.

Pre compiler is responsible to check the syntax errors of DB2 statements before putting in DBRM.

Pre compiler doesn’t check the syntax errors in COBOL statements.

Pre compiler used to generate time stamp tokens.


DBRM:


DBRM is data base request module where syntax free SQL statements are stored.

DBRM library is a user defined PDS and syntax free SQL statements will be stored with the program name.


PLAN:


DSNHPC utility will uses for the pre-compilation process.

DBRM is also PDS where all the SQLs will be stored after it got separated from COBOL program.

RUNTIME SUPERVISOR mainly responsible for to check the what are the time stamps for COBOL & DB2


BIND:


BIND is the process to create the optimized access path to retrieve the data from the DB2 for the SQLs coded in the program.

BIND takes DBRM as input and generates the optimized access path with the help of optimizer which checks the table statistics.

Table statics are


  • Number of columns
  • Number of rows
  • Primary key is available or not
  • Foreign key is available or not.

The Utility used for BIND is IKJEFT01

BIND is also used to check the some of the syntax errors of DB2 statements

Whenever we use GROUP BY and ORDER BY clause we can not specify column in SELECT we get BIND error. find below example for it.


Example:

SELECT NAME FROM STD
GROUP BY SNO.

Mismatch between number of columns selected and number of host variables.find below example for it.


Example:


SELECT SNO, NAME FROM XYZ
INTO :HV-SNO.
WHERE SNO=10


BIND used to specify isolation levels.


SAMPLE PRECOMPILATION JCL:

//JOB CARD
//S-1 EXEC PGM=DSNHPC
//SYSLIB DD DSN=T.T.S (P1)		-	Pre compilation
//DBRMLIB DD DSN=X.Y.Z (M1)
//S-2 EXEC PGM=IKJEFT01			-	BIND
//SYSIN DD *
	MEMBER (P1)
	PROGRAM (P1)
	LIB (X.Y.Z (M1))
	REPLACE (YES/NO)
	ISOLATION LEVEL (CS/RR)
	EXPLAIN (YES/NO)
	PACKAGE (P001)
	PLAN (P001)
/*
//S-3 EXEC PGM=IGYCRCTL			-	Link-edit
//SYSLOAD DD DSN=&&TEMP, DISP=PASS
//S-4 EXEC PGM=IEWL
//SYSLOAD DD DSN=&&TEMP, DISP=SHR
//SYSLMOD DD DSN=Z.Z.A (P1)
//*

DB2 INDEX DB2 Bind Process

Advertisements