02a - SQL (EN)

Up and down (EN)

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

These and many other SQL prêt-à-porter instructions on the MarkOneTools blog https://www.markonetools.it/sql-pret-a-porter/

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

IBM i's mentor IBM Champion 2021 for Power Systems

Leave a Reply

Your email address will not be published. Required fields are marked *