Discovering how much personal storage space remains

This post is based upon something I made for myself, and after discussing it with some friends they said it would be worthy of becoming a post as others have the same need.

All of the code shown on this blog is written on servers generously provided by the IBM i hosting provider RZKH. Their hosting package gives me a fixed amount of storage, disk space, which I can use. While I am a careful user, who deletes all objects when I have finished with them and regularly clears my output queue, I want an easy way to see the following:

1. How much storage space was I allocated when my profile was created?
2. How much space have I used?
3. How much space do I have left I can use?
4. What percentage of my allocated space have I used? If I reach a certain percentage I know I need to start deleting and clearing immediately

How is the maximum amount of storage allocated for a user? It is done when either their user profile is created or changed.

CRTUSRPRF USRPRF(TestUser) MAXSTG(250000)

I find the answer to the first two questions on the third page of the Display User Profile command, DSPUSRPRF.

Display User Profile - Basic User profile . . . . . . . . . . . . . . . : SIMON Storage information: Maximum storage allowed . . . . . . . . : 250000 Storage used . . . . . . . . . . . . . . : 10296 Storage used on independent ASP . . . . : *NO

But I wanted a way to display all of the answers to my questions without needing to use a calculator. Fortunately there is a DB2 of i (SQL) catalog I can use for this. The USER_STORAGE view gives details about the storage by user profile. Depending upon your authority you may see just yourself or you may see everyone on the IBM i system you are using. I am going to build my own view over this one to provide me with the answers to my questions.

USER_STORAGE view contains four columns:

Column name	Description
AUTHORIZATION_NAME	User profile
ASPGRP	Name of the independent ASP group, or if it is not in an independent ASP group *SYSBAS
MAXIMUM_STORAGE_ALLOWED	Maximum amount of storage that can be used for permanent objects, in kilobytes. Objects in QTEMP are not considered permanent. Contains null if no maximum storage was given for this user.
STORAGE_USED	Amount of storage used, in kilobytes

I am going to create two derived columns for my view:

1. Amount of free space available
2. Percentage of space used

Let me start with the code for this new view of the existing view.

01 CREATE OR REPLACE VIEW MYLIB.AVAILSPACE ( 02 USER_NAME FOR "USER", 03 ASP_GROUP FOR "ASPGRP", 04 MAXIMUM_STORAGE FOR "MAXSTG", 05 STORAGE_USED FOR "STGUSED", 06 FREE_SPACE FOR "FREESPACE", 07 PERCENTAGE_USED FOR "PCTUSED" 08 ) 09 AS SELECT AUTHORIZATION_NAME, 10 ASPGRP, 11 CAST(MAXIMUM_STORAGE_ALLOWED AS DECIMAL(6,0)), 12 CAST(STORAGE_USED AS DECIMAL(6,0)), 13 CAST(MAXIMUM_STORAGE_ALLOWED - STORAGE_USED AS DECIMAL(6,0)), 14 CAST((STORAGE_USED * 100 / MAXIMUM_STORAGE_ALLOWED) AS VARCHAR(3)) CONCAT '%' 15 FROM QSYS2.USER_STORAGE 16 WHERE AUTHORIZATION_NAME = 'SIMON' ;

Line 1: As I am working upon an IBM i that is running version 7.3 I can use the CREATE OR REPLACE. If you are on a version of IBM i that is 7.1 or less, and I believe some of the earlier TRs of 7.2, you have to replace the CREATE OR REPLACE with first a DROP VIEW and then a CREATE VIEW.

Lines 2 – 7: These are the definitions of the names of the columns/fields that will be in my view. I am defining the long, SQL, name of the column first followed by the short name.

Line 8: The parenthesis marks the end of the column definitions.

Lines 9 – 17: These describe what data is going to be placed in the view's columns.

Lines 11 and 12: I am using the CAST to convert the type of column from BIGINT to DECIMAL(6,0). The reason I have done this as my account gives me a maximum of 250,000 kilobytes. Why would I need to show a number bigger than that?

Line 13: This is the first of the derived columns. The calculation to create the value very simple and allows me to know how much storage space I have left I can use.

Line 14: I want to display a percentage of how much space I have remaining, that way I can quickly see how close I am to my limit and whether I need to do some serious clean up. I have calculated the percentage as a number and then converted it to a VARCHAR type so that I can append a percentage symbol at the end.

Line 15: I do need to define from which table or view I am getting this information from.

Line 16: And I only want to see my information.

The next part of my code gives the view and its columns recognizable names.

17 LABEL ON TABLE MYLIB.AVAILSPACE IS 'SQL view of available space for user' ; 18 LABEL ON COLUMN MYLIB.AVAILSPACE ( 19 USER_NAME IS 'User', 20 ASP_GROUP IS 'ASP group', 21 MAXIMUM_STORAGE IS 'Maximum storage', 22 STORAGE_USED IS 'Storage used', 23 FREE_SPACE IS 'Free space', 24 PERCENTAGE_USED IS 'Percentage used' 25 ) ; 26 LABEL ON COLUMN MYLIB.AVAILSPACE ( 27 USER_NAME TEXT IS 'User', 28 ASP_GROUP TEXT IS 'ASP group', 29 MAXIMUM_STORAGE TEXT IS 'Maximum storage', 30 STORAGE_USED TEXT IS 'Storage used', 31 FREE_SPACE TEXT IS 'Free space', 32 PERCENTAGE_USED TEXT IS 'Percentage used' 33 ) ;

Line 17: Even though this is a view, and not a table, I use the LABEL ON TABLE to give the view object the description I desire.

Lines 18 – 25: This may look strange to those of you who have not seen this LABEL ON COLUMN statement before. This defines the equivalent of the DDS files' column headings for the columns in the view. And, yes, you do need those spaces so that this text will be separated into the equivalent of the first and second lines of column headings.

Lines 26 – 33: This is where I define the equivalent of the text in DDS files.

I then create my view and I can see it in MYLIB.

Work with Objects Using PDM Library . . . . . MYLIB Opt Object Type Attribute Text AVAILSPACE *FILE LF SQL view of available space for user

To view the contents of the view I can either use the SQL statement:

SELECT * FROM MYLIB.AVAILSPACE

Or I can just use the Run Query command, RUNQRY:

RUNQRY *NONE MYLIB/AVAILSPACE

Both display the same results:

User ASP Maximum Storage Free Percentage group storage used space used SIMON *SYSBAS 250,000 10,296 239,704 4%

I now know that after my last clean up I have plenty of room left for my account.

If I create the same view on an IBM i I do not have a restriction on my maximum storage it would look like:

User ASP Maximum Storage Free Percentage group storage used space used SIMON *SYSBAS - 1,179,108 - -

The hyphens ( -) indicate a null value, as there is no restriction on the maximum storage I can use.

 

You can learn more about the USER_STORAGE view from the IBM website here.

 

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