Wednesday, June 24, 2015

Select the number of rows from SQL's result set

sql select with fetch rows only dspobjd

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
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:

02             DETAIL(*FULL) OUTPUT(*OUTFILE) +
03             OUTFILE(QTEMP/@DSPOBJD)

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.


  1. Good morning Simon,

    Check out the DB2 for i Services. These primarily QSYS2 tables provide a wealth of system information without requiring preceding DSP* or RTV* commands.

    HTH, *bg

  2. This one sounds good as well


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.