forstie

--
-- Subject: SQL alternatives to Analyze Default Passwords (ANZDFTPWD) ACTION(*NONE)
-- Author: Scott Forstie  
-- Date  : October 26, 2021
-- Features Used : This Gist uses user_info_basic, grouping, and SYSTOOLS.CHANGE_USER_PROFILE
--
-- Function - The request was, is there an SQL alternative to the command ANZDFTPWD ACTION(*NONE) ?
--
--
stop;

forstie

--
-- Subject: Establishing a virtual layer in the data model using SQL Views
-- Author: Scott Forstie  
-- Date  : Rocktober, 2021
-- Features Used : This Gist uses QSYS2.SYSFILES, QSYS2.SYSCOLUMNS2, dynamic SQL, and SQL PL
-- Prereq:  IBM i 7.3 or higher, with the Db2 PTF Group level from September 9, 2021 or later
--
-- Function - The request was, I don't have a DBE.. I don't have SQL Views... how do I get started with
--            shifting users and applications away from directly consuming the physical files?
--

forstie

--
-- Subject: Extracting the IFS file name from a path, using regular expression built-in functions
-- Author: Scott Forstie  
-- Date  : September 27, 2021
-- Features Used : This Gist uses regexp_count, regexp_instr, substr, and SQL PL
--
-- Function - The request was, if you have an absolute path, how can SQL extract the filename from the path?
--            One approach is found below.
--
--

forstie

--
-- Subject: authority collection - split to the rescue
-- Author: Scott Forstie  
-- Date  : September 20, 2021
-- Features Used : This Gist uses split(), right exception join, set session authorization, and authority_collection
--
-- Function - This gist goes beyond visual recognition of the authorization gap, using SQL to compute the different AND 
--            generate the Grant Object Authority (GRTOBJAUT) CL commands needed to bridge the gap.
--
-- In this example, JOEUSER wants to QUERY and UPDATE the TOYSTORE/SALES file.

forstie

--
-- Subject: IFS stream file transformation
-- Author: Scott Forstie (thanks to Sue Romano for helping with this Gist)
-- Date  : June 28, 2021
-- Features Used : This Gist uses ifs_read, ifs_write, regexp_replace, and the previously unknown fx designation for a unicode literal.
--
-- Function - Point is routine at an existing IFS steam file, which contains some character data that you want to globally replace.
--            The function extracts (reads) the contents, replaces the search string occurrences with the replacement string, and then 
--            writes everything to the target IFS stream file.
--

forstie

--
-- Which CL commands have had their parameter defaults changed?
-- (On IBM i 7.3 and higher)
--
with libs (lib) as (
    select objname
      from table (
          qsys2.OBJECT_STATISTICS('*ALLAVL', '*LIB')
        )
  )

forstie

--
-- Subject: QBATCH Job Analysis
-- Author: Scott Forstie
-- Date  : May 30, 2021
-- Note  : This Gist leverages different built-in functions and grouping support to explore job history
--

-- ================================================================================================
--
-- description: 20 Jobs that ran (started and ended) the longest in QBATCH over the last 24 hours

forstie

create table toystore.employee_deetz (
      Company_Id bigint, 
      Employee_Id bigint, 
      FirstName varchar(100) for sbcs data,
      SurName varchar(100) for sbcs data, 
      Employed_Start_Date date, 
      Employed_End_Date date);
      
insert into toystore.employee_deetz values
  (101, 10001, 'Scott', 'Forstie', '07/11/1989', NULL);

forstie

--
-- If you're not ready to use IFS_OBJECT_PRIVILEGES, this might help you...
-- Note: A special thanks to Sue Romano for her help with this Gist
--

-- https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-privileges-table-function
cl:PRTPVTAUT OBJTYPE(*STMF) DIR('/') SCHSUBDIR(*YES);

--
-- create an alias over the most recent execution of PRTPRVAUT

forstie

-- 
-- Collection Services data file:  QAPMJOBL (JOB PERFORMANCE DATA)
-- 
-- Resource:
-- https://www.ibm.com/docs/en/i/7.4?topic=data-collection-services-files-qapmjobs-qapmjobl
--

--
-- Raw
--