SYSLIMITS view

The SYSLIMITS view contains information about limits. This view is built on the QSYS.SYSLIMTBL table along with other system information. If a job is still active, the view contains information about the job that logged the limit.

Start of change

Authorization: For rows where the job is still active, the caller's user profile must be the same as the job user identity of the job for which the information is being returned, or must have *JOBCTL user special authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority. If the caller does not have sufficient authority, partial information is returned along with an SQL warning of '01548'.

For rows where LIMIT_TYPE = 'OBJECT', additional authorization is required:

  • If the user has *EXECUTE authority to the library, and both *OBJOPR and *READ authority to an object, full details are returned.
  • Otherwise, partial information is returned along with an SQL warning of '01548'.
End of change

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

Table 1. SYSLIMITS view
Column Name System Column Name Data Type Description
LAST_CHANGE_TIMESTAMP LASTCHG TIMESTAMP The timestamp when this row was last changed.
LIMIT_CATEGORY CATEGORY VARCHAR(15) The category for this limit.
  • DATABASE
  • JOURNAL
  • SECURITY
  • MISCELLANEOUS
  • WORK MANAGEMENT
  • FILE SYSTEM
  • SAVE RESTORE
  • CLUSTER
  • COMMUNICATION
LIMIT_TYPE TYPE VARCHAR(7) The type of limit.
  • OBJECT
  • JOB
  • SYSTEM
  • ASP
SIZING_NAME SIZING_NAM VARCHAR(128) Name that corresponds to the sizing ID.
COMMENTS COMMENTS VARCHAR(2000)
Nullable
Description of the limit.
USER_NAME CURUSER VARCHAR(10) The name of the user in effect when this row was logged.
CURRENT_VALUE CURVAL BIGINT Reported value for this limit.
MAXIMUM_VALUE MAXVAL DECIMAL(21,0)
Nullable
Maximum value allowed for this limit.
JOB_NAME JOB_NAME VARCHAR(28) The name of the job when this row was logged.

Contains the null value if the job is no longer active.

JOB_STATUS JOB_STATUS CHAR(10)
Nullable
Status of the job.

Contains the null value if the job is no longer active.

ACTIVE_JOB_STATUS AJSTATUS CHAR(4)
Nullable
The active status of the initial thread of the job.

Contains the null value if the job is in transition or is no longer active.

RUN_PRIORITY RUNPRI INTEGER
Nullable
The highest run priority allowed for any thread within this job.

Contains the null value if the job is no longer active.

SBS_NAME SBS_NAME CHAR(10)
Nullable
Name of subsystem where job is running.

Contains the null value if the job is no longer active.

CPU_USED CPU_USED BIGINT
Nullable
The amount of CPU time (in milliseconds) that has been currently used by this job.

Contains the null value if the job is no longer active.

TEMP_STORAGE_USED_MB TEMPSTG INTEGER
Nullable
The amount of auxiliary storage (in megabytes) that is currently allocated to this job.

Contains the null value if the job is no longer active.

AUX_IO_REQUESTED AUXIO BIGINT
Nullable
The number of auxiliary I/O requests performed by the job across all routing steps. This includes both database and nondatabase paging.

Contains the null value if the job is no longer active.

PAGE_FAULTS PAGEFAULT BIGINT
Nullable
The number of times an active program referenced an address that was not in main storage during the current routing step of the specified job.

Contains the null value if the job is no longer active.

CLIENT_WRKSTNNAME CLIENTWRK CHAR(255)
Nullable
Value of the SQL CLIENT_WRKSTNNAME special register.

Contains the null value if the job is no longer active.

CLIENT_APPLNAME CLIENTAPP CHAR(255)
Nullable
Value of the SQL CLIENT_APPLNAME special register.

Contains the null value if the job is no longer active.

CLIENT_ACCTNG CLIENTACT CHAR(255)
Nullable
Value of the SQL CLIENT_ACCTNG special register.

Contains the null value if the job is no longer active.

CLIENT_PROGRAMID CLIENTPGM CHAR(255)
Nullable
Value of the SQL CLIENT_PROGRAMID special register.

Contains the null value if the job is no longer active.

