forstie

--
-- Subject: QBATCH Job Analysis
-- Author: Scott Forstie
-- Date  : May 30, 2021
-- Note  : This Gist leverages different built-in functions and grouping support to explore job history
--

-- ================================================================================================
--
-- description: 20 Jobs that ran (started and ended) the longest in QBATCH over the last 24 hours

BirgittaHauser

      //*********************************************************************************************
      // I recently get asked how to read a small PDF document into a BLOB and pass it as parameter  
      // to another function or procedure
      // 1. When working with embedded SQL character host variables can only be used up to 32k
      //    Larger data (up to 16 MB - RPG restriction) 
      //    can be read into a LOB-Variable defined with the SQLTYPE keyword
      // 2. Unfortunately the SQLTYPE Keyword cannot be used in a Prototype or Procedure Interface
      // 3. But the SQL precompiler generates for the LOB variable a data structure with an
      //    UNS(4)     Subfield _LEN and
      //    CHAR(xxx)  Subfield _DATA

forstie

--  category:  IBM i Services
--  description:  Librarian -  Library Info 
--  minvrm: V7R3M0
--

create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default
'create or replace table coolstuff.library_sizes
      (library_name, schema_name, 
      
       -- qsys2.library_info() columns

forstie

--
-- description: printf to the Joblog, using SQL
--
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(LPRINTF);
insert into qtemp.qcsrc values
 (1,010101,'{'),                                                  
 (2,010101,'extern int Qp0zLprintf (char *format, ...);'),      

forstie

-- 
-- When an IFS directory includes W (write), you are exposed to malware attacks
-- Use this to review and overcome this topic for the all important ROOT directory
--
-- For help on this or related security topics, contact Robert and team...
-- http://ibm.biz/IBMiSecurity
-- Robert Andrews - robert.andrews@us.ibm.com
--
stop;

forstie

--
-- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the
-- acsbundle.jar onto the IBM i.  Just apply the latest ACS PTFs and reference the jar
-- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar
--
-- Initial PTFs of ACS
-- V7R4M0 SI71900
-- V7R3M0 SI71934 
--

EdgardoEhiyan

SELECT ENTRY_TIMESTAMP as JE_TIME, CURRENT_USER as CURRENT_USR,     
SEQUENCE_NUMBER, JOURNAL_ENTRY_TYPE,JOB_NAME,                       
JOB_USER, JOB_NUMBER,PROGRAM_NAME,                                  
OBJECT,OBJECT_TYPE,PATH_NAME,                                       
CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) as F1,                                     
CAST(cast(substring(entry_data, 33, 130) as char(130) for bit data) as CHAR(130) CCSID 1141) as F2,                                    
HEX(cast(substring(entry_data, 163, 258) as char(258) for bit data)) as F3,                                                             
HEX(cast(substring(entry_data, 421, 6) as char(6) for bit data)) as N1,                                                                 
HEX(cast(substring(entry_data, 427, 11) as char(11) for bit data)) as N2,                                                              
HEX(cast(substring(entry_data, 438, 4) as char(4) for bit data)) as N

forstie

create or replace function systools.objects_owned(
        user_name varchar(10) FOR SBCS DATA)
	RETURNS TABLE ( 
	object DBCLOB(16M) CCSID 1200,
    object_type varchar(10) for sbcs data,
    size bigint,
    sizeform varchar(50) FOR SBCS DATA,
    created timestamp,
    changed timestamp,

BirgittaHauser

-- Scan through all spoolfiles in a specific outqueue (e.g. QEZJOBLOG) for a specific string
Select a.Job_Name, Spooled_File_Name, File_Number, Spooled_Data
       -- , a.*
  from OutPut_Queue_Entries a Cross Join 
       Lateral(Select *
                 From Table(SysTools.Spooled_File_Data(
                                        Job_Name            => a.Job_Name, 
                                        Spooled_File_Name   => a.Spooled_File_Name, 
                                        Spooled_File_Number => File_Number))) b
  Where     Output_Queue_Name = 'QEZJOBLOG'

NielsLiisberg

-- This procedure deletes old backup files produced 
-- when the RSTOBJ command restores file objects.
--
-- It uses the regex to filter the names of the files 
-- the RSTOBJ command gives these file objets in the rename process.
--
-- Simply paste this gist into ACS SQL and step through the code.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--