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;