Scott Forstie forstie
forstie
/ mti_info.sql
Last active
November 17, 2023 16:16
The idea... discover MTIs and replace them with permanent indexes
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: The idea... discover MTIs and replace them with permanent indexes | |
| -- Author: Scott Forstie | |
| -- Date : June, 2022 | |
| -- Features Used : This Gist uses QSYS2.MTI_INFO, REPLACE(), QSYS2.CONDIDXA, RUNSQL CL command, and TIMESTAMPDIFF | |
| -- | |
| -- Background: | |
| -- The SQL Query Engine (SQE) recognizes when an index would be beneficial, | |
| -- and sometimes creates an index known as a Maintained Temporary Index (MTI). | |
| -- The MTI can be removed by SQE for many reasons. |
forstie
/ send_sms.sql
Last active
December 26, 2023 20:15
The idea... open up SQL to sending text (SMS) messages. Surely this idea is well within our grasp. To capture the idea fully implies that the complexity needs to be encapsulated.
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: The idea... open up SQL to sending text (SMS) messages. Surely this idea is well within our grasp. To capture the idea fully implies that the complexity needs to be encapsulated. | |
| -- Author: Scott Forstie | |
| -- Date : May, 2022 | |
| -- Features Used : This Gist uses QSYS2.HTTP_GET, QSYS2.HTTP_POST, SQL PL, and wrap | |
| -- | |
| -- Step 1: | |
| -- Decide on an SMS provider service to use. | |
| -- This example is based upon Twilio, but there are many other services (TextMagic, Vonage, and others) that provide similar support. | |
| -- |
forstie
/ Responding to an inquiry message.sql
Last active
May 29, 2022 22:38
The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
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: The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message. | |
| -- Author: Scott Forstie | |
| -- Date : May, 2022 | |
| -- Features Used : This Gist uses CTEs, SQL PL, message_queue_info, inner join, exception join, qcmdexc | |
| -- | |
| -- | |
| -- | |
| stop; |
forstie
/ alerting on high levels of jobs.sql
Created
May 29, 2022 12:58
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
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: The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows... | |
| -- Author: Scott Forstie | |
| -- Date : May, 2022 | |
| -- Features Used : This Gist uses system_value_info, CTEs, syslimits_basic, override_qaqqini, MESSAGE_QUEUE_INFO, SEND_MESSAGE, and more | |
| -- | |
| -- | |
| -- System limit tracking provides IBM i health insight: | |
| -- ================================================================ | |
| -- 1) Recognize when the IBM i is trending towards an outage or serious condition |
forstie
/ object owners with exclude authority.sql
Created
May 3, 2022 02:29
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
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.
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
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?
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
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.
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
December 10, 2021 17:29
The challenge was simple... can SQL be used to compare the contents of two spooled files? The solution follows...
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
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.
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; |