Getting a List of All User Profiles and the Amount of Storage They Use

If the systems OS level is 7.2 or above you can use the new SQL function QSYS2.USER_STORAGE to get this information by running the following SQL statement:

SELECT *
    FROM QSYS2/USER_STORAGE

Or, you can use the older method by following these steps:

DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) OUTFILE(QTEMP/USERDB)

On the command line, type STRQRY to start the query. Then, select Option 1 to work with queries.

Complete the Work with Queries as follows:

 Option  . . . . . .   1          1=Create, 2=Change, 3=Copy, 4=Delete
                                  5=Display, 6=Print definition
                                  8=Run in batch, 9=Run
 Query . . . . . .  .   spcqry    Name, F4 for list
   Library . . . . .     QGPL     Name, *LIBL, F4 for list

Complete the Define the Query as follows:

Opt    Query Definition Option
 1     Specify file selections
       Define result fields
 1     Select and sequence fields
       Select records
 1     Select sort fields
       Select collating sequence
       Specify report column formatting
        Select report summary functions
       Define report breaks
 1     Select output type and output form
       Specify processing options

Press the Enter key, and the next few screens are shown in succession. If you make a mistake on one, once you are through all of them, you are shown the Define the Query screen again; select the screen that you need to change.

Complete the Specify File Selections as follows:

File . . . . . . . . .   userdb       Name, F4 for list
   Library  . . . . . .     QTEMP      Name, *LIBL, F4 for list
 Member . . . . . . . .   *FIRST       Name, *FIRST, F4 for list
 Format . . . . . . . .     *FIRST     Name, *FIRST, F4 for list

Complete the Select and Sequence Fields as follows:

Seq  Field          Text                          Len    Dec
  1   UPUPRF         User Profile Name              10
  2   UPMXSU         Storage used                   15     0
  3   UPTEXT         Text description               50


The UPMXSU field is on the second screen, and UPTEXT is on the third screen. UPTEXT is there if you put the name of the user in the Text field. After you have chosen them and pressed the Enter key, the system moves the chosen fields to the front of the list. Press the Enter key again, and proceed to the next screen.

On the Select Sort Fields screen, you can choose to sort by user:

Sort
Prty A/D  Field                   Text                    Len  Dec
1    A     UPUPRF          User Profile Name              10
 ___ _     UPMXSU          Storage used                   15   0
 ___ _     UPTEXT          Text description               50

or sort by highest storage to lowest storage:

Sort
Prty A/D  Field                  Text                 Len  Dec
___ _    UPUPRF         User Profile Name             10
  1  D   UPMXSU         Storage used                  15    0
___ _    UPTEXT         Text description              50


On the Select Output Type and Output Form screen, specify the following:

Output type  . . . . . . . . . . .   2    1=Display
                                            2=Printer
                                            3=Database file

  Form of output . . . . . . . . . .   1    1=Detail
                                            2=Summary only

  Line wrapping  . . . . . . . . . .   N    Y=Yes, N=No
    Wrapping width . . . . . . . . .        Blank, 1-378
    Record on one page . . . . . . .   N    Y=Yes, N=No

After filling in this screen, press F10 to return to the Define the Query screen. Make any additional changes. Press F3, and the following screen is shown:

Exit This Query

Type choices, press Enter.

  Save definition  . . .   Y            Y=Yes, N=No

  Run option . . . . . .   3            1=Run interactively
                                        2=Run in batch
                                        3=Do not run

For a saved definition:

     Query  . . . . . . .   SPCQRY       Name
     Library  . . . . .     QGPL         Name, F4 for list

    Text . . . . . . . .   List of users and their storage usage

    Authority  . . . . .                *LIBCRTAUT   *LIBCRTAUT, *CHANGE, *ALL,
                                        *EXCLUDE, *USE,
                                        authorization list name

Press the Enter key. The Work with Queries screen is shown. The report can now be generated.

                               Work with Queries

Type choices, press Enter.

  Option  . . . . . .     8       1=Create, 2=Change, 3=Copy, 4=Delete
                                  5=Display, 6=Print definition
                                  8=Run in batch, 9=Run
  Query . . . . . .   .   SPCQRY  Name, F4 for list
    Library . . . . .      QGPL   Name, *LIBL, F4 for list


This produces a spooled file with the space report. In the future, skip the query creation steps, re-create the user profile database, and run the existing query SPCQRY.