Being proactive about system sizings

There are a couple of SQL views that keep track of objects exceeding the maximum system sizes. I can use these to be proactive, capture information about objects reaching their maximums, before it happens.

In this example I want to show any object whose sizing limit has reached a value of 75% or more.

The Views I could use are:

• SYSLIMITS:  Full version of the View. You need *JOBCTL authority, or QIBM_DB_SQLADM or QIBM_DB_SYSMON function usages. If the Limit Type is "OBJECT" then you will need *EXECUTE to the library and *OBJOPR and *READ authority to the object.
• SYSLIMITS_BASIC:  Requires less authority than SYSLIMITS, and as it has less columns the results are returned faster.

The information I will be using is found in both Views, and I will be using SYSLIMITS_BASIC.

What kind of information can we get from this View?

I can just list the different types of Sizing names:

01 SELECT DISTINCT SIZING_NAME FROM QSYS2.SYSLIMITS_BASIC ORDER BY 1

I am using a Select Distinct to select only one of each name.

The results show me what types of sizing information is present:

SIZING_NAME ------------------------------------------------------------------------ MAXIMUM NUMBER OF ALL ROWS MAXIMUM NUMBER OF BYTES IN A STREAM FILE MAXIMUM NUMBER OF DELETED ROWS MAXIMUM NUMBER OF FILE SYSTEM OBJECTS IN THE SYSTEM AND BASIC USER ASPS MAXIMUM NUMBER OF INDEXES OVER A PARTITION MAXIMUM NUMBER OF MEMBERS MAXIMUM NUMBER OF OBJECT DESCRIPTION ENTRIES IN A LIBRARY MAXIMUM NUMBER OF OVERFLOW ROWS MAXIMUM NUMBER OF VALID ROWS MAXIMUM NUMBER OF VARIABLE LENGTH SEGMENTS MAXIMUM SEQUENCE NUMBER FOR A *MAXOPT1 OR *MAXOPT2 JOURNAL MAXIMUM TABLE PARTITION SIZE

How can I determine if something is getting close to its maximum. The View contains two fields we can use:

1. CURRENT_VALUE:  The current value for this limit
2. MAXIMUM_VALUE:  The maximum value for this limit

Using these I can calculate the percentage used for the limit with:

DEC(ROUND(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),2),5,2)

I am using a couple of scalar functions to make the result easier to understand.

The result of the calculation goes to 12 decimal places. I can understand the need for two, but not twelve.

SELECT (CURRENT_VALUE * 100) / MAXIMUM_VALUE FROM QSYS2.SYSLIMITS_BASIC 00001 -------------- 0.232830644122

I am going to round this number to two decimal places using the ROUND scalar function:

SELECT ROUND((CURRENT_VALUE * 100) / MAXIMUM_VALUE,2) FROM QSYS2.SYSLIMITS_BASIC 00001 -------------- 0.230000000000

To remove the trailing zeroes I will use the DEC scalar function to define the result as five long with two decimal places:

SELECT DEC(ROUND(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),2),5,2) FROM QSYS2.SYSLIMITS_BASIC 00001 -------------- 0.23

With the above I can use that as the sort field to order results by the calculated number. The whole SQL statement I generated was:

01 SELECT SIZING_NAME, 02 CURRENT_VALUE, 03 MAXIMUM_VALUE, 04 DEC(ROUND(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),2),5,2) AS "% used", 05 SYSTEM_SCHEMA_NAME AS "Library", 06 SYSTEM_OBJECT_NAME AS "Object", 07 SYSTEM_TABLE_MEMBER AS "Member", 08 LAST_CHANGE_TIMESTAMP 09 FROM QSYS2.SYSLIMITS_BASIC 10 WHERE LIMIT_CATEGORY = 'DATABASE' 11 ORDER BY "% used" DESC 12 LIMIT 5

Lines 1 – 8: These are the columns of information I want in my results, including the percentage I calculated, which I have called "% used".

Line 10: For this example, I just want to restrict the results to database limits.

Line 11: And I want to order the results by the calculated column in descending order.

Line 12: And return only five results.

These are the results:

