USER_STORAGE view

Edit online

The USER_STORAGE view contains details about storage Start of change and authority entries End of change for user profiles.

Start of change Every user profile contains information used for managing ownership and authority of objects. This view returns information about consumption of these authority-related entries, including different categories of usage. End of change

The information returned is similar to the detail from the Retrieve User Information (QSYRUSRI) API.

Information is broken down by SYSBAS and independent auxiliary storage pool (IASP). To see information for IASPs, the IASP must be varied on.

Authorization: The caller must have *OBJOPR and *READ authorities to the *USRPRF.

The following table describes the columns in the view. The system name is USER_STG. The schema is QSYS2.

Table 1. USER_STORAGE view
Column Name	System Column Name	Data Type	Description
AUTHORIZATION_NAME	USER_NAME	VARCHAR(10)	User profile name.
ASPGRP	ASPGRP	VARCHAR(10)	Name of the independent ASP or *SYSBAS.
MAXIMUM_STORAGE_ALLOWED	MAXSTG	BIGINT Nullable	The maximum amount of auxiliary storage, in kilobytes, that can be assigned to store permanent objects owned by the user. Contains the null value if the user does not have a maximum amount of allowed storage.
STORAGE_USED	STGUSED	BIGINT	The amount of auxiliary storage, in kilobytes, occupied by the user's owned objects for this ASP group.
AVAILABLE_PROFILE_ENTRIES	AVAIL_ENT	BIGINT	The number of authority-related entries available to the user for this ASP group.
TOTAL_PROFILE_ENTRIES	TOTAL_ENT	BIGINT	The total number of authority-related entries used by the user for this ASP group. This is the total of owner, private authority, authorized user, and primary group entries used.
OWNER_ENTRIES	OWN_ENT	BIGINT	The number of owner entries used by the user for this ASP group. This is the number of entries for objects for which this user is the owner.
PRIVATE_AUTHORITY_ENTRIES	PRIV_ENT	BIGINT	The number of private authority entries used by the user for this ASP group. This is the number of entries for objects for which this user has a private authority.
AUTHORIZED_USER_ENTRIES	USER_ENT	BIGINT	The number of authorized user entries used by the user for this ASP group. This is the number of entries for other users that have private authorities to objects for which this user is the owner.
PRIMARY_GROUP_ENTRIES	PGP_ENT	BIGINT	The number of primary group entries used by the user for this ASP group. This is the number of entries for objects for which this user is the primary group.
TEMP_AVAILABLE_PROFILE_ ENTRIES	AVAIL_TEMP	BIGINT Nullable	The number of authority entries available to the user for temporary objects in a user-defined file system (UDFS). Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.
TEMP_TOTAL_PROFILE_ENTRIES	TOTAL_TEMP	BIGINT Nullable	The total number of authority entries used by the user for temporary objects in a UDFS. This is the total of owner, private authority, authorized user, and primary group entries used. Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.
TEMP_OWNER_ENTRIES	OWN_TEMP	BIGINT Nullable	The number of owner entries used by the user for temporary objects in a UDFS. This is the number of entries for objects for which this user is the owner. Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.
TEMP_PRIVATE_AUTHORITY_ ENTRIES	PRIV_TEMP	BIGINT Nullable	The number of private authority entries used by the user for temporary objects in a UDFS. This is the number of entries for objects for which this user has a private authority. Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.
TEMP_AUTHORIZED_USER_ENTRIES	USER_TEMP	BIGINT Nullable	The number of authorized user entries used by the user for temporary objects in a UDFS. This is the number of entries for other users that have private authorities to objects for which this user is the owner. Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.
TEMP_PRIMARY_GROUP_ENTRIES	PGP_TEMP	BIGINT Nullable	The number of primary group entries used by the user for temporary objects in a UDFS. This is the number of entries for objects for which this user is the primary group. Contains the null value if ASPGRP is not *SYSBAS or if the user has no UDFS temporary authority entries.

Examples

Determine how much storage user SCOTTF has consumed.

SELECT * FROM QSYS2.USER_STORAGE
  WHERE USER_NAME = 'SCOTTF';

Find user profiles that have reached at least 80% of their authority-related entry consumption.

WITH HIGH_USAGE_PROFILES AS (
     SELECT AUTHORIZATION_NAME, DECIMAL(
                   (DECIMAL(AVAILABLE_PROFILE_ENTRIES,21,2)/
                      (DECIMAL(AVAILABLE_PROFILE_ENTRIES,21,2) + 
                       DECIMAL(TOTAL_PROFILE_ENTRIES,21,2))) *100 ,5,2) AS PERCENT_USED 
        FROM QSYS2.USER_STORAGE)
SELECT AUTHORIZATION_NAME, PERCENT_USED FROM HIGH_USAGE_PROFILES
  WHERE PERCENT_USED >= 80;