dancarlosgabriel

--
--
-- description: find database files and deploy Temporal over them
-- note: The history table will be named <existing-table-name>_HISTORY
-- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i
-- minvrm: V7R3M0
--

CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10))
   BEGIN

dancarlosgabriel

-- =================================================================
-- Author: Scott Forstie
-- Email : [email protected]
-- Date  : January 10, 2020
-- =================================================================

--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:

dancarlosgabriel

-- Read *csv File from IFS
With x as (-- Split IFS File into Rows (at CRLF)
           Select Ordinal_Position as RowKey, Element as RowInfo
             from Table(SysTools.Split(Get_Clob_From_File('/home/Hauser/Employee.csv'), x'0D25')) a
             Where Trim(Element) > ''),
     y as (-- Split IFS File Rows into Columns (and remove leading/trailing double quotes ")
           Select x.*, Ordinal_Position ColKey,
                  Trim(B '"' from Element) as ColInfo 
             from x cross join Table(SysTools.Split(RowInfo, ',')) a)
-- Return the Result as Table

dancarlosgabriel

        ctl-opt dftactgrp(*no) ;

      //-----------------------------------------------------------------------*
      // TS_BCE: Get Exchange Rate from ECB/BCE Web Service
      // This source is part of Faq400 Blog's post
      // https://blog.faq400.com/it/?p=2648
      //-----------------------------------------------------------------------*


        dcl-pr qCmdExc ExtPgm('QCMDEXC');

dancarlosgabriel

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

dancarlosgabriel

-- Search thourhg IFS files that include a specific text (sequence of characters)
-- In the following example the '/home/Hauser' directory is searched for streamfiles with the extender .json
-- All these streamfiles are searched for 'FirstName'. 
-- All streamfiles including 'FirstName' are returned.

-- Attention: Get_CLOB_From_File must run under commitment control!
Select Path_Name, Get_Clob_From_File(Path_Name) "IFS File Content"
  From Table (Qsys2.Ifs_Object_Statistics(Start_Path_Name     => '/home/Hauser', 
                                          Subtree_Directories => 'YES',
                                          Object_Type_List    => '*ALLSTMF')) x

dancarlosgabriel

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

dancarlosgabriel

It was just a question in a Forum: How to search (all) source physical file members for a specific string and list all those members
In this examples all source files beginning with "QSRC" in the "YOURSCHEMA" library are searched whether they include "String". 
All Source Members that include "String" are returned

With a as (Select a.System_Table_Schema OrigSchema, 
                  a.System_Table_Name   OrigTable, 
                  a.System_Table_Member OrigMember,
                  Trim(System_Table_Schema) concat '/' concat 
                  Trim(System_Table_Name)   concat '(' concat 
                  Trim(System_Table_Member) concat ')'         as OrigCLOBMbr

dancarlosgabriel

Here is a function whith wich you can read your source files directly with SQL:
-- 1.1. Parameters:                                                                
--      ParSrcMbr        Source Member Name                                        
--      ParSrcFile       Source File                                               
--      ParSrcLib        Source Library                                            
-- ---------------------------------------------------------------*                
Create Or Replace Function YourSchema.PH_SrcMbr                                               
       (ParSrcMbr     VarChar(10) ,                                                
        ParSrcFile    VarChar(10) ,                                                
        ParSrcLib     VarChar(10))                                                 

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'