CLIENT_USERID CLIENTUSER CHAR(255)
Nullable
Value of the SQL CLIENT_USERID special register.

Contains the null value if the job is no longer active.

SQL_STATEMENT_TEXT SQLSTMT VARCHAR(10000)
Nullable
Statement text of the last SQL statement to run or the SQL statement that is currently running.

Contains the null value if the job is no longer active.

Start of changeSCHEMA_NAMEEnd of change Start of changeOBJ_SCHEMAEnd of change Start of changeVARCHAR(128)
Nullable
End of change
Start of changeThe SQL schema name for this object.

Contains the null value if there is no schema name.

End of change
Start of changeOBJECT_NAMEEnd of change Start of changeOBJ_NAMEEnd of change Start of changeVARCHAR(128)
Nullable
End of change
Start of changeThe SQL name for the object.

Contains the null value if there is no object name or if an SQL name could not be returned.

End of change
SYSTEM_SCHEMA_NAME SYS_NAME VARCHAR(10)
Nullable
The library name for the object.

Contains the null value if there is no library name.

SYSTEM_OBJECT_NAME SYS_ONAME VARCHAR(30)
Nullable
The object name for this row.

Contains the null value if there is no object name.

SYSTEM_TABLE_MEMBER SYS_MNAME VARCHAR(10)
Nullable
The member name for an object limit specific to database members.

Contains the null value if this row is not for a member limit.

IFS_PATH_NAME PATHNAME DBCLOB(5000) CCSID 1200
Nullable
IFS path for the object.

Contains the null value if there is no path.

OBJECT_TYPE OBJTYPE VARCHAR(7)
Nullable
The IBM i object type when an object name has been logged in the SYSTEM_SCHEMA_NAME and SYSTEM_OBJECT_NAME columns.

Contains the null value when no object name is specified.

Start of changeSQL_OBJECT_TYPEEnd of change Start of changeSQLOBJTYPEEnd of change Start of changeVARCHAR(9)
Nullable
End of change
Start of changeThe SQL type of the object when an object name has been logged in the SYSTEM_SCHEMA_NAME and SYSTEM_OBJECT_NAME columns. Values are:
  • ALIAS
  • FUNCTION
  • INDEX
  • PACKAGE
  • PROCEDURE
  • ROUTINE
  • SEQUENCE
  • TABLE
  • TRIGGER
  • TYPE
  • VARIABLE
  • VIEW
  • XSR

Contains the null value if the object is not an SQL object or when no object name is specified.

End of change
ASP_NUMBER ASPNUM SMALLINT
Nullable
Contains the ASP number related to this row.

Contains the null value if there is no ASP number.

LIMIT_ID LIMIT_ID INTEGER Unique identifier for this limit. Values are maintained in the SIZING_ID column in the QSYS2.SQL_SIZING table.

Examples

  • Find the 50 largest IFS stream files. Remove any duplicates from the result. Note that only stream files that have reached the documented floor and increment values will appear in SYSLIMITS.
    SELECT IFS_PATH_NAME, MAX(CURRENT_VALUE) AS MAX_BYTE_SIZE
      FROM QSYS2.SYSLIMITS 
      WHERE LIMIT_ID = 18409 
      GROUP BY IFS_PATH_NAME
      ORDER BY MAX_BYTE_SIZE DESC LIMIT 50;
  • Review the consumption of the 'Total number of jobs', relative to the QMAXJOB system value.
    WITH TT(JOB_MAXIMUM)
      AS (SELECT CURRENT_NUMERIC_VALUE
            FROM QSYS2.SYSTEM_VALUE_INFO
            WHERE SYSTEM_VALUE_NAME = 'QMAXJOB')
    SELECT LAST_CHANGE_TIMESTAMP AS INCREMENT_TIME, CURRENT_VALUE AS JOB_COUNT, 
         TT.JOB_MAXIMUM, 
         DEC(DEC(CURRENT_VALUE,19,2) / DEC(TT.JOB_MAXIMUM,19,2) * 100,19,2) 
             AS PERCENT_CONSUMED
        FROM QSYS2.SYSLIMITS, TT
        WHERE LIMIT_ID = 19000 ORDER BY CURRENT_VALUE DESC;