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.
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_74/rzajq/rzajqserviceshealth.htm
|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
|15003||Maximum size of the data in a table partition||1,869,169,767,219|
|15104||Maximum number of variable-length segments||65,533|
|15400||Maximum *MAX4GB Index Size||4,294,967,296|
|15401||Maximum *MAX1TB Index Size||1,869,166,411,776|
|15403||Maximum Encoded Vector Index Size||2,199,023,255,552|
|19002||Maximum number of spooled files in the system and basic user ASPs||2,610,000|
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 is > 90% of the maximum.
For those instances, a single message will be sent to QSYSOPR for each object that has exceeded 90% of the 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 file exceeded 90% consumption within the last 24 hours. The sole determinant factor for sending the message are 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.
The alerting level defaults to 90%, but clients can optionally customize the alerting level, via global variables within SYSIBMADM.
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
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.
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.4||SF99704||1|
|IBM i 7.3||SF99703||
(Level 15 - TR6 timed enhancements)
|IBM i 7.2||SF99702||17|
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 over come limits to growth
- Identify the appropriate solution to over come limits to growth
- Formulate a plan that will over come 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 over coming limits to growth, and increasing scalability.
To learn more about engaging a Db2 for i subject matter expert, please contact:
IBM Systems Lab Services
12 April 2021