SAP on IBM i – Update week 33, 2017: Alerts for IBM i System Limits
As your business is progressing and growing, the amount of data to keep in the database tables of your SAP system is growing as well. Like any other database, Db2 for IBM i has several technical limits that cannot be exceeded, among them the maximum number of rows in a non-partitioned table or a table partition, the maximum size of an index or the maximum amount of variable-length data. A complete list of database limits can be found in the section SQL Limits of the SQL Reference information in the IBM Knowledge Center for IBM i.
There are different ways to monitor database growth in your SAP system: You can use the SAP transactions DB02 or DBACockpit to display the largest tables or tables with the most rows in your database, or you can look at the system catalog view QSYS2/SYSLIMITS to get information about objects that are approaching a system limit. In our blog entry from January 16th, 2016, we have explained how to look at the system limits view through transaction DBACockpit.
However, all these methods require a regular check of object sizes and limits. So far there has been no alerting when critical limits were approached. With the latest set of Db2 PTF groups for IBM i (SF99701 level 42 for IBM i 7.1, SF99702 level 17 for IBM i 7.2 or SF99703 level 5 for IBM i 7.3), alerting for some system limits was introduced. When these PTF groups are installed, a message will be sent to the QSYSOPR message queue when the number of rows in a non-partitioned table or table partition exceeds 90% of the maximum number, when an index size exceeds 90% of the maximum index size, or when the number of storage segments for variable-length data (as used in VARGRAPHIC, BLOB, or DBCLOB columns) exceeds 90% of the maximum value. The message will be repeatedly sent once per day until the problem is resolved. A detailed description about the new alerting can be found at http://ibm.biz/DB2foriAlerts.
When a limit is approaching, you should look for a solution as soon as possible. Once the limit is reached, you can no longer insert data into the affected table. The solution depends on the application and table that is affected. The first approach should always be data reduction, for example by archiving and deleting data. If archiving is not an option, you can also consider table partitioning. For SAP Business Warehouse (BW) systems, table partitioning is described in SAP Note 815186. For non-BW systems, table partitioning is described in SAP Note 2187681.