CCAPrint Newsletter for Model 204 and System 1032 Users

Model 204 allocates virtual storage based on parameters set in CCAIN. While there are some operating system differences when defining a virtual machine or partition at two gigabytes, the way Model 204 allocates storage is the same. This article uses OS/390 as the example; however, the principles are the same under VM/CMS and VSE/ESA.

Parameters such as LENQTBL, LRETBL, MAXBUF, NDCBS, NDIR, NFILES, NSERVS, NUSERS, SERVSIZE, and many others are used to calculate the number of bytes of virtual storage allocated by each invocation of the IBM GETMAIN macro. These parameters, set by the system manager, determine the size of tables and control blocks that are allocated to the ONLINE region and ultimately determine the amount of virtual storage consumed by Model 204.

Each parameter, with the exception of MAXBUF, is used in a calculation that results in the allocation of a fixed number of bytes of virtual storage. The resulting control block or table must be allocated at the computed size or initialization fails.

MAXBUF, on the other hand, specifies an upper limit on the number of in-memory database buffers that are allocated.

Allocating fewer buffers than specified by MAXBUF is not an ideal arrangement, because the number allocated is not easily predictable. And worse, allocation is no longer under the direct control of the system manager. It has effectively been usurped by the operating system. The IBM REGION parameter, the virtual machine size, or partition size sets an upper limit on virtual storage available to Model 204.

In OS/390, the preferred approach is to set REGION=0M on the JOB card giving the job access to 2G (two gigabytes) of virtual storage. This setting does not mean that the job will consume 2G of virtual storage. It merely sets the upper limit to 2G, leaving the option of whether to use that amount to the application. In the case of Model 204, that option is controlled by parameters set in CCAIN.

Among those parameters, the setting of MAXBUF accounts for approximately 60ñ90 percent of the total virtual storage allocated to the Online address space. Its setting is critical for relieving a large number of performance problems, which are the result of allocating too few buffers.

Most small to medium sized Onlines with 50 to 500 users should be running with MAXBUF set to a minimum of 10,000. A number of customers are currently running production Onlines with MAXBUF set to 20,000 or 40,000 or 60,000 or 75,000 and 99,000.

The calculation for virtual storage, in bytes, allocated by the MAXBUF parameter is:

For each 6184-byte buffer allocated there are also three 12-byte hash cells and one 256-byte buffer control block.

Therefore, MAXBUF set to 10,000 results in the allocation of 64,760,000 bytes of virtual storage for database buffers. This assumes that the address space has access to at least that amount of storage, plus the additional storage required by the ONLINE load module and by other control blocks and tables defined by parameter settings in CCAIN.

The value of the REGION parameter, as described in the IBM JCL Reference Manual, may fall into one of four different categories:

Two IBM supplied exits, IEFUSI and IEALIMIT, are also available to each installation to modify or reject, with JCL errors, certain settings of the REGION parameter.

The key point to remember regarding the REGION parameter, regardless of the value specified, is that REGION does not cause that amount of virtual storage to be allocated. It merely sets an upper limit or, in the case of REGION=0M, indicates that the upper limit is 2G. The amount of storage actually allocated is determined by the application which, in the case of Model 204, is determined by CCAIN parameters. This is why it is safe, assuming MAXBUF is not set to a value which would cause excessive system paging, to set REGION=0M in all Model 204 jobs. Model 204 does not allocate all available storage just because it is available - it allocates what is required based on parameter settings in CCAIN.

With REGION set to zero megabytes, the system manager controls precisely how much storage is allocated and can predict that amount with certainty.

In Query Efficiency: Part 1, we discussed what key tables are, how System 1032 builds them, and what they look like on the inside. Part 2 addresses how they actually work and what happens when a query is processed.

We know from looking at the internals of a key that there is a sorted list of unique attribute values, and a record list of $IDs for each associated record. Essentially, this information is stored in a linked list with the rest of the tree structure providing lightning fast access to it.

As described in Part 1, the top-higher-level key and first-lower-level key provide the entry points for reading key tables. Queries always read the key information from top to bottom through the branches of the tree, or left-to-right across the lower-level keys. Many commands do both. The result is four types of key table reads:

The operator specified in the FIND command determines which type of key table read is used on a given query.

Let's return now to our demographics example, where we have a three-level key table of LAST_NAME, and look at some examples using the names Fuller and Vais. Let's assume Fuller is about 20 percent of the way through the value distribution, and Vais is around 95 percent through. Figure 1 shows Fuller several blocks in from the first-lower-level key and Vais in the next to last block of the lower-level keys.

The shaded blocks show the blocks actually read to satisfy the query. Figure 1 is an example of a top-down read. The FIND EQ command is the fastest form of the FIND command that reads keys and the simplest in its use of the key tree structure. It starts reading at the top-higher-level key block and follows the pointers down through the tree until it reaches the lower-level key block containing the value Fuller.

Note: The figures show the values in mixed case for readability. In fact, unless USE CASE was specified when the keys were built, the values are stored in uppercase.

The NE (Not Equal) operator reads the exact same key blocks. Rather than try to locate all the things that don't equal Fuller, it is easier and much faster to locate what does equal Fuller and then select everything else. Because the record lists are stored as a bitmap, it's very easy and fast to create a bitmap that contains all records in the dataset, then perform a logical XOR operation on them to select the records that do not equal Fuller. System 1032 always knows how many records exist in the dataset.

The CT (Contains) operator provides a good example of a full scan-across type of key table read. In this case, every value must be examined to determine if it contains the value in the FIND expression. When doing a scan-across read from the lowest value in the keys, it is not necessary to read down the tree structure. Instead, reading starts from the first-lower-level key block and moves across the lower-level key blocks. Figure 2 shows the blocks read to process:

Figure 2. FIND CT reads the lower-level key blocks in a full scan-across read

As with the NE operator, the NCT (Does Not Contain) operator generates the same key table reads at the CT operator. Then the complement is used to determine the result. FIND commands that cause a full scan-across read are the least efficient type of FIND.

Similar to the scan for the CT operator in Figure 2, the operators LT (Less Than) and LE (Less Than Or Equal To) start reading the key blocks at the first-lower-level key block and continue across. The difference is that with LT or LE there's an end-point. Instead of reading all the way through the keys, when the value in the FIND expression is reached, the reading stops.

The difference between LT and LE operator results is whether or not the end-point is included in the result. Figure 3 shows the blocks read for:

The key blocks are read until the value Vais is reached. Any records containing the value Vais are not included in the selection set because the LT operator was specified, not the LE operator.

Figure 3. FIND LT performs a partial scan-across read, stopping at the value Vais

As you can see, most of the lower-level key blocks are read in Figure 3. Vais is very near the end of the key table. In Figure 3, only one lower-level-key block was not read. Knowing your data distribution can be very helpful in composing much more efficient queries.

The last of our four basic key reading patterns is the across/down combination. Similar to the LT operator, this happens when a range of values is requested, with an end-point (or both end-points) established. Figure 4 illustrates the evaluation of the following command where the end-point is on the far end of the range.

Processing starts at the top-higher-level key block and navigates the tree structure until it reaches Vais in the lower-level key blocks. From there it scans across the lower-level key blocks until it reaches the end, including all values greater than its starting point.

In Figure 4, Vais is so close to the end of the value distribution, it takes a total of only four disk I/Os to select the desired records. The result is a much more efficient query than the FIND LT command shown in Figure 3, because of the value distribution. If the value in each command had been Fuller, the LT operator would have required substantially less I/O to complete than the GE operator.

The other operators that use this type of read are BEGINS, BET (Between), and NBET (Not Between). These operators provide both end-points, directly or implied. The BEGINS operator reads top-down to the value specified, then scans only until it reaches a value that no longer meets its BEGINS criteria. At that point, the FIND processing is complete. The BET operator reads top-down to the smaller value and scans across until the higher value is reached. As we've seen before, the negated operator, NBET, actually does a BET lookup and then selects everything else.

In Part 3 we will examine the FIND command internals. Some FIND commands don't read the keys at all.


Home DownloadsownFeedback