I had a client reach out this week because they encountered the maximum size for an index. (ouch) They had some indexes that were constructed to use a smaller maximum size than what's possible for SQL indexes. This query allows them to monitor when their Max 4GB sized indexes have grown to the level that they are over 60% of the max size.

Smaller Index is nearing a limit.sql

--
-- Show me the 4GB maximum size database radix indexes that have eclipsed 60% of the maximum allowed size
-- SQL indexes are *MAX1TB (where the maximum size is really closer to 2TB)
-- Non-SQL indexes created with CRTLF can be either *MAX1TB or *MAX4GB
--
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqserviceshealth.htm
--
WITH OVER_60_PERCENT(Object_Name, limit_id, Sizing_name,     
       Current_value, Maximum_value, Row_Number) AS                 
 (                                                                  
 SELECT SYSTEM_SCHEMA_NAME concat '/' concat SYSTEM_object_name,  
        Limit_id, Sizing_name, Current_value, Maximum_value,        
        ROW_NUMBER() OVER (PARTITION BY SYSTEM_SCHEMA_NAME          
                        concat '/' concat SYSTEM_object_name      
                           ORDER BY CURRENT_VALUE DESC)             
     FROM qsys2.syslimits l WHERE                                   
       LAST_CHANGE_TIMESTAMP > CURRENT TIMESTAMP - 30 days AND 
--    
-- Maximum size of a *MAX4GB index 	15400
--
       limit_id IN (15400) AND                                      
       DECFLOAT(current_value,34) /                                 
       DECFLOAT(maximum_value, 34) * 100 > 70.00                    
)                                                                   
SELECT Object_Name                                                  
  concat ' *FILE HAS CONSUMED MORE THAN 60% OF THE LIMIT: '       
  concat limit_ID concat '-' concat sizing_name                   
  concat ' (' concat current_value concat ' OF ' concat         
  maximum_value concat '=' concat                                 
  DECIMAL(DECFLOAT(current_value,34) /                              
  DECFLOAT(maximum_value, 34) * 100,63,2) concat '%'              
  concat '). REFER TO ibm.biz/DB2foriAlerts FOR MORE DETAIL.'     
 FROM OVER_60_PERCENT                                               
 WHERE Row_Number = 1;