MarkOne’s suggestions – n. 4
Index
When did our IBM i shut down and last? And how long did it take to go out and turn on?
In this article I suggest a simple API and some ready-to-use SQL statements to extract information about the last IBM i shutdown and power-up operation from the system.
This information is contained in various “angles” of our operating system (system values, system operator messages, history logs, …). With a few SQL statements we display them all together.
I advise (not only in this case, but in general) to execute the instructions from a “graphical” SQL client. We all surely have available Execute SQL script contained in IBM Access Client Solutions or IBM i Access. Or there are many other java-based SQL clients (eg Squirrel SQL client, DBeaver, …).
WARNING! The following restrictions apply to the SQL statements written below:
First, let’s run the QWCCRTEC API which produces a QPSRVDMP spool with detailed information about the various stages of turning off and turning on the IBM i system.
- >> information on the execution steps of the last IPL
- N.B. from IBM i 7.1 it is no longer necessary to specify the second parameter with the string length of the command to be executed
call qcmdexc ('CALL QWCCRTEC');
Still in the same session of Execute SQL script we execute the following 3 quer
ies. ATTENTION: the table function SPOOLED_FILE_DATA requires IBM i 7.3 TR6. If you want to execute these instructions on previous versions, you need to remove the parts of the instruction that use this function.
- >> Last shutdown
with
- QABNORMSW system value
SV_QABNORMSW as
(select case when CURRENT_CHARACTER_VALUE = '1' then 'anomalous' else 'normal' end as SYSVAL
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QABNORMSW'),
- hourly extraction starting PWRDWNSYS from the QWCCRTEC spool
PWRDOWN_START as
(select timestamp_format (substr (SPOOLED_DATA, 103, 17), 'MM / DD / YY HH24: MI: SS', 0) as PDSTART
from table (SYSTOOLS / SPOOLED_FILE_DATA ('', 'QPSRVDMP')) where ucase (substr (SPOOLED_DATA, 87, 11)) = 'XPF PWRDWN'),
- PWRDWNSYS end time extraction from QWCCRTEC spool
PWRDOWN_END as
(select timestamp_format (substr (SPOOLED_DATA, 103, 17), 'MM / DD / YY HH24: MI: SS', 0) as PDEND
from table (SYSTOOLS / SPOOLED_FILE_DATA ('', 'QPSRVDMP')) where ucase (substr (SPOOLED_DATA, 87, 11)) = 'END PWRDWN'),
- CPI0995 message extraction (PWRDWNSYS entry) from history log (DSPLOG)
HSTLOG_CPI0995 as
(select timestamp (MESSAGE_TIMESTAMP, 0) as M1TIME,
trim (substr (MESSAGE_SECOND_LEVEL_TEXT, locate_in_string (MESSAGE_SECOND_LEVEL_TEXT, '. . . : ', 1) +8)) as M1TEXT
from table (HISTORY_LOG_INFO ((select PDSTART - 1 hours from PWRDOWN_START),
(select PDSTART + 1 hours from PWRDOWN_START))) as HL
where MESSAGE_ID = 'CPI0995'
order by MESSAGE_TIMESTAMP desc
fetch first 1 rows only)
- final query
select
SYSVAL as "Shutdown status",
M1TIME as "PWRDWNSYS entry", PDSTART as "Start of shutdown", PDEND as "End shutdown",
M1TEXT as "PWRDWNSYS Details"
from SV_QABNORMSW left join PWRDOWN_START on 1 = 1 left join PWRDOWN_END on 1 = 1 left join HSTLOG_CPI0995 on 1 = 1;
If the shutdown was successful you will get a result similar to:
or if the shutdown has ended abnormally:
An abnormal shutdown eg. it could occur if you run the PWRDWNSYS * IMMED command and due to some anomaly in the system the shutdown process does not end before the number of seconds specified in the QPWRDWNLMT system value or due to a power failure not protected by UPS, etc.
The next instruction instead shows us the details of the last switch-on of our IBM i system:
- >> Last start:
with
- QIPLSTS system value
SV_QIPLSTS as
(select case CURRENT_CHARACTER_VALUE
when '0' then 'operator panel'
when '1' then 'automatic after power on again'
when '2' then 'reboot'
when '3' then 'pre-established time'
when '4' then 'remote'
else 'N / A' end as IPLSTS
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLSTS'),
- QIPLTYPE system value
SV_QIPLTYPE as
(select case CURRENT_CHARACTER_VALUE
when '0' then 'unattended'
when '1' then 'manned with DST'
when '2' then 'manned (debug)'
else 'N / A' end as IPLTYPE
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLTYPE'),
- IPL start time extraction from QWCCRTEC spool
IPL_START as
(select timestamp_format (substr (SPOOLED_DATA, 103, 17), 'MM / DD / YY HH24: MI: SS', 0) as ISTART
from table (SYSTOOLS / SPOOLED_FILE_DATA ('', 'QPSRVDMP')) where ucase (substr (SPOOLED_DATA, 87, 8)) = 'XPF IPL'
fetch first 1 rows only - only the first rek. In case of IPL with PTF application there can be two consecutive XPF IPL records), - hourly end IPL extraction from QWCCRTEC spool IPL_END as (select timestamp_format (substr (SPOOLED_DATA, 103, 17), 'MM / DD / YY HH24: MI : SS ', 0) as IEND from table (SYSTOOLS / SPOOLED_FILE_DATA (' ',' QPSRVDMP '))
where ucase (substr (SPOOLED_DATA, 87, 11)) = 'END OF IPL'), - start time of the SCPF job (Start Control Program Function) , JOB_USER_FILTER => 'QSYS')) as Job
where JOB_NAME = '000000 / QSYS / SCPF'),
- operator message system with information on IPL area
- (the message may have been removed so it is better to extract this information from the history log)
- QSYSOPR_AREAIPL as
- (select trim (MESSAGE_TEXT) as AREAIPL, timestamp (MESSAGE_TIMESTAMP, 0) as AREAIPL_TIME
- from QSYS2 / MESSAGE_QUEUE_INFO
- where MESSAGE_QUEUE_NAME = 'QSYSOPR' and MESSAGE_ID = 'CPI0C04'),
- extraction of messages from history log (DSPLOG)
HSTLOG as
(select MESSAGE_ID, timestamp (MESSAGE_TIMESTAMP, 0) as M1TIME,
MESSAGE_TEXT as M1TEXT1, MESSAGE_SECOND_LEVEL_TEXT as M1TEXT2
from table (HISTORY_LOG_INFO ((select ISTART - 1 hours from IPL_START),
(select IEND + 1 hours from IPL_END))) as HL
where MESSAGE_ID in ('CPI0C04', 'CPF0998', 'CPI091D')),
- CPI0C04 message extraction from history log: IPL area
HSTLOG_CPI0C04 as
(select trim (M1TEXT1) as M1TEXT1
from HSTLOG
where MESSAGE_ID = 'CPI0C04'
order by M1TIME desc
fetch first 1 rows only),
- extraction message CPF0998 from history log: anomalous closure
HSTLOG_CPF0998 as
(select M1TEXT1
from HSTLOG
where MESSAGE_ID = 'CPF0998'
order by M1TIME desc
fetch first 1 rows only),
- CPI091D message extraction from history log: due to anomalous closure
HSTLOG_CPI091D as
(select substr (M1TEXT1, locate_in_string (M1TEXT1, 'cause code', 1) +13, 1) as CODE,
substr (M1TEXT2,
locate_in_string (M1TEXT2, '& P' concat substr (M1TEXT1, locate_in_string (M1TEXT1, 'cause code', 1) +13, 1), 1) +7,
(locate_in_string (M1TEXT2, '& P', locate_in_string (M1TEXT2, '& P' concat substr (M1TEXT1, locate_in_string (M1TEXT1, 'cause code', 1) +13, 1), 1) +1) -1) -
(locate_in_string (M1TEXT2, '& P' concat substr (M1TEXT1, locate_in_string (M1TEXT1, 'cause code', 1) +13, 1), 1) +7)
)
as DESCRIPTION
from HSTLOG
where MESSAGE_ID = 'CPI091D'
order by M1TIME desc
fetch first 1 rows only)
- final query
select IPLSTS as "IPL type executed", IPLTYPE as "IPL type to execute",
ISTART as "Start-up",
SCPFSTART as "Start / Start Time Program Control Function Date",
IEND as "End of ignition",
HSTLOG_CPI0C04.M1TEXT1 as "IPL Area",
case when HSTLOG_CPF0998.M1TEXT1 is null then 'No' else 'Yes' end as "IPL after anomalous closure",
HSTLOG_CPI091D.CODICE concat '-' concat HSTLOG_CPI091D.DESCRIZIONE as "Cause anomalous closure"
from SV_QIPLSTS, SV_QIPLTYPE, IPL_START, IPL_END, SCPF_START
left join HSTLOG_CPI0C04 on 1 = 1
left join HSTLOG_CPF0998 on 1 = 1
left join HSTLOG_CPI091D on 1 = 1;
Power-up information may be similar to:
After an abnormal shutdown the information of the next power up could be similar to:
Finally with this last instruction we visualize the planned date / time for the next planned ignition or from the menu POWER opz. 2 or via a scheduled job that executes a PWRDWNSYS with parameter RESTART (* YES).
- >> Next planned ignition
- ignition programmed in the POWER menu
select 'Menu POWER' as "Type",
timestamp_format (substr (trim (CURRENT_CHARACTER_VALUE), 2), 'YYMMDDHH24MISS', 0) as "Date / time next switch-on"
from SYSTEM_VALUE_INFO
where SYSTEM_VALUE_NAME = 'QIPLDATTIM'
and CURRENT_CHARACTER_VALUE <> 'NONE'
union all
- scheduled switch-on with PWRDWNSYS command… RESTART (YES)
select 'Scheduled job' as "Type",
timestamp_format (NEXT_SUBMISSION_DATE concat '' concat SCHEDULED_TIME, 'YYYY-MM-DD HH24: MI: SS', 0) as "Date / time next switch-on"
from SCHEDULED_JOB_INFO as JS
where ucase (COMMAND_STRING) like '% PWRDWNSYS% RESTART (* YES)%'
and STATUS = 'SCHEDULED'
order by 2;
If the query does not return any records, no IBM i system is scheduled to power up.
If the POWER menu has been set to power on 2/12/2019 at 1.30 you will get
If, on the other hand, you set a reboot via a scheduled job for 12/25/2025 at 11.00pm you will get:
Download the complete script from https://gist.github.com/mk1tools/575e3ac809736e01b724928fc2c48597
These and many other SQL prêt-à-porter instructions on the MarkOneTools blog https://www.markonetools.it/sql-pret-a-porter/
👉 Review: Bob Cozzi’s RPG IV to RPG Free Conversion – a useful VS Code extension for RPG modernization If…
Hello everyone, I’d like to highlight another excellent contribution by Massimo Duca, part of his ongoing IBM i & SQL…
Intrigued by some recent posts from Cristian Larsen on LinkedIn (New Release – Display File DDS Edit v 0.10.1), I…
Hello everyone, Today I’d like to draw your attention to a major new announcement from IBM: Project Bob — a…
I want to share with you a particularly useful article by Massimo Duca in the IBM i & SQL Tips…
Hello everyone, I’d like to highlight a very useful article by Marco Riva on Markonetools, where he clearly explains how…