dancarlosgabriel

--
-- Subject: Data driven emails from IBM i
-- Author: Scott Forstie  
-- Date  : May, 2024
-- Features Used : This Gist uses SQL PL, listagg, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL, and database know-how
--
-- Now that it's simple to generate spreadsheets and send emails from the IBM i, the request was to 
-- send emails and NOT have the recipient(s) of the email hard-coded.
--
-- One solution is found below. Store the email recipients within a Db2 for i table and 

dancarlosgabriel

--
-- =======================================================================
-- Subject: See Collection Services (CS) config and query CS data with SQL
-- =======================================================================
--
-- Author: Scott Forstie
-- Date  : September, 2023
-- Email : [email protected]
--
-- This Gist is a two-fer:

dancarlosgabriel

-- Subject: DBE in a box
-- Author: Scott Forstie  
-- Date  : August, 2023
-- Features Used : This Gist uses SQL PL,  qsys2.SYSTEM_STATUS_INFO_basic, systools.lprintf, qsys2.sysixadv, qsys2.sysindexes, QSYS2.SYSINDEXSTAT, SYSTOOLS.ACT_ON_INDEX_ADVICE, QSYS2.RESET_TABLE_INDEX_STATISTICS and SYSTOOLS.REMOVE_INDEXES
--
-- Note: 
-- 1) Indexes are not free, and care should be taken when deciding whether an index is worth creating
-- 2) Indexes are not free, and care should be taken to understand whether adequate system resources are available before creating additional indexes
-- 3) Indexes are not free, and care should be taken to establish a well trained Database Engineer (DBE) responsible for your IBM i partitions
--

dancarlosgabriel

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

dancarlosgabriel

-- Subject: Using initAuto to find uninitialized variable problems in ILE code
-- Author: Scott Forstie  
-- Date  : June, 2023
-- Features Used : This Gist uses SQL PL, INCLUDE, Change Program (CHGPGM) CL command, qsys2.joblog_info, qsys2.program_info, qsys2.qcmdexc, and QSYS2.BOUND_MODULE_INFO
--
-- A) Programmers use declared variables and structures to facilitate program logic.
-- B) Programmers should always initialize or assign declared variables and structures to contain known and expected values.
-- C) Programmers sometime miss step B.
--
-- This Gist shows how the IBM i Optimizing Translator can help.

dancarlosgabriel

--
-- Subject: The request... return SQL services detail using JSON.
-- Author: Scott Forstie  
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, listagg(), rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note: 
--       When someone asks you to return Db2 for i data "as JSON", they probably want you
--       to publish a JSON document, which contains good key names and of course, the data.
--

dancarlosgabriel

--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.journal_receiver_info, qsys2.object_statistics, qsys2.qcmdexc, CTEs, sysibmadm.env_sys_info, and SYSTOOLS.split
--
-- Notes:
-- ===============================================
-- 1) There are many configuration options to consider using when establishing the Audit Journal, this Gist uses SQL to examine some of the most important choices.
-- 2) Its important to have a retention strategy for audit journal - journal receivers

dancarlosgabriel

-- Retrieve the IFS file with GET_CLOB_FROM_FILE
-- Replace & with & 
-- Write a new IFS file with the same name as the original file and replace the existing IFS file
Call Qsys2.Ifs_Write_Utf8(Path_Name   => '/Home/Dir1/Dir2/YourIfsFile.csv', 
                          Line        => Replace(Get_Clob_From_File('/Home/Dir1/Dir2/YourIfsFile.csv'), 
                                                 '&', '&'), 
                          Overwrite   => 'REPLACE', 
                          End_Of_Line => 'NONE');            

dancarlosgabriel

-- Question in an Forum: I have a table/file which has duplicate records (Lets say there are 10 fields in a record). 
--                       Out of which, if 5 fields put together are same across more than 1 record, then it is considered as a duplicate). 
--                       I need to delete those duplicate records alone using a SQL statement embedded in a SQLRPGLE program.

-- 1. Solution with a Cursor in embedded SQL (using a Common Table Expression for determining the duplicate rows!)
**Free
//---------------------------------------------------------------------------------------------
  CTl-Opt DatFmt(*ISO)  Option(*NoDebugIO);
//---------------------------------------------------------------------------------------------
  DCL-S  GblRRN   Int(20);

dancarlosgabriel

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