IBM Support

Alerts for IBM i System Limits

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:


IBM i System Limits tracking: 
For the most important system resources, the IBM i operating system automatically tracks the highest consumption and consumers.

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.


Alerting for System Limits

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 (7.4 & onward) 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 (7.4 & earlier) 1,869,166,411,776 Greater than 90%
15403 Maximum Encoded Vector Index Size 2,199,023,255,552 Greater than 90%
15410  Max Size of Index with 8K logical page size (7.5 & onward) 4,398,046,511,104 Greater than 90%
15411 Max Size of Index with 16K logical page size (7.5 & onward) 8,796,093,022,208 Greater than 90%
15412 Max Size of Index with 32K logical page size (7.5 & onward) 17,000,000,000,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.

System limits alerting controls

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...

Alerts for some System Limits

Alerts for some System Limits

Alerts for some System Limits


Required PTF levels for Alert support

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
User-initiated alerts
System limits alerts, by default, are sent when Collection Services recycles, which is typically once per day just past midnight.
The QSYS2.PROCESS_SYSTEM_LIMITS_ALERTS() procedure can be called anytime that a client would like to have alerts processed for the previous 24 hours.
In other words, this procedure allows a client to establish a cadence more frequently than daily for important System Limits alerting.
See this IBM Documentation page for details: PROCESS_SYSTEM_LIMITS_ALERTS

Assistance with overcoming limits to growth

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 https://db2ibmi.blogspot.com/2022/02/db2-for-i-dbe-duties.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 Technology Expert Labs 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 to assist with addressing limits, please contact:

John Westcott
email: jWestcott@ibm.com

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m3p000000LRYDAA4","label":"IBM i Db2-\u003ESYSLIMITs"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0;and future releases"}]

Document Information

Modified date:
09 September 2024

UID

ibm11116795