SAP on IBM i: Maximum number of objects in a database schema – do I need to worry?

People that are familiar with other applications are usually impressed by the number of tables, views and indexes in the main database schema of an SAP ERP system when they see it for the first time. Depending on the application mix and add-ons, you can easily have more than 100,000 objects in an SQL schema. With these numbers, you may wonder if you have to worry about any database limit. Up to IBM i 7.2, the documented maximum number of objects in a schema was “approximately 360,000”, since IBM i 7.3, the limit is documented as “approximately 1,000,000”. Plenty of room left, you may think.

Unfortunately, there is a little caveat in the term “approximately”: Each SQL schema (or library at the operating system level) comes with an object information repository (OIR) that contains references to tables, views and indexes used by programs, service programs or SQL packages in that SQL schema. The OIR has a size limitation, and if that is reached, attempts to create new objects will fail with message CPD2120 (“Cannot add new objects to library…”), followed by SQL7032 or even SQL0901. The amount of space needed per object in the OIR depends on the number of references within the object, so it is impossible to predict the exact number of objects that can be handled by the OIR. Does it matter? Well, we have seen a few customers running into that limit when upgrading an SAP system on IBM i 7.2 using the near Zero Downtime Maintenance (nZDM) option. During the upgrade, a lot of trigger programs are created, making it easier to reach the limit. Another contributing factor may be fragmentation of the OIR: When objects are deleted and new objects are created, the system is only reusing the space occupied by the deleted objects if the new objects fit into the unused OIR space. Otherwise, the deleted space cannot be reused and stays unused.

Most customers do not need to worry about reaching the limit, and those with larger numbers of objects can take steps to avoid running into the limit. Obviously, when you are still at IBM i 7.2 or even IBM i 7.1, you should consider upgrading to IBM i 7.3 or higher before you start the SAP upgrade. The new limit of approximately 1,000,000 objects is less likely exceeded, so you may be fine. If you want to check your system before an SAP upgrade, you can execute the following SQL statement through a tool of you choice, such as STRSQL or IBM i Access Client Solutions:

SELECT DISTINCT SYSTEM_OBJECT_NAME,
                OBJECT_TYPE,
                CURRENT_VALUE,
                MAXIMUM_VALUE,
                LAST_CHANGE_TIMESTAMP
  FROM QSYS2.SYSLIMITS
 WHERE SIZING_NAME = 'MAXIMUM NUMBER OF OBJECT DESCRIPTION ENTRIES IN A LIBRARY'
   AND SYSTEM_OBJECT_NAME = 'R3<sid>DATA'
 ORDER BY LAST_CHANGE_TIMESTAMP DESC

If the query does not return any data, or if the returned “CURRENT_VALUE” is significantly smaller than the returned “MAXIMUM_VALUE”, you don’t need to worry. If the “CURRENT_VALUE” comes close to the “MAXIMUM_VALUE”, you have the following options to increase the difference between them:

1. On any OS level, call the API QEXCLNCI with the schema name as parameter to enable reusing the OIR space occupied by deleted entries. Note that the API is provided with option 5 of the operating system (“System/36 Environment”). This option is on your IBM i media and is included in the base OS licensing. The API is documented here: Using API QSSP/QEXCLNCI to Clear Up Unused Object Information Repository Entries (ibm.com). This is the easiest option.
2. If the API was not sufficient, Reclaim unused space in the OIR by executing RCLSTG (requires restricted state of the system) or by saving, deleting and restoring the database schema. Consider the number of SAP systems on your LPAR and how many of them are near the limit to know whether to use RCLSTG to clean everything or to use the save/restore procedure to reclaim space for the one OIR of interest.
3. If you are at IBM i 7.2 or below, upgrade to IBM i 7.3 or above. This may be preferable to option 2, given that it increases the limit significantly and may take less downtime than a full RCLSTG.
4. If none of the previous options is feasible for you, perform the planned upgrade without the nZDM option.

You can also find this information in SAP Note 3106299.