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.
image

References:

Tuesday, October 1, 2013

Space ABENDs – SB37, D37, E37

What are those extents?
One of the major tasks of the z/OS is to allocate space for files. A simple mechanism for doing this is contigous allocation. In this scheme, a contiguous section of the disk is allocated for a file. This method achieves optimal performance for the application reading/writing the file. However, contiguous allocation scheme has some cons :

1. The OS must know, how much space will be required for the file prior to creating the file. On the z/OS, you must specify explicitly, the amount of space needed through the SPACE parameter.
2. Because files are being continually created or deleted, over a period of time, free space(holes) is scattered across the disk in small chunks.Fragmentation occurs. A single large contiguous section may not be available to satisfy the space request.

To resolve these two problems, the z/OS will commonly use extent allocation - a scheme in which chunks of space are allocated to a file, as and when the program that is writing the file needs them. A system that uses extent allocation, needs to know where all of the extents of a file are located. The z/OS uses a dictionary approach, a system in which a table has the information about each dataset. The VTOC(Volume table of contents) holds a row for every file on the disk. Every row referred as file control block has an array of pointers to extents. To further improve the performance of the VTOC, there's also a VTOC index.
Space Allocation on z/OS
The SPACE parameter specifies the primary and secondary allocation. A space request like (CYL,(100,100)) means that z/OS would try to allocate 100 cylinders of primary space. The secondary space is the overflow size. That is if the dataset becomes full, z/OS routines extend the dataset by another 100 cylinders of secondary space.

For non-VSAM dataset, z/OS allows upto 16 extents of a file per disk volume. Theoretically, you can therefore have a file size of 1 primary extent and upto 15 secondary extents. For a space request like (CYL,(100,100)), this translates to a maximum size of 100 cylinders + 15 x 100 cylinders = 1600 cylinders of space. In practice, z/OS may not find a large contiguous section of the disk to fulfill a primary request. By default, z/OS can satisfy a primary request by breaking it up in upto 5 extents. The bottom-line is, that a primary request can be fulfilled by anything from 1 to 5 extents. Remember, that each extents contributes to the total of the 16 allowed. So, if the full 5 extents are used for the primary allocation, you are left with just 11 for the secondary.

Space abends
As a programmer, you may run into out-of-space errors on creating new datasets. The system abend code SB37 occurs, when there's not enough space or no more extents available on the current volume. The abend code SD37 is raised when you don't specify a secondary allocation. Another abend is SE37, which occurs when there are no more volumes available.

References:

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.