bdietz400

--  description: LAST_IPL.sql
-- Analogus to the CL I created years ago:
-- that calls API QUSRJOBi against job 000000/QSYS/SCPF using format JOBi0400


SELECT Job_Entered_System_Time AS Last_Ipl
    FROM TABLE(Qsys2.Job_Info(Job_Status_Filter => '*ACTIVE', Job_User_Filter => 'QSYS')) X
    WHERE Job_Name = '000000/QSYS/SCPF' ;
    

bdietz400

-- category: bryan.dietz
-- description: WRKRMTWTR  
-- Inspired by : http://bryandietz.us/wrkrmtwtr.html
SELECT TRIM(Output_Queue_Library_Name) CONCAT '/' CONCAT TRIM(Output_Queue_Name) AS Output_Queue
       ,Number_Of_Files AS "# Splf"
       ,Output_Queue_Status AS Status
       ,Remote_System_Name AS Remote_Host
       ,Remote_Printer_Queue AS Rmt_Queue
       ,Writer_Job_Status AS Wtr_Sts
       ,Workstation_Customizing_Object_Name AS Wscst

bdietz400

-- category: History_Log
-- description: history log - subtotals: jobs per hour

-- change date time to lok for
-- if looking for specific job name or user submitted job, 
-- change as needed.

SELECT DATE(Message_Timestamp) AS Date_Started
       ,HOUR(Message_Timestamp) AS Hour_Started
       ,COUNT(From_Job) AS Jobs_Count

bdietz400

-- Bryan Dietz
-- trying to get some results similar to WRKODBCJOB

SELECT Aj.Subsystem
       ,Aj.Job_Name
       ,Aj.Authorization_Name AS "Current User"
       ,Aj.Client_Ip_Address
       ,Aj.Cpu_Time AS "CPU MS Used"
       ,Aj.Temporary_Storage as "Temp Storage"
       ,Aj.Total_Disk_Io_Count as " Disk I/O Count"

bdietz400

SELECT current server as Sysname, YEAR(Create_Timestamp) AS Year_Created
       ,MONTH(Create_Timestamp) AS Month_Created
       ,COUNT(System) AS Spooled_Files
    FROM Qsys2.Output_Queue_Entries_Basic
    GROUP BY Grouping Sets (
                (
                    MONTH(Create_Timestamp)
                    ,YEAR(Create_Timestamp)
                ) 							            --  "Detail"
                ,(YEAR(Create_Timestamp)) 	--  subtotal

bdietz400

-- category: bryan.dietz
-- description: Look for BADLOGONS

SELECT Entry_Timestamp, current server as System_name
       ,Job_Number CONCAT '/' CONCAT RTRIM(Job_User) CONCAT '/' CONCAT RTRIM(Job_Name) AS Job_Name
       ,RTRIM(Program_Library) CONCAT '/' CONCAT RTRIM(Program_Name) AS Program_Name
       ,RTRIM(CAST(SUBSTR(Entry_Data, 2, 10) AS VARCHAR(10))) AS User_Id
       ,Remote_Address
       ,Remote_Port
       ,CASE RTRIM(CAST(SUBSTR(Entry_Data, 1, 1) AS CHAR(1)))

bdietz400

--  category:  Bryan.Dietz 
--  description:  Jobs in msgw(wrkactjob)

SELECT Job_Name
       ,Subsystem
       ,Function_Type CONCAT '-' CONCAT Function AS "Function"
       ,Job_Status
       ,(SELECT Message_Text
               FROM TABLE (Qsys2.Joblog_Info(I.Job_Name)) A
               ORDER BY Ordinal_Position DESC