SYSLIMTBL table

The SYSLIMTBL table contains information about limits as they are being consumed. It is maintained by Db2® for i.

This table is not authorized or managed like a typical Db2 for i catalog. By default, all users have authority to view this table. If this table is removed or incompatibly altered, the IBM® i operating system will automatically recreate it. The SYSLIMTBL table is designed to have as small a footprint as possible.

You can add AFTER INSERT or AFTER DELETE triggers to this table. This allows you to perform an action such as sending a notification when a limit is being logged to the table.

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

Table 1. SYSLIMTBL table
Column Name	System Column Name	Data Type	Description
LAST_CHANGE_TIMESTAMP	LASTCHG	TIMESTAMP	The timestamp when this row was last changed.
LIMIT_CATEGORY	CATEGORY	SMALLINT	The category of this limit. 0 Database 1 Journal 2 Security 3 Miscellaneous 4 Work management 5 File system 6 Save/restore 7 Cluster 8 Communication
LIMIT_TYPE	LIMTYPE	SMALLINT	The type of limit. 1 Object 2 Job 3 System 4 ASP
LIMIT_ID	LIMIT_ID	INTEGER	Unique identifier for this limit. Values are maintained in the SIZING_ID column in the QSYS2.SQL_SIZING table.
JOB_NAME	JOB_NAME	VARCHAR(28)	The name of the job that reported the current value.
USER_NAME	CURUSER	VARCHAR(10)	The name of the user in effect when the current value was updated.
CURRENT_VALUE	CURVAL	BIGINT	Reported value for this limit.
SYSTEM_SCHEMA_NAME	SYS_NAME	VARCHAR(10) Nullable	The library name for the object. If no library name, contains the null value.
SYSTEM_OBJECT_NAME	SYS_ONAME	VARCHAR(30) Nullable	The object name for this row. If no object name, contains the null value.
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.
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.
ASP_NUMBER	ASPNUM	SMALLINT Nullable	Contains the ASP number related to this row. Contains the null value if there is no ASP number.
IFS_PATH_NAME	PATHNAME	DBCLOB(5000) CCSID 1200 Nullable	IFS path for the object. Contains the null value if there is no path.

Example

Add a trigger to QSYS2.SYSLIMTBL to send a message when any table is approaching the maximum size. The trigger will be fired when any row is inserted into SYSLIMTBL. Within the trigger, it checks for the LIMIT_ID indicating the maximum number of rows in a partition (15000) and the value when you want to be notified.

/* Force any pseudo closed cursors over SYSLIMTBL to be closed */
CL: ALCOBJ OBJ((QSYS2/SYSLIMTBL *FILE *EXCL)) CONFLICT(*RQSRLS) ;
CL: DLCOBJ OBJ((QSYS2/SYSLIMTBL *FILE *EXCL));

CREATE OR REPLACE TRIGGER MYLIB.SYSTEM_LIMITS_LARGE_FILE
  AFTER INSERT ON QSYS2.SYSLIMTBL
  REFERENCING NEW AS N FOR EACH ROW MODE DB2ROW
  SET OPTION USRPRF=*OWNER, DYNUSRPRF=*OWNER
  BEGIN ATOMIC
    DECLARE V_CMDSTMT VARCHAR(200) ;
    DECLARE "ERROR" INTEGER;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET "ERROR" = 1;
    /* ------------------------------------------------------------------*/
    /* If a table is nearing the maximum size, alert the operator        */
    /* ------------------------------------------------------------------*/
    IF (N.LIMIT_ID = 15000 AND
        N.CURRENT_VALUE > 3000000000) THEN
      SET V_CMDSTMT = 'SNDMSG MSG(''Table: '
        CONCAT N.SYSTEM_SCHEMA_NAME CONCAT '/' CONCAT N.SYSTEM_OBJECT_NAME
        CONCAT ' (' CONCAT N.SYSTEM_TABLE_MEMBER CONCAT
        ') IS GETTING VERY LARGE - ROW COUNT = '
        CONCAT CURRENT_VALUE CONCAT ' '') TOUSR(*SYSOPR) MSGTYPE(*INFO) ';
      CALL QSYS2.QCMDEXC( V_CMDSTMT );
    END IF;
  END;