CURRENT MAXIMUM SIZING NAME _VALUE _VALUE % used Library File Member LAST_CHANGE_TIMESTAMP ------------------------- ------- ------- ------ ------- ------ ------ -------------------------- MAXIMUM NUMBER OF MEMBERS 30,000 32,767 91.56 REGLIB MMPF <NULL> 2026-08-10 12:32:05.622145 MAXIMUM NUMBER OF MEMBERS 29,950 32,767 91.40 REGLIB MMPF <NULL> 2026-08-10 12:32:05.121220 MAXIMUM NUMBER OF MEMBERS 29,900 32,767 91.25 REGLIB MMPF <NULL> 2026-08-10 12:32:04.690789 MAXIMUM NUMBER OF MEMBERS 29,850 32,767 91.10 REGLIB MMPF <NULL> 2026-08-10 12:32:04.216803 MAXIMUM NUMBER OF MEMBERS 29,800 32,767 90.95 REGLIB MMPF <NULL> 2026-08-10 12:32:03.780424

Now I see a problem. The results are all for the same file. Every time it crossed a sizing threshold a row was written to the Table that SYSLIMITS is built over. I only want the most recent row, and will ignore the rest. I am sure there are several ways of doing this, I went and used the ROW_NUMBER scalar function:

01 SELECT SIZING_NAME, 02 CURRENT_VALUE, 03 MAXIMUM_VALUE, 04 DEC(ROUND(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),2),5,2) AS "% used", 05 SYSTEM_SCHEMA_NAME AS "Library", 06 SYSTEM_OBJECT_NAME AS "Object", 07 SYSTEM_TABLE_MEMBER AS "Member", 08 LAST_CHANGE_TIMESTAMP 09 ROW_NUMBER() OVER (PARTITION BY SYSTEM_SCHEMA_NAME,SYSTEM_OBJECT_NAME 10 ORDER BY LAST_CHANGE_TIMESTAMP DESC) 11 AS "ROW_NBR" 12 FROM QSYS2.SYSLIMITS_BASIC 13 WHERE LIMIT_CATEGORY = 'DATABASE' 14 ORDER BY "% used" DESC 15 LIMIT 5

Lines 9 – 11: Are the only difference between this and the previous statement. What does this statement mean?

It partitions the data, groups, by the system schema name and the system object name, and sorts the data by the last change timestamp in descending order. Therefore, row number "1" will always be the most recent row.

The results are:

CURRENT MAXIMUM ROW_ SIZING NAME _VALUE _VALUE % used Library File Member LAST_CHANGE_TIMESTAMP NBR ------------------------- ------- ------- ------ ------- ------ ------ -------------------------- ---- MAXIMUM NUMBER OF MEMBERS 30,000 32,767 91.56 REGLIB MMPF <NULL> 2026-08-10 12:32:05.622145 1 MAXIMUM NUMBER OF MEMBERS 29,950 32,767 91.40 REGLIB MMPF <NULL> 2026-08-10 12:32:05.121220 2 MAXIMUM NUMBER OF MEMBERS 29,900 32,767 91.25 REGLIB MMPF <NULL> 2026-08-10 12:32:04.690789 3 MAXIMUM NUMBER OF MEMBERS 29,850 32,767 91.10 REGLIB MMPF <NULL> 2026-08-10 12:32:04.216803 4 MAXIMUM NUMBER OF MEMBERS 29,800 32,767 90.95 REGLIB MMPF <NULL> 2026-08-10 12:32:03.780424 5

Row number "1" is the most recent row.

I decided to use a Common Table Expression, CTE, and divide what I want to do into two steps. The first part would be to calculate the row numbers, and the second would select the first record for each partition of data and calculate the percentage.

01 WITH T0 AS 02 (SELECT SIZING_NAME,CURRENT_VALUE,MAXIMUM_VALUE,SYSTEM_SCHEMA_NAME,SYSTEM_OBJECT_NAME,SYSTEM_TABLE_MEMBER, 03 ROW_NUMBER() OVER (PARTITION BY SYSTEM_SCHEMA_NAME,SYSTEM_OBJECT_NAME 04 ORDER BY LAST_CHANGE_TIMESTAMP DESC) AS "ROW_NBR" 05 FROM QSYS2.SYSLIMITS_BASIC) 06 SELECT SIZING_NAME AS "Name", 07 TO_CHAR(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),'990.99') AS "% used", 08 CURRENT_VALUE,MAXIMUM_VALUE, 09 SYSTEM_SCHEMA_NAME AS "Library", 10 SYSTEM_OBJECT_NAME AS "Object", 11 SYSTEM_TABLE_MEMBER AS "Member" 12 FROM T0 13 WHERE ROW_NBR = 1 14 ORDER BY "% used" DESC 15 LIMIT 5

Line 1 – 5: The first part of the CTE gathers the columns for the results, and calculates the row number.

Line 6 – 15: the second part gives the columns that will appear in the results nice column names, and calculates the percentage.

Line 7: Rather than using the ROUND and DESC scalar functions, I have used the TO_CHAR scalar function. TO_CHAR converts the number to character in the format the follows the calculation. The zero ( 0 ) before the decimal point will give the numbers that are less than one a leading zero.

The results are:

CURRENT Name % used _VALUE MAXIMUM_VALUE Library File Member ---------------------------- ------ --------- ------------- ------- ---------- ------ MAXIMUM NUMBER OF MEMBERS 91.56 30000 32767 REGLIB MMPF <NULL> MAXIMUM NUMBER OF MEMBERS 2.59 850 32767 MYLIB DEVSRC2 <NULL> MAXIMUM NUMBER OF MEMBERS 0.61 200 32767 QSPL Q04079N012 <NULL> MAXIMUM NUMBER OF MEMBERS 0.31 100 32767 QSPL Q04079N005 <NULL> MAXIMUM TABLE PARTITION SIZE 0.26 4854059008 1869169767219 REGLIB FOOFILE FOOFILE

It makes sense to make a SQL View of the last statement, then it can be used time and again:

01 CREATE OR REPLACE VIEW MYLIB.MAXIMUM_SIZING 02 FOR SYSTEM NAME "MAXSIZING" 03 (SIZING_NAME,PERCENTAGE_USED,CURRENT_VALUE,MAXIMUM_VALUE,LIBRARY,OBJECT,MEMBER) 04 AS WITH T0 AS 05 (SELECT SIZING_NAME,CURRENT_VALUE,MAXIMUM_VALUE, 06 SYSTEM_SCHEMA_NAME,SYSTEM_OBJECT_NAME,SYSTEM_TABLE_MEMBER, 07 ROW_NUMBER() OVER (PARTITION BY SYSTEM_SCHEMA_NAME,SYSTEM_OBJECT_NAME 08 ORDER BY LAST_CHANGE_TIMESTAMP DESC) AS "ROW_NBR" 09 FROM QSYS2.SYSLIMITS_BASIC) 10 SELECT SIZING_NAME, 11 TO_CHAR(((CURRENT_VALUE * 100) / MAXIMUM_VALUE),'990.99'), 12 CURRENT_VALUE,MAXIMUM_VALUE, 13 SYSTEM_SCHEMA_NAME AS "Library", 14 SYSTEM_OBJECT_NAME AS "Object", 15 SYSTEM_TABLE_MEMBER AS "Member" 16 FROM T0 17 WHERE ROW_NBR = 1 ;

Line 1: The new View is called MAXIMUM_SIZING.

Line 2: As the SQL name is longer than ten characters I have given it a system name too.

Line 3: As I have a new column, the percentage, that does not exist the origination View, I need a column list.

Lines 4 – 17: These are the same as the previous statement.

After building the View I can just have a simple statement to return the results I desire:

01 SELECT * FROM MAXIMUM_SIZING 02 WHERE PERCENTAGE_USED >= 75 03 ORDER BY PERCENTAGE_USED DESC 04 LIMIT 5

Line 2: I am only selecting results where the percentage in greater than 75%.

Line 3: And I ordering results by percentage in descending order.

Only one result is returned:

SIZING NAME % used _VALUE MAXIMUM_VALUE Library File Member ---------------------------- ------ --------- ------------- ------- ---------- ------ MAXIMUM NUMBER OF MEMBERS 91.56 30000 32767 REGLIB MMPF <NULL>

I now know what I need to address before the number of members in MMPF reaches the maximum.

You can learn more about this from the IBM website:

• SYSLIMITS SQL View
• SYSLIMITS_BASIC SQL View
• ROUND SQL scalar function

 

This article was written for IBM i 7.6, and should work for some earlier releases too.