Skip to content

Instantly share code, notes, and snippets.

Avatar

bryan_d bdietz400

View GitHub Profile
View When was the Last IPL
-- 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
bdietz400 / WRKRMTWTR
Created Sep 23, 2020
Work with Remote Writers
View WRKRMTWTR
-- 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
bdietz400 / History_Log__jobs_per_hour
Created Aug 28, 2020
history log - subtotals: jobs per hour
View History_Log__jobs_per_hour
-- 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
bdietz400 / WRKODBCJOB in SQL
Last active Jul 16, 2020
WRKODBCJOB in SQL
View WRKODBCJOB in SQL
-- 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
bdietz400 / spooled files subtotals
Created Jan 15, 2020
all spooled files subtotals by year/month/#splfs
View spooled files subtotals
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
bdietz400 / BadLogons.sql
Last active Apr 3, 2020
Show BADLOGON attempts
View BadLogons.sql
-- 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)))
View Active jobs in MSGW
-- 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