Skip to content

Instantly share code, notes, and snippets.

Avatar

Scott Forstie forstie

  • IBM
View GitHub Profile
@forstie
forstie / object owners with exclude authority.sql
Created May 3, 2022
A client asked how they could identify which objects were set to *PUBLIC *EXCLUDE (good!), but where the object owner also had *EXCLUDE (not so good). Herein lies one solution.
View object owners with exclude authority.sql
--
-- Subject: Owners typically have *ALL authority to objects. This gist shows how to find those objects that exclude the owner.
-- As an added bonus the 2nd form of the query shows how to re-establish *ALL object authority for the object owner.
-- Author: Scott Forstie
-- Date : May 2, 2022
-- Features Used : This Gist uses OBJECT_PRIVILEGES
--
--
-- To run, change TOYSTORE to the library of your choice
@forstie
forstie / dashboarding storage capacity.sql
Created Feb 11, 2022
The request... return a simple to understand dashboard showing the basic storage detail, by database, with a percentage of storage used.
View dashboarding storage capacity.sql
--
-- Subject: Dashboard the storage capacity, including percentage used
-- Author: Scott Forstie
-- Date : February 11, 2022
-- Features Used : This Gist uses asp_info
--
--
-- Capacity numbers are in MB units.
-- https://www.ibm.com/docs/en/i/7.4?topic=services-asp-info-view
--
@forstie
forstie / Using lateral correlation to combine SQL services.sql
Created Jan 10, 2022
In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?
View Using lateral correlation to combine SQL services.sql
--
-- Subject: Using lateral correlation to combine SQL services
-- Author: Scott Forstie
-- Date : January 9, 2022
-- Features Used : This Gist uses active_job_info, joblog_info, lateral correlation, and the values statement
--
--
stop;
--
@forstie
forstie / Searching the IFS for objects with "log4j" in the name.sql
Last active Mar 22, 2022
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
View Searching the IFS for objects with "log4j" in the name.sql
--
-- Subject: Find objects that have the string "log4j" in their name
-- Author: Scott Forstie
-- Date : December 13, 2021
-- Features Used : This Gist uses ifs_object_statistics, job_info, RUNSQL, and SBMJOB
--
--
stop;
--
--
@forstie
forstie / Compare the contents of two spooled files.sql
Created Dec 10, 2021
The challenge was simple... can SQL be used to compare the contents of two spooled files? The solution follows...
View Compare the contents of two spooled files.sql
-- ========================================================================
--
-- Subject: Comparing two spooled files
-- Author: Scott Forstie
-- Date : December, 2021
--
-- IBM i Services used : OUTPUT_QUEUE_ENTRIES_BASIC, SYSTOOLS.SPOOLED_FILE_DATA
-- SQL language features used: CTEs, Exception joins, Union, and row_number()
-- ========================================================================
@forstie
forstie / SQL alternative to the command ANZDFTPWD ACTION(*NONE).sql
Last active Oct 26, 2021
The request was, is there an SQL alternative to the command ANZDFTPWD ACTION(*NONE)? The answer was a resounding YES.
View SQL alternative to the command ANZDFTPWD ACTION(*NONE).sql
--
-- 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
forstie / virtually done.sql
Last active Mar 4, 2022
Does your physical data model include a virtual layer? If no, this gist is for you...
View virtually done.sql
--
-- 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
forstie / Extracting the IFS filename from an absolute path name.sql
Created Sep 28, 2021
The request was, if you have an absolute path, how can SQL extract the filename from the path? One approach is found below.
View Extracting the IFS filename from an absolute path name.sql
--
-- 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
forstie / authority collection - split to the rescue.sql
Last active Mar 4, 2022
This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.
View authority collection - split to the rescue.sql
--
-- 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
forstie / IFS_search_replace_and_create.sql
Last active Jul 22, 2021
I was asked how ifs_read and ifs_write could be combined to build a new IFS stream file, where certain character strings are replaced.
View IFS_search_replace_and_create.sql
--
-- 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.
--