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

-- 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

-- 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

-- 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

--  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

     A                                      DSPSIZ(24 80 *DS3)                                      
     A                                      CA03                                                    
     A                                      CA12                                                    
     A                                      CF09                                                    
     A*                                                                                             
     A          R HEADER                                                                            
     A*                                                                                             
     A                                      OVERLAY                                                 
     A                                  1 25'Multiple Subfiles - Side by Side'                      
     A                                      DSPATR(HI)                                              

bdietz400

 * example of getting HMC name and IP address
 * https://www.ibm.com/docs/en/i/7.4?topic=instructions-materialize-machine-attributes-matmatr#MATMATR__mat0204
 


h DFTACTGRP(*no)                                              
d matmatr_tmpl    ds                                          
d bytes_in                      10i 0                         
d bytes_avail                   10i 0                         
d NoOfEntries                   10I 0                         

bdietz400

--  description: netstat - jobs from telnet sessions Regular and SSL
SELECT 
 Remote_Address 
,LOCAL_ADDRESS
 , Job_Name 
 , Local_Port
 ,LOCAL_PORT_NAME
   FROM Qsys2.Netstat_Job_Info i
    WHERE Local_port in ( 23, 992 )
    and job_name not like '%QTCP%' 

bdietz400

-- description: find in IFS files
-- using ACS 1.1.9.2 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