Q: I enjoyed your IFSDIR utility to read an IFS directory from SQL (article ID 54760, May 24, 2007). Is there a way to return the contents of the subdirectories? Right now, it shows only one directory at a time, but none of the subdirectories within it. I'd like to use it to calculate how much space is used by various areas of the IFS.
A: Yes, it's possible, but it does add a new level of complexity. In this article, I provide a new UDTF called IFSTREE that returns the contents of subdirectories as well. I also discuss the programming challenges of writing this tool and explain how I overcame them.
My IFSDIR tool is an external User-Defined Table Function (UDTF). When you call a UDTF, the SQL engine calls your service program in a loop. Your procedure is called once for every row of the resulting table that you want to return. With this in mind, the original IFSDIR tool would read one new directory entry each time it was called, and return the info from that entry.
This style of coding doesn't work so well when you have to process all of the subdirectories inside a given directory. Why not?
Normally, when you want to read an entire directory tree, you have two options. One is to call the opendir(), readdir(), and closedir() APIs to read each directory individually. In that scenario you usually implement recursive logic. Each time you read a directory entry that points to a new subdirectory, you call the procedure recursively. The new copy of the procedure creates a new call stack entry, and that entry processes the subdirectory. If any further subdirectories are found, additional call stack entries are added. But that doesn't work when you're expected to fetch just one entry and then exit, does it? If you exited your recursive procedure, how would you resume the reading the next time you're called? The recursive logic relies on your program controlling the flow of the loop, and controlling when the procedures end, it won't work if SQL is controlling the loop.
A faster way to read a directory recursively is to call the Qp0lProcessSubtree() API. However, it has the same problem. For every directory entry found, it calls a subprocedure. You'd need a way to stop the API so you could return that path back to SQL, and then you'd need a way to resume the API where it left off. Unfortunately, there is no way to do these things!
I came up with a few ideas of how I might solve these problems:
I could save the entire directory list to a temporary file on the first call, then return the entries of that file. I rejected this idea because it wouldn't perform well.
I could implement a stack of directory handles from the opendir() API, and use that stack to control the flow of my program. I rejected this idea because it'd be very complicated to write and debug.
I could submit a background job that would use the Qp0lProcessSubtree() API to get the file list, and my foreground job could read the results from the background job, one at a time.
I settled on option three because it performs relatively well, but is also relatively easy to code. The hardest part is enabling communication between the jobs. I thought about data queues and sockets as possible ways to communicate, but I decided to use a pipe instead, because it's simple and fast. I also decided to submit the job using the spawn() API since it makes it easy to connect the pipe.
Basically, the spawn() API submits a background job. Unlike the more traditional SBMJOB command, however, it lets you pass pipes, sockets and stream file descriptors to the new job. The job that it creates is considered a "child job" to the job that spawns it, and therefore it's under the control and supervision of the original job. This behavior is very useful in this circumstance because it lets you create the new job, connect a pipe to it, and wait for it to complete, all at the appropriate time.
To learn more about the pipe() and spawn() APIs, please see the following articles from previous issues of this newsletter:
Another problem with using a UDTF to calculate disk usage is that there's a difference between the amount of disk that's allocated to a file and the size of the file itself. You see, hard drives are divided into blocks, and any file must always be an even multiple of those blocks.
For example, if my system uses 8 KB blocks and I create a file that's only one byte long, my file size will be one byte. There will only be one byte that a program can read from the file. However, because the system stores the file in blocks, it still occupies at least one block, or 8 KB, of disk space. If the goal of my SQL statement is to know how much disk space is used, I want to ensure that I'm looking at the allocated size of the file rather than the size of the data in the file.
To make that possible, I've added a new field to the UDTF named alcsize that you can query to find out the amount of disk space allocated.
The final problem with my new UDTF had to do with the fact that SQL doesn't wait indefinitely for a UDTF to complete. By default, it will wait 30 seconds for the UDTF to return a row, and if it doesn't receive one in that amount of time, it will return an error.
Unfortunately, if you attempt to use the new UDTF on a large section of the IFS, it can take a long time to open because of the size of the directory tree. As a result, you have to increase the time out value, or the UDTF can fail.
In order to change the time out, it's necessary to create a modified QAQQINI file. Paul Conte described the process of creating your own QAQQINI file back in 2001 in the Club Tech System i DB2 UDB & SQL/400 Newsletter. You can read that article at the following link:
http://www.systeminetwork.com/article.cfm?id=11157
Once you've set up your QAQQINI file, you can change the timeout by running the following SQL statement:
UPDATE QUSRSYS.QAQQINI SET QQVAL='180'
QQTEXT='Allow IFSTREE to complete' WHERE
QQPARM='UDF_TIME_OUT'
In this example, I changed the timeout value to 180 seconds, which worked okay on my machine. If you have a bigger directory where it takes longer to open, you might have to tweak that number.
I decided to keep the old IFSDIR function as-is, and create a new UDTF called IFSTREE. I suspect that IFSDIR and IFSTREE will serve different purposes, so I didn't think it made sense to replace the old one.
IFSTREE works just like the IFSDIR function that I presented in the previous article, except that it returns a full path name in the first parameter, rather than only returning the object name, and as I already mentioned, it has an alcsize field so you can see how much DASD is allocated to the file.
As with the previous example, you run it from an ordinary SQL SELECT statement, and you can use all of the neat features of the SELECT statement such as order by, grouping, where, and so forth.
Here's a simple example that gives me the total disk space that's used by the /tmp directory of the IFS:
Select SUM(alcsize)
from TABLE(IFSTREE('/tmp')) as t
There's a slightly more complex example named IFSTREDEMO that's included in the code download for this article.
Click here to download the source members for the IFSTREE UDTF