forstie

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

forstie

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

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

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

forstie

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

forstie

--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_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

BirgittaHauser

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

BirgittaHauser

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

BirgittaHauser

-- UDF - Check eMail Address:
-- Parameters: ParEmail => EMail Address
-- Returns:    0 = Invalid eMail Address / 1 = valid eMail Address
--             In Release 7.5 the new Boolean Datatype could be used for the Return Value
-- Attention:  There might be better regular expression patterns for checking eMail addresses

Create or Replace Function YourSchema.CheckEmail (ParEMail Varchar(256))
       Returns Integer
   Language SQL
   Modifies SQL Data

forstie

--
-- Subject: The request... use SQL to determine if the DDM/DRDA server was active, and if not, start it.
-- Author: Scott Forstie  
-- Date  : June, 2022
-- Features Used : This Gist uses QSYS2.ACTIVE_JOB_INFO, BOOLEAN, QSYS2.QCMDEXC scalar function, CTE, case expression
--
--
-- Is the DDM/DRDA listener active? (If at IBM i 7.4 or earlier)
--
select count(*) as DDM_DRDA_Listener_Active