Last Updated on 1 December 2019 by Roberto De Pedrini
MarkOne’s suggestions – n. 4
Let’s we check last IPL status
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:
- the instructions are tested on IBM i 7.3 TR6 and IBM i 7.3 TR7
- the SPOOLED_FILE_DATA table function requires IBM i 7.3 TR6
- the table function HISTORY_LOG_INFO requires IBM i 7.3 TR1 or 7.2 TR5
- the JOB_INFO table function requires IBM i 7.2 TR3
- CTEs (with clause) require V5R4
- the SYSTEM_VALUE_INFO view requires IBM i 7.1
- the SCHEDULED_JOB_INFO view requires IBM i 7.2
- the QWCCRTEC API requires IBM i 6.1
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
Bibliography
- How to read spool files with SQL, by Massimo Duca, Oct 13, 2019, https://blog.faq400.com/it/programmazione/varie-programmazione-ibm-i/leggere-spool-file-con -sql /
- Program QWCCRTEC: Prints Report on Time Spent in IPL Phases / Steps, IBM Technical document n. 642439, Jun 17, 2018, https://www.ibm.com/support/pages/program-qwccrtec-prints-report-time-spent-ipl-phasessteps
- Searching the History log using SQL, by Simon Hutchinson, Dec 14, 2016, https://www.rpgpgm.com/2016/12/searching-history-log-using-sql.html
- Simplify SQL “With” Common Table Expressions, by Sam Lennon, Sep 19, 2012, https://www.mcpressonline.com/programming/sql/simplify-sql-qwithq-common-table-expressions
These and many other SQL prêt-à-porter instructions on the MarkOneTools blog https://www.markonetools.it/sql-pret-a-porter/