Sunday, June 23, 2013

Cobol DB2 Compilation Process

The Basic steps involved are 1) Precompilation 2) Bind 3) Finally Execution of the COBOL DB2 program. 
When the cobol-DB2 complition job is submitted,the DB2 Precompilation step is first executed.Its Function is to analyse the host language source module ie, the COBOL-DB2 program and stripping out all the db2 sql statements it finds and replaces them by the host language CALL statements to the DSNHLI module. 

SQL statements are commented out and call to DSNHLI module is being made.(click to enlarge)








What the heck does that mean?? :) 
To be clear, it(the precompiler) comments out the all the DB2 SQL statement in our cobol-db2 program,and in turn calls DB2 run time interface modules.
All the sql it encounters in the progrm, the precompiler puts them in a PDS know as  database request module(DBRM)which becomes the input to the next step,ie the bind step.
The runtime call contains necessary parameters(DBRM name,Timestamp, statement number,host variable address,SQLCA address).  
So here the DB2 SQL statements and Cobol code are Separated.
The separated Cobol part goes to cobol compiler and stripped out SQL statements from the same program ,ie DBRM goes as input to BIND step which will create the executable code for the DB2 portion of the cobol program.
The separated cobol and SQL statements must be united later in the final stage of execution.So,to help each other in finding, both the seperated parts are assigned one token,called contoken,which is basically a timestamp. 

Lets see the functions of each step in details now:

STEP1. THE PROCESS OF PRECOMPILATION: 

a. Searches and Expands DB2 related INCLUDE members.You will see something of this sort in the program.
EXEC SQL
  INCLUDE MEMBER
END-EXEC 
These 'member' is nothing but DCLGEN.
b. Searches for all the SQL statements in the cobol program
c. Creates the modified version of the source program where every SQL statement is commented out and call to DB2 runtime interface module replaces each original sql statement.
d. All extracted SQL statements is stored in a PDS called DBRM.
e. Matches each column and table name in the SQL to the DECLARE TABLE statements contained in the DCLGEN


This is how a DBRM looks like having all stripped out SQL statements
Image 1: DBRM






NOTE: DB2 catalog is not accessed during this precompilation process. 
STEP2. BIND: 
we can say this component as the compiler for the sql statements produced in the above step(output of precompilation step). It reads the sql statements from the DBRMs and produces a mechanism to access data as directed by the sql statements being bound.Output of this bind is the PLAN. The bind plan accepts as input one or more DBRM’s produced from the previous DB2 program precompilation steps and creates the executable code for the SQL statements.We BIND the instructions for the SQL that was in DBRM into a PLAN
or BIND the instructions into a PACKAGE.

The major functions of bind are:
a. Parsing and Syntax Checking
b. Optimization
c. Authorization.

Remember we need to Bin
Either
DBRMS  to a Plan
OR
DBRMS To a Package and then Packages into the Plan.We will see Collections later(Nothing but group of packages,or few packages grouped together)
Note:  Only the Plan is executable.  

Thus,PLAN/Package  are nothing but Container which contains how your SQL statements should get executed. In Application Program, we specify What we want, and not how we want it. Plan Contains the logic for 'How'.(We dont care how DB2 internally does it.)

When we say Plan is executable, it means it needs to be executed along with Cobol load module which was separated during precompilation time.

STEP3:  EXECUTION 
At run time, the load module starts up and eventually hits a paragraph containing a call to db2.
Image 2: SQL statements are commented and call to DSNHLI module are being made








We execute the DB2 program witht the DB2 utility IKJEFT01.In the SYSIN parameter we specify the DB2 subsystem name, the plan name and the load module name. 





General info:
When a plan is bound by the BIND COMMAND,DB2 reads the following DB2 catalog tables:
SYSIBM.SYSCOLDIST
SYSIBM.SYSCOLSTATS
SYSIBM.SYSINDEXES
SYSIBM.SYSPLAN
SYSIBM.SYSTABLES
SYSIBM.SYSTABSTATS
SYSIBM.SYSCOLDISTSTATS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSINDEXSTATS
SYSIBM.SYSPLANAUTH
SYSIBM.SYSTABLESPACE
SYSIBM.SYSUSERAUTH

Information about the plan is stored in:

SYSIBM.SYSDBRM
SYSIBM.SYSPACKLIST
SYSIBM.SYSPLANAUTH
SYSIBM.SYSPLSYSTEM
SYSIBM.SYSTABAUTH
SYSIBM.SYSPACKAUTH
SYSIBM.SYSPLAN
SYSIBM.SYSPLANDEP
SYSIBM.SYSSTMT
Note: The DB2 catalog only stores information about the plan. The executable form of the plan called a skeleton cursor table or SKCT is stored in DB2 directory in SYSIBM.SCT02 tabl.

What is a collection in DB2 ??? 
A collection is a group of packages. When we bind a package into a collection, and the collection is available to the plan, any package included in the collection cane be executed by the plan. we can segregate the packages based on application into one collection

Cheers..!!!






 






14 comments:

  1. nicely explained in simple language

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. well-written. Thanks for sharing this article.. I am cleared with the pre-compilation process.

    ReplyDelete
  4. Never seen such a nice explanation for this confusing topic..thanks for sharing.

    ReplyDelete
  5. Very helpful! Thanks a lot for sharing.

    ReplyDelete
  6. Hello Sir, Thank you for your clear explanation. However, I would like to know when there will be more than 1 DBRM gets created or there will be more than 1 package. If you could explain with a real world example it would be great. Thank you! Hoping to receive a reply from you!!

    ReplyDelete
  7. Sir, Do you provide DB2 developer training in India? I have few years of experience on Mainframe but never got a chance to work on DB2.

    ReplyDelete
  8. Thanks Sir, well explained

    ReplyDelete