Remember the Allocation

Published: March 19, 2008

Hey, Brian:

Because we run packages, we do not get to use our i5/OS system knowledge very often any more, and thus some things fall through the cracks. For example, I recently had to add a field to a file that we had made accessible to Microsoft Access.

This special physical file (ALTINVEN) reflected a one record view of our two-file item master file. We store static info in one item file (ITEMSTAT) and dynamic info in the other file (ITEMDYN). As well-intentioned as this design may have been, both parts change regularly. The production department has some specific uses for the records in this alternate inventory file (ALTINVEN) so when it was created there are a number of fields in it that are not in either of the production item masters. These are maintained only with MS-Access by the production department. We did our job so well in this respect that the production department abandoned their own duplicate inventory file.

We had hired a consultant to create trigger programs on both master files so changes could be immediately reflected in ALTINVEN. He devised an interim file (let's call it INTTRAN) that we use both as an activity log of updates to the master files and as the input file from which we update the alternate inventory file ALTINVEN.

When an update trigger fires on either part of the two-part production inventory file, the trigger programs add a record to INTTRAN. A never-ending transaction processing program (NETRAN) reads the file and whenever a record is added to the INTTRAN file, \NETRAN updates the corresponding inventory record in ALTTRAN with the new record contents. When there are no records to update the WAIT EOF facility in i5/OS, it does not send an end of file to the NETRAN program. Thus, the program continues to wait for the trigger programs to send records to INTTRAN so it can in turn use the added records to update ALTINVEN seconds later. It is pretty clever.

When we added the field, which must be propagated to ALTINVEN from the ITEMDYN file, we got it 100 percent right, or so it seemed. The consultant added the new field to INTTRAN, we recompiled the triggers and NETRAN, fired up the triggers and started it all and it worked. Then, some time today, it crashed with a message that file INTTRAN had run out of room. We had created it with DDS. I looked at it and it had the default size specified. I changed it to *NOMAX and we are running again. The consultant says that *NOMAX is fine and not to worry about it, but I don't want to use all that disk space. How can it be set to *NOMAX and not take a lot of disk space?

--Genevieve

Hi Genevieve,

Sounds like you have an interesting application going on there. You have the best of the PC world and the best of the System i. Putting a trigger on your package files permits you to use the alternate file in real-time without having to know anything about the application logic. Nice.

To have the job stop with a message because somebody forgot to put the right number in for the size of the file (plus the growth factor) surely created a bit of heartache for you, but at least you were able to respond to the message and have the file extended. The problem is quite common as most people do not think about file allocations when they create database files manually.

When you use the Create Physical File (CRTPF) command to create a physical file, you have the following options for file size:

Member size: 
  Initial number of records  . .  10000     1-2147483646, *NOMAX
  Increment number of records  .   1000     Number
  Maximum increments . . . . . .      3     Number

When you create a physical file with SQL, you do not get to set the size of the file at all during creation. The default size for all SQL files is *NOMAX, which is exactly where your INTTRAN file is right now. Let's say that we created the file CITIES in my default library (not a schema) using the following SQL:

create table cities
(name varchar(20),
state varchar(20),
population decimal(8),
zipcode decimal(5) unique)

Table CITIES in KELLY created but was not journaled.

The system does not journal the file because KELLY is a library and not a schema/collection. SQL will automatically journal a file if it is created by SQL in a schema.

If you wanted to look inside the SQL table object (treated as a file in i5/OS), to see its real allocation at the *NOMAX level, you could use the familiar display file description command:

dspfd kelly/cities

Paging through the output panels, you will find the following allocation information:

  Data Space Activity Statistics  . . . . . :
    Data space size in bytes  . . . . . . . : 12288

So, even though it is at *NOMAX, you are using only 12,288 bytes to store the file with no data in it.

Because the System i database (DB2 for i5/OS) is very smart and very flexible, it uses the same structure for CRTPF files as it does for SQL Create Table files. Thus, if you do not like the notion of your SQL files being *NOMAX in terms if constrained growth, you can set the file to something else with the CHGPF command.

CHGPF FILE(KELLY/CITIES) SIZE(30000 *SAME *SAME)

Let's describe the sizing and how it works one more time in just a bit more detail. Your AS/400 running i5/OS sets file sizes to *NOMAX for SQL and it has a number of values that you use to set the size of a file when you create it with CRTPF--with or without DDS. These are:

1. Initial size
2. Size of increment if initial size is ever reached
3. Number of increments

However, i5/OS doesn't listen to any of that stuff during allocation. It does its own thing based on what it thinks is best for the situation. OK, it does listen to one thing. It listens only to the fact that there may be a limitation.

Files can be at *NOMAX or a zillion records, but the allocations as noted with the cities file can be miniscule. If you choose to live by the increment value, you have the opportunity to watch your files grow and research the reasons for excessive growth.

When you run out of increments, the system stops and makes you take notice that a file has gotten larger than you estimated it would ever get. You can then proceed with another increment and the system will halt again when that increment is exhausted.

If you run out of space once, you will get a message on a file that you would and should re-estimate the size of the file and change the physical file with the proper values (CHGPF) so that when this new estimate is exceeded, you are again forced to take notice that a file is growing in an unanticipated fashion.

So Much for Limitation. How about Allocation?

Setting the value at 1,000,000 for the initial number of records starts the limitation for the allocation, but it allocates nothing until you begin to fill the file. Then, when the file is filling, the system gets a clue as to how much is being added for real on a regular basis, and allocates more space if more data is being stored more frequently. Otherwise its allocations are small. The system only allocates a lot when you are adding a lot so it does not have to go through the expense of allocation on a record-by-record or block-by-block basis. When the file has lots of add activity, the system gets you a big wad of actual allocation as you are filling up the file. But if you are adding one record at a time, and not necessarily on the same day, the system just grabs a block of storage at a time--nothing close to the file's maximum capacity. Yes, it is pretty smart.

Dumbing Down the Auto Allocation

If you want the system to be dumb and you want to be the one who determines how much space is in the allocation, then you can use the ALLOCATE(*YES) parameter on the CRTPF or the CHGPF command. If you choose *YES, the system multiplies the number of records by the record length and adds the increment size multiplied by the number of increments by the record length, which fully allocates the file to its maximum size. The maximum for *NOMAX is 2147483646 per member so be careful. Most of us don't even know about ALLOCATE (*YES) because the default is ALLOCATE(*NO). Unless you have a good reason, don't even think about using it.

Unless you see the potential for scads and scads of records to be added to a file by a runaway program or some welcome Web activity, *NOMAX won't hurt you or IBM would never have selected *NOMAX as its default for SQL. Personally, I like plugging in a real value instead of *NOMAX because you probably do want to be warned that the file size is approaching your estimate. But, make sure your estimate is a few years larger than you ever think the file will become--maybe 10 years larger--because you don't really want to be answering those messages by setting the limits too tight.

So, Genevieve maybe we should really be talking about how you got the triggers working and how the EOF Wait actually added to the design of this application. But, in the meantime, I hope this explanation of the *NOMAX parameter helps you know that why the problem occurred. Whether it is an error of commission or an error of omission, whether by a consultant or by yourself, errors, especially those that affect operations can be very serious. Thankfully your operating system, i5/OS is very forgiving--even if you had to extend the file an innumerable number of times.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot