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

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

forstie

--
-- Subject: The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
-- Author: Scott Forstie  
-- Date  : May, 2022
-- Features Used : This Gist uses system_value_info, CTEs, syslimits_basic, override_qaqqini, MESSAGE_QUEUE_INFO, SEND_MESSAGE, and more
--
--
-- System limit tracking provides IBM i health insight:
-- ================================================================
-- 1) Recognize when the IBM i is trending towards an outage or serious condition

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'

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

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

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

--
-- Gist: Largest MTIs in use today
--
-- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more...
--

--
--
-- Find the tables with the 10 largest MTIs
--

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

--
-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule
--              (80% of the time, the length of the column data would be less than or equal to n)
-- minvrm: V7R3M0
--
create or replace function systools.compute_allocate (
      p_schema_name varchar(128) for sbcs data, 
      p_table_name  varchar(128) for sbcs data, 
      p_column_name  varchar(128) for sbcs data, 
      allocate_percentage decimal(3,2)