News
Abstract
IBM i will send messages to the QSYSOPR message queue for a subset of the System Limits support. These messages are intended to alert the IBM i system operator to a high level of consumption of an important system limit.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i - Services (SQL) > Tracking Important System Limits > Alerts for IBM i System Limits
This fact page is a resource to help IBM i clients understand the options available to help them to proactively manage instances of high consumption of a critical limit and avoid a potential outage.
Refer to the following topics:
The IBM i operating system is comprised of many products and components. As an integrated operating system, not only do the products and components frequently rely upon each other, but common building blocks and resources are used. Some of the resources are deemed to be critical because their proper use and consumption is directly related to achieving continued, normal operational behavior. The repository for this tracking lies within Db2 for i.
A table, a view, and global variables combine to provide information about limits on your system. Information about the important limits is logged in a Db2 for i supplied table named QSYS2.SYSLIMTBL. The QSYS2.SYSLIMITS view uses SYSLIMTBL and other Db2 resources to provide extended and formatted detail about these limits. You should generally work with the view rather than the underlying table. You can use Db2 for i provided global variables to control the number of rows kept for each type of limit in SYSLIMTBL.
The documentation for this capability is found here: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_75/rzajq/rzajqserviceshealth.htm
The IBM i operating system includes alerts for a subset of System Limits tracking. Once per day, IBM i will look for new occurrences where consumption of some limits exceeded the alerting level, generally 90% of the maximum allowable size. For these instances of extremely high consumption, messages will be sent to the QSYSOPR system operator message queue.
| Limit ID | Limit Description | Maximum | Alerting Level | Alerting Cadence |
| 15000 | Maximum number of all rows in a partition | 4,294,967,288 | Greater than 90% | Once per day |
| 15002 | Maximum number of deleted rows in a partition | 4,294,967,288 | Greater than 50% | |
| 15003 | Maximum size of the data in a table partition | 1,869,169,767,219 | Greater than 90% | |
| 15104 | Maximum number of variable-length segments | 65,533 | Greater than 90% | |
| 15400 | Maximum *MAX4GB Index Size | 4,294,967,296 | Greater than 90% | |
| 15401 | Maximum *MAX1TB Index Size | 1,869,166,411,776 | Greater than 90% | |
| 15403 | Maximum Encoded Vector Index Size | 2,199,023,255,552 | Greater than 90% | |
| 19002 | Maximum number of spooled files in the system and basic user ASPs | 2,610,000 | Greater than 90% |
When collection services is recycled (typically just past midnight), a call is made to the QSYS2.Process_System_Limits_Alerts() procedure. This procedure will look in the System Limits table for objects that have changed in the last 24 hours where the current resource consumption level exceeded the alerting level. For those instances, a single message will be sent to QSYSOPR for each object that has exceeded its maximum.
The message is a Severity 80 with message ID SQL7062.
The message will be sent if there are any rows within the QSYS2.SYSLIMTBL table which indicate that the limit exceeded its consumption level within the last 24 hours. The sole determinant factor for sending the message is the existence of rows within the QSYS2.SYSLIMTBL table with the criteria previously mentioned. The message is sent to raise awareness and enable proactive systems management.
Most alerting levels default to 90%. Clients can customize each alerting level using global variables defined within the SYSIBMADM schema.
Example alerting customization:
--
-- Alert on variable length segment consumption at 75% consumed
--
CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSTEM_LIMITS_ALERT_15104_PERCENTAGE
FOR SYSTEM NAME QIBM_15104
INTEGER
DEFAULT 75;
The following is an example of the SQL7062 message being sent to QSYSOPR.
MYLIB/MYTABLE *FILE HAS CONSUMED MORE THAN 90% OF THE LIMIT: 15000-MAXIMUM NUMBER OF ALL ROWS (4008420999 OF 4294967288=93.33%). REFER TO ibm.biz/DB2foriAlerts FOR MORE DETAIL.
or...
.jpg)
The Alert support for System Limits was established with the following Db2 PTF Group.
| IBM i Operating System release | Db2 PTF Group | Enabling Level |
| IBM i 7.5 | SF99950 | 1 |
| IBM i 7.4 | SF99704 | 1 |
| IBM i 7.3 | SF99703 |
5
(Level 15 - TR6 timed enhancements) |
| IBM i 7.2 | SF99702 | 17 |
See this IBM Documentation page for details: PROCESS_SYSTEM_LIMITS_ALERTS
Reaching an IBM i architectural limit will cause an unscheduled and potentially debilitating application outage due to the database object being unavailable to accept additional data. Actively monitoring the current size and growth rate of database objects is obviously a best practice; and taking advantage of the alerts can be a real advantage. Traditionally, monitoring the database behavior is the responsibility of the "database engineer".
(see http://db2fori.blogspot.com/2012/11/db2-for-i-database-engineer-description.html for more on the role of a DBE)
Upon receiving an alert, ideally, a high-priority project will be initiated. The main tasks of this project are:
- Review and assess the current environment
- Identify short-term and long-term trends for data growth
- Obtain knowledge on possible methods and strategies to overcome limits to growth
- Identify the appropriate solution to overcome limits to growth
- Formulate a plan that will overcome the limits to growth
The IBM Systems Lab Services team has Db2 for i subject matter experts available to assist with all aspects of very large database, including overcoming limits to growth, and increasing scalability.
To learn more about engaging a Db2 for i subject matter expert, please contact:
Was this topic helpful?
Document Information
Modified date:
06 October 2023
UID
ibm11116795