Select the number of rows from SQL's result set

There are times when I do not want all of the rows/records from a table/file just, for example, the 100 with the largest value in a variable. I could use a "Copy File" command, CPYF, to copy on the first 100, but I cannot sort with a CPYF to select the largest. Fortunately SQL's Select does offer me a way to do this.

Today did not start well as the development server reached critical storage, 93%+ of DASD was being used. After doing some basic deleting and purging of a few files I was not able to lower the percentage of DASD more than 1%, which was not enough. What I need to do was to identify the largest object on this IBM i and determine if they could be deleted or their contents purged. But how to find those big objects?

The "Display object description" command, DSPOBJD, will give me a list of all the objects with their sizes and who created them. This was going to be a really big file, so I would not want to keep it for long in this situation. What I wanted was to extract the 250 biggest objects from the DSPOBJD output. I would then share the list with my colleagues and we would delete and purge to free up DASD.

SQL's Select offers us a way to do this with its FETCH FIRST 999 ROWS ONLY. I could make a table that would only contain the columns from DSPOBJD I was interested in, and then fetch the first 250 rows. I decided to use the SQL's CREATE TABLE statement to make the output table/file. I have described the CREATE TABLE statement in a previous post, see here, so I will not be going into too much detail about it here. All I am going to say is that I can use a Select statement within the Create Table to give which columns and rows, and what file I want to extract the data from.

The information I wanted from DSPOBJD for objects was:

Field name	Description
ODLBNM	Library
ODOBNM	Object
ODOBTP	Object type
ODOBAT	Object attribute
ODOBSZ	Size in bytes
ODCRTU	Created by user
ODOBTX	Object text

Let me show the relevant parts of my program here:

01 DSPOBJD OBJ(*ALLUSR/*ALL) OBJTYPE(*ALL) + 02 DETAIL(*FULL) OUTPUT(*OUTFILE) + 03 OUTFILE(QTEMP/@DSPOBJD) 04 RUNSQL SQL('CREATE TABLE MYLIB/BIGOBJS AS + 05 (SELECT ODLBNM,ODOBNM,ODOBTP,ODOBAT,ODOBSZ,+ 06 ODCRTU,ODOBTX + 07 FROM QTEMP/@DSPOBJD + 08 ORDER BY ODOBSZ DESC + 09 FETCH FIRST 250 ROWS ONLY) + 10 WITH DATA') + 11 COMMIT(*NONE)

Lines 1 – 3 is the DSPOBJD. I am looking for *ALL objects in the "user" libraries, *ALLUSR, in other words not IBM's libraries. The output is sent to a file, @DSPOBJD in QTEMP.

Lines 4 – 11 are the CREATE TABLE. My output file has the unoriginal name of BIGOBJS. In the Select statement I have selected the columns/fields I mentioned in the table above, from the DSPOBJD outfile. I have ordered the results by the object size in descending order (largest to smallest), line 8.

Line 9 contains the part of the statement which fetches the first 250 rows from the result set.

The DSPOBJD outfile is deleted when the job ends, and I am left with a table/file containg a list of the 250 largest objects. I worked this list and managed to reduce the amount of used DASD to just over 82%.

 

Is there another way to find the largest objects on an IBM i not using DSPOBJD? If you know of one use the Contact Form to let me know.

 

This article was written for IBM i 7.2, and should work for earlier releases too.