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/
If you work with IBM i, I recommend reading this insightful article by Giancarlo Lui: IBM i System Management: Simpler…
In his recent article "RPG Free and option *convert" , Aldo Succi explores the *CONVERT option in the RPG language,…
Updating Java and DCM Certificates for ECB Exchange Rate Retrieval on IBM i In our blog, we have previously discussed…
We are pleased to receive and share this "tip & trick" from Patrick Rizzi, which introduces a technique that allows…
I take inspiration from a response by Michael Mayer on the Midrange.com mailing lists to someone who asked how to…
Businesses are increasingly seeking tools to enhance efficiency, collaboration, and resource management. Enterprise Resource Planning (ERP) systems provide a comprehensive…