Skip to content

Instantly share code, notes, and snippets.

@forstie
Created July 13, 2020 13:12
Show Gist options
  • Save forstie/2ff96fbe2d8c4c9d429c8a215ae25eb1 to your computer and use it in GitHub Desktop.
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.
--
-- 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