Jonathan-49

--
-- Subject: Create a table function that returns size information, similiar
--                to the 'Size Limits' analysis in the Health Center found in IBM i Access Client Solutions.
--                However, not all size subjects are returned.
--                It returns size information per the IASP (Independent ASP)  or SYSBAS (system ASP)
--                The IASP must be in the users job namespace.
-- Author:  Jonathan Heinz
-- Date:     6th September 2022
-- OS: IBM i 7.3 / 7.4
-- Parameters: There are two parameters, p_ASP - mandatory, auxiliary storage pool (ASP) device name.

Jonathan-49

with f1 as
(SELECT replace(RTRIM(SUBSTR(lower(path_name), 
LOCATE_IN_STRING(path_name, '/', -1) + 1, LENGTH(path_name))), '.csv', '') AS File_name_no_suffix,
       path_name, t.*
    FROM TABLE (
            QSYS2.IFS_OBJECT_STATISTICS(
                START_PATH_NAME =>
                 '/folder',
                 SUBTREE_DIRECTORIES => 'NO', 
                 object_type_list => '*ALLSTMF') 

Jonathan-49

-- https://www.rpgpgm.com/2024/01/sql-repeat-scalar-function.html

-- IBM Db2 for i

  SELECT EMAIL_ADDRESS,
         SUBSTR(EMAIL_ADDRESS, 1, 1) ||
         REGEXP_REPLACE(LOWER(SUBSTR(EMAIL_ADDRESS, 2,
         (LOCATE_IN_STRING(EMAIL_ADDRESS, '@') - 2))),
        '[a-z]|[0-9]|[._]', 'x')
        || SUBSTR(EMAIL_ADDRESS, LOCATE_IN_STRING(EMAIL_ADDRESS, '@'))

Jonathan-49

-- Similar to the MySQL function SEC_TO_TIME() except this
--  function can handle hours greater than 23. 

-- IBM Db2 for i
-- UDF
-- Accepts an integer (seconds) and returns a time value formatted as 'HOURS:MM:SS',
CREATE OR REPLACE FUNCTION libary/SECS_TO_HOURSMMSS (
            Duration INT
    )
    RETURNS VARCHAR(20)

Jonathan-49

https://www.itjungle.com/2020/08/31/guru-reading-nested-xml-using-sql/


set transaction isolation level read committed;

select a.* from xmltable('INVOICES/INVOICE'
  passing (xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
  columns
    InvoiceID varchar(6) Path 'HEADER/INVOICE_ID',
    CustomerName varchar(20) Path 'RECEIVER/CUSTOMER_INFORMATION/CUSTOMER_NAME',

Jonathan-49

--  Will return 0 if the string contains only digits 
--  or the number of characters found.     
--  OS: IBM i Db2 
       Create or replace Function isNumeric (                
         V_INPUT VarChar(1000))                                  
         Returns INT                                       
         Language SQL                                            
         Deterministic                                           
         Reads SQL Data                                          
         Called on NULL Input                                    

Jonathan-49

        -- UDF that covers, hides, obscures characters, either from the left or right.
        -- Parameters: P_INPUT - input text
        --             P_Char  - Replacement character
        --             P_Num   - number of characters to cover, if negative, then
        --                       characters in the text are replaced from the right.
        --                       If postive then characters are replaced from the left.                                   
        -- Returns - input text with characters replaced from the left or right.
        -- IBM i Db2
        Create or replace Function Cover  (     
        P_INPUT VARChar(1000), P_Char Char(1), P_NUM INT)