Mainframes 360
The one stop destination for System Z professionals

Sunday, October 20, 2013

Package, Plans and Collections

When we write a program that needs data from DB2 tables, we code SQL statements in the source program. SQL statements are guest to the host Cobol program. The SQL statements are enclosed by EXEC SQL and END-EXEC delimiters. Between the delimiters, we also code the INCLUDE dclgen (which is similar to coding Cobol COPY). For each table referenced in the SQL query, we must code INCLUDE dclgen. The DCLGEN has the definition of the table, just how the table looks in DB2. The DCLGEN also has host Cobol variables, that are a warehouse for the rows delivered from the SQL query.

Pre-process SQL
Once the program is written, this source program containing SQL code blocks is passed through the DB2 pre-compiler. You see, the pre-compiler is not connected to DB2. So, it does a modest syntax check on the SQL queries. The tables are compared against their definitions in the INCLUDE dclgen.

The pre-compiler then extracts the SQL and creates a Database Request Module(DBRM) containing all the SQL in the program. A modified source program is also created with the SQL commented out and calls to DB2 substituted. Arnold - the modified source program and Danny - the DBRM are two twins born out of pre-processor.

Arnold - the "pure" Cobol source and Danny - the DBRM go down separate paths, until they re-unite in the future. The pre-compiler puts an identical tattoo on both Arnold and Danny. A few years from now, Arnold graduates to become a load-module. Danny grows into a package/plan. We'll look at the life of Arnold and Danny in a while. But, Arnold and Danny carry their tattoos with them throughout their life.

Arnold's journey

The normal compile/link process is executed on, the newly generated, modified Cobol source code. The Cobol COMPILE produces an object program. The object program is LINK EDITed with DB2 routines. The resulting load module(with the tattoo on it) is put into a LOAD library.

The life of Danny

The SQL in the DBRM must now go through a process similar to COMPILE to produce run-time code. The DB2 BIND process transforms the SQL into DB2 interpretable form. The output of BIND is called a package. To convert the DBRM into executable code, the BIND process connects to DB2. It reads the SQL statements in the DBRM and does a much, much more, thorough syntax check. The pre-compiler did a decent check on the SQL, only as accurate as the DCLGEN. The BIND process validates the DB2 tables and columns being accessed against the corresponding DB2 catalog information.

The optimizer is the heart and soul of DB2. It basically works like an expert system. The optimizer analyzes the SQL and determines the most efficient access path for satisfying the SQL. The optimizer queries the statistics stored in the DB2 catalog to determine an access path. Statistics used by optimizer include information about the current status of tables, indexes, columns etc. The optimizer plugs this information into query cost formulas. The optimizer also looks at how many CPU's are being used, size of your bufferpools and much, much more.

A doctor applies his knowledge of medicine, combined with the symptoms of the ailment to prescribe medication. Likewise, the optimizer applies a standard set of rules combined with situational data housed in the DB2 catalog and recommends data retrieval methods.


To many people who are thrown to work at a mainframe computer on their first job, they feel lost. Mainframe people seem to speak a completely different language and that doesn't make life easy. What's more, the books and manuals are incredibly hard to comprehend.

"What on earth is a Mainframe?" is an absolute beginner's guide to mainframe computers. We'll introduce you to the hardware and peripherals. We'll talk about the operating system, the software installed on a mainframe. We'll also talk about the different people who work on a mainframe. In a nutshell, we'll de-mystify the mainframe.

Readers based in India, can buy the e-book for Rs. 50 only or the print book. International readers based in the US and other countries can click here to purchase the e-book.