Skip to content

Instantly share code, notes, and snippets.

View bdietz400's full-sized avatar

bryan_d bdietz400

View GitHub Profile
-- description: find in IFS files
-- using ACS this will prompt you for starting path and search term
-- forced uppercase, if you know the case remove the "upper()", it may run faster
with ifsobjs (path, type) as (
select path_name, object_type
from table(qsys2.IFS_OBJECT_STATISTICS(
start_path_name => ?, -- use /path/to/start/in
subtree_directories => 'YES')) a
-- description: netstat - jobs from telnet sessions Regular and SSL
, Job_Name
, Local_Port
FROM Qsys2.Netstat_Job_Info i
WHERE Local_port in ( 23, 992 )
and job_name not like '%QTCP%'
* example of getting HMC name and IP address
d matmatr_tmpl ds
d bytes_in 10i 0
d bytes_avail 10i 0
d NoOfEntries 10I 0
A DSPSIZ(24 80 *DS3)
A CA03
A CA12
A CF09
A 1 25'Multiple Subfiles - Side by Side'
-- 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 / WRKRMTWTR
Created September 23, 2020 14:47
Work with Remote Writers
-- category: bryan.dietz
-- description: WRKRMTWTR
-- Inspired by :
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 / History_Log__jobs_per_hour
Created August 28, 2020 13:41
history log - subtotals: 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 / WRKODBCJOB in SQL
Last active July 16, 2020 17:51
-- Bryan Dietz
-- trying to get some results similar to WRKODBCJOB
SELECT Aj.Subsystem
,Aj.Authorization_Name AS "Current User"
,Aj.Cpu_Time AS "CPU MS Used"
,Aj.Temporary_Storage as "Temp Storage"
,Aj.Total_Disk_Io_Count as " Disk I/O Count"
bdietz400 / spooled files subtotals
Created January 15, 2020 21:18
all spooled files subtotals by year/month/#splfs
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 (
) -- "Detail"
,(YEAR(Create_Timestamp)) -- subtotal
bdietz400 / BadLogons.sql
Last active April 3, 2020 13:26
Show BADLOGON attempts
-- 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
,CASE RTRIM(CAST(SUBSTR(Entry_Data, 1, 1) AS CHAR(1)))