Guru: Keeping Track Of IBM i System Limits
January 17, 2022 Dawn May
Every complex system has limits, and computer systems are no different. During my work with clients over the years I’ve seen many shops push their systems as far as they can go until performance breaks down. The limits I encounter are generally numbers of things: How many objects can exist in a library? How many rows can be inserted into a table? How large can a file in the IFS be?
Typically, these limits are large and you may think you do not need to worry about them, especially after an upgrade. But as the workload on your system increases – whether it be number of users, the rate of transactions, workload peaks, etc., the need for understanding system limits also increases. Limits are also of concern if there is an unexpected run-away situation, such as starting large numbers of jobs or creating excessive spooled files.
IBM i system limits are documented in the Maximum Capacities section of the Knowledge Center for each release. Knowing that limits exist is one thing, but it is even more important to know if your system is approaching these limits. If your system reaches a limit, bad things start to happen. These bad things might be sort of bad where jobs fail, or they can be really bad, like a system outage.
To help understand how close you are to potentially hitting a system limit, IBM i ships with System Health Services. The operating system automatically tracks key limits (note that not all limits are tracked) and provides views you can query to review the peaks on your system.
Generally, there are three limits that I encounter on customer systems. If such a situation occurs, it results in urgent work to resolve the problem. These are examples of limit issues I see more often than you’d expect:
- Maximum number of jobs: I’ve seen this occur as a runaway situation of starting excessive numbers of jobs where the jobs generate job logs or have pending job logs. These job logs keep that job in the system.
- Maximum number of spooled files in the system ASP: Everyone seems to love their spooled files! But there is a hard limit of 2.6 million spooled files in the system ASP and things get ugly if you hit this limit. Job logs often contribute significantly to this problem. I worked with a recent account where the QDFTSVR job description was changed to LOG(4 0 *SECLVL). Don’t do this! With this logging level, every time a prestart job is reused, a job log is created when the job swaps user profiles. You can end up with huge numbers of job logs.
- Maximum number of rows in a table: Endlessly adding rows to a table but never deleting old records will ultimately result in hitting this limit. If you must save endless amounts of data, you need to have some sort of archival process in place.
The above three limits are examples of the type of limits that the system automatically tracks. The System Health Services feature provides a way to understand how close you are to potentially hitting these limits. Many more limits are tracked, but the three I outlined should concern IBM i all shops.
The limits are tracked in table QSYS2.SYSLIMTBL. Limits are logged based upon configuration values known as the floor and the increment. The floor is the lower level at which the system starts tracking the limit and the increment is how much that limit must increase (or decrease) before it is tracked in the SYSLIMTBL again. The floor and increments are documented for the limits that are tracked in the System Health Services section of the Knowledge Center. You can add AFTER INSERT or AFTER DELETE triggers to this table. This is one way to perform an action such as sending a notification when a limit is being logged. IBM’s documentation on the SYSLIMTBL has an example of using a trigger to check for the maximum number of rows in a partition.
Typically, you will not query the SYSLIMTBL, but rather the QSYS2.SYSLIMITS view. The SYSLIMITS view contains information about the system limits, along with other system information, the job that logged the limit, and if that job is still active, information about that job. There is also a QSYS2.SYSLIMITS_BASIC view that does not include information about the job that logged the limit in order to reduce performance overhead.
I like to use an IBM-supplied example to demonstrate how knowledge of system limits can be used. IBMer Scott Forstie drafted this SQL example when I asked him if it was possible to show the maximum number of jobs compared to the QMAXJOB system value. This is a critical comparison because, while the documented maximum number of jobs is 970,000, the QMAXJOB system value defines the actual maximum number of jobs for the partition.
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;
The above query returns results similar to the screen capture below. On the test system, the QMAXJOB system value was set to 970,000. If you have QMAXJOB set to a lower value, the results will use that setting. The results are ordered by the CURRENT_VALUE (i.e., JOB_COUNT column) to show the current number of jobs that was tracked. You could change the ORDER BY to be LAST_CHANGE_TIMESTAMP to see the consumption over time. The PERCENT_CONSUMED column makes it easy to understand how close the number of jobs got to the QMAXJOB system value.
IBM supplies several global variables that can be used to control how the system prunes the SYSLIMTBL table. You can control the pruning of the table by designating the number of days to keep a row, or by stipulating the number of rows to keep for each type of limit. You can change the value of any of these global variables to customize the pruning of SYSLIMTBL to meet your specific needs.
System Health Services features built-in alerting for a small number of these limits, along with global variables to define when the system should send the alert for these limits (the default of which is 90 percent of the maximum). The alert is simply a message, SQL7062, sent to the QSYSOPR message queue. The system only checks these threshold settings once a day, when Collection Services is cycled, so if you are concerned about timely notification, the option of using a trigger as described above would be better.
Also, a reminder that Run SQL Scripts has examples for all IBM i Services, including System Health Services.
Scott Forstie, the Db2 for i Business Architect who provided me with the example above, has a presentation available with more information about System Limits, including many examples of using triggers and SQL queries to review the system limit information.
RELATED STORY AND RESOURCES
Maximum Capacities (IBM)
System Health Services (IBM)