PowerWire DB2 for i Services

iServices.sql

/*  Copyright FormaServe Systems 2020                                                                                             */
/*                                                                                                                                  */
/*  All rights reserved. All trademarks, trade names, service marks & logos referenced herein belong to their respective companies  */
/*  No unauthorised use, copying or distribution permitted                                                                          */
/*  This presentation is for informational purposes only                                                                            */
/*  FormaServe assumes no responsibility for the accuracy or completeness of the information                                        */


-- Number 1 - Check PTF Groups
With Ilevel (Iversion, Irelease) As (
    Select Os_Version,
           Os_Release
      From Sysibmadm.Env_Sys_Info
  )
  Select P.*
    From Ilevel,
         Systools.Group_Ptf_Currency P
    Where Ptf_Group_Release = 'R' Concat Iversion Concat Irelease Concat '0'
    Order By Ptf_Group_Level_Available - Ptf_Group_Level_Installed Desc;

-- Number 2 - Show average CPU usage
Select Average_Cpu_Utilization
  From Qsys2.System_Status_Info;
        
-- Number 3 - What PASE shell is being used?        
select authorization_name, pase_shell_path 
  from qsys2.user_info where pase_shell_path is not null;

--Number 4 - Check History Log
Select *
  From Table (
      Qsys2.History_Log_Info(Current Timestamp - 1 Day)
    ) X ;

-- Number 5 - Show firmware
Select *
  From Systools.Firmware_Currency;

-- Number 6 - Netstat stuff
Select *
  From Qsys2.Netstat_Info
  Where Local_Port Between 3000  and 5000;

-- Number 7 - Object Info
Select *
  From Table (
      Qsys2.Object_Statistics('HRDATA', '*ALL')
    )
  Order By Objtype,
           Objname;

-- Number 8 - Server stuff
Select Machine_Type,
       Machine_Model,
       Serial_Number,
       System_Asp_Used
  From Qsys2.System_Status_Info;

-- Number 9 - Checking Authority
Select Qsys2.Sql_Check_Authority('HRDATA', 'EMPLOYEE')
  From Sysibm.Sysdummy1;

-- Number 10 - Is an object locked?
Select Job_Name,
       Lock_State,
       Lock_Status
  From Qsys2.Object_Lock_Info
  Where System_Object_Name = 'EMPLOYEE' ;