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.

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'.

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.
SCHEMA_NAME	OBJ_SCHEMA	VARCHAR(128) Nullable	The SQL schema name for this object. Contains the null value if there is no schema name.
OBJECT_NAME	OBJ_NAME	VARCHAR(128) Nullable	The SQL name for the object. Contains the null value if there is no object name or if an SQL name could not be returned.
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.
SQL_OBJECT_TYPE	SQLOBJTYPE	VARCHAR(9) Nullable	The 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.
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;