Scott Forstie 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.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- ======================================================================== | |
| -- | |
| -- 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 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 Mar 4, 2022
Does your physical data model include a virtual layer? If no, this gist is for you...
View virtually done.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 Mar 4, 2022
This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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. | |
| -- |
NewerOlder