USER_STORAGE view

The USER_STORAGE view contains details about storage and authority entries for user profiles.

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.

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;