forstie’s gists

forstie / dashboarding storage capacity.sql

Created February 11, 2022 15:09

The request... return a simple to understand dashboard showing the basic storage detail, by database, with a percentage of storage used.

	--
	-- 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 / Using lateral correlation to combine SQL services.sql

Created January 10, 2022 02:24

In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?

	--
	-- 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 / Searching the IFS for objects with "log4j" in the name.sql

Last active August 15, 2022 13:12

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.

	--
	-- 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 / Compare the contents of two spooled files.sql

Created December 10, 2021 17:29

The challenge was simple... can SQL be used to compare the contents of two spooled files? The solution follows...

	-- ========================================================================
	--
	-- 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 / SQL alternative to the command ANZDFTPWD ACTION(*NONE).sql

Last active October 26, 2021 19:43

The request was, is there an SQL alternative to the command ANZDFTPWD ACTION(*NONE)? The answer was a resounding YES.

	--
	-- 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 / virtually done.sql

Last active December 27, 2023 19:27

Does your physical data model include a virtual layer? If no, this gist is for you...

	--
	-- 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 / Extracting the IFS filename from an absolute path name.sql

Created September 28, 2021 01:55

The request was, if you have an absolute path, how can SQL extract the filename from the path? One approach is found below.

	--
	-- 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 / authority collection - split to the rescue.sql

Last active December 2, 2024 09:43

This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.

	--
	-- 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 / IFS_search_replace_and_create.sql

Last active July 22, 2021 16:15

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.

	--
	-- 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 / change command default.sql

Created June 4, 2021 16:22

Before an upgrade, rollswap, or just for good hygiene, its good to know which CL commands have had their command defaults changed. Here's an approach that works all the way back to IBM i 7.2.

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

Scott Forstie forstie