greghelton

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Vector;
import java.util.Enumeration;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.io.IOException;

RainerRoss

         ctl-opt dftactgrp(*no) option(*nodebugio:*nounref);
      //------------------------------------------------------------------//
      //                                                                  //
      // Get JSON from IFS                                                //
      //                                                                  //
      //-----------------                                                 //
      // R.Ross 07.2018 *                                                 //
      //------------------------------------------------------------------//
      // Array SQL-Result                                                 //
      //------------------------------------------------------------------//

forstie

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

forstie

-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date  : January 10, 2020
-- =================================================================

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

BirgittaHauser

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

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

BirgittaHauser

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

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