Created
July 13, 2020 13:12
-
-
Save forstie/2ff96fbe2d8c4c9d429c8a215ae25eb1 to your computer and use it in GitHub Desktop.
The IBM i has some built-in facilities to allow you to understand that an 'out of storage' condition exists. Here are some example SQL techniques for managing this alert mechanism.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Check the configuration of: | |
-- Maximum system disk pool usage (QSTGLOWACN and QSTGLOWLMT) system values | |
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzakz/rzakzqstglowacn.htm | |
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzakz/rzakzqstglowlmt.htm | |
-- | |
select current_character_value from qsys2.system_value_info | |
where system_value_name = 'QSTGLOWACN'; | |
stop; | |
-- | |
-- Review the ASP consumption vs limit | |
-- | |
with sysval(raw_QSTGLOWLMT, low_limit) as ( | |
select current_numeric_value, | |
current_numeric_value/10000.0 as QSTGLOWLMT | |
from qsys2.system_value_info | |
where system_value_name = 'QSTGLOWLMT' | |
) | |
select SYSTEM_ASP_USED, | |
DEC((100.00 - low_limit),4,2) as SYSTEM_ASP_LIMIT, | |
raw_QSTGLOWLMT | |
from sysval, qsys2.SYSTEM_STATUS_INFO ; | |
stop; | |
-- | |
-- Review recent history when the storage utilization rose too high | |
-- | |
select message_id, message_timestamp, | |
interpret((substr(message_tokens, 63, 8)) as decimal(15, 6)) as threshold_percent, | |
100.0 - interpret((substr(message_tokens, 71, 8)) as decimal(15, 6)) as consumed_percent, | |
interpret((substr(message_tokens, 71, 8)) as decimal(15, 6)) as available_percent, | |
varchar_format( | |
bigint(substr(message_tokens, 1, 31)), | |
'999G999G999G999G999G999G999G999G999G999G999') as storage_capacity, | |
varchar_format( | |
bigint(substr(message_tokens, 32, 31)), | |
'999G999G999G999G999G999G999G999G999G999G999') as storage_used, | |
substr(message_second_level_text, 184, 105) as cpf0907_first_level_text, | |
substr(message_second_level_text, 290, 89) as cpf0907_second_level_text | |
from table ( | |
qsys2.history_log_info( | |
start_time => current date - 1 day) -- <=== today and yesterday | |
) | |
where message_id = 'CPF0907' | |
order by message_timestamp desc; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment