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:
|ODOBSZ||Size in bytes|
|ODCRTU||Created by user|
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.