Scott Forstie forstie
forstie
/ Are programs in QRPLOBJ being used.sql
Created
February 8, 2023 00:13
The request here was simple, are there active jobs that had objects in QRPLOBJ on the stack? The solution was a little tricky, because jobs can end in the middle of doing the analysis.
This file contains hidden or 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: Are we running with *PGMs or *SRVPGMs that reside within QRPLOBJ? | |
| -- Author: Scott Forstie | |
| -- Date : February, 2023 | |
| -- Features Used : This Gist uses qsys2.stack_info, CTEs, PIPE, SQL PL | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) Programs and Serice Programs get moved to QRPLOBJ when they are (re)created with replace(*YES). | |
| -- 2) It is not safe to delete *PGMs or *SRVPGMs from QRPLOBJ, if you base your decision upon locks. |
forstie
/ Defective PTF Currency.sql
Last active
February 11, 2026 15:11
PTFs should help, not hurt. That's the credo, goal, and expectation. But... sometimes things go the wrong way. This gist shows how to use SQL to consume an IBM provided resource, compare what you have locally and most importantly, tell you if you are exposed to a known defective PTF. Please use this gist to gain skills with SQL, but more importa…
This file contains hidden or 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: Is this IBM i at risk of a known defective PTF? | |
| -- Author: Scott Forstie | |
| -- Date : February, 2023 | |
| -- Features Used : This Gist uses qsys2.http_get, a defective PTF service from IBM, CTEs, sysibmadm.env_sys_info, string manipulation BIFs, SYSTOOLS.split | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) The data returned here is the same data you would find when using | |
| -- Go QMGTOOLS/MG option 24 (PTF Menu) --> option 3 (Compare DEFECTIVE PTFs from IBM) |
forstie
/ Searching the IFS by name or date.sql
Last active
July 27, 2025 05:22
The request was to provide an easy to use and customize approach for finding files within the IFS based upon generic names and including the date they were created.
This file contains hidden or 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
| -- With this style of SQL, you can search the IFS by file name, or by creation date, or both! | |
| -- In fact, it is simple to search by any criteria you'd like to use. | |
| -- | |
| -- Find files within the IFS where: | |
| -- --> The name starts with "P" | |
| -- --> The file suffix is ".txt" | |
| -- --> The file was created on January 10, 2022 | |
| -- |
forstie
/ AUINTERNALS made easy with SQL.sql
Created
June 30, 2022 15:26
The request... find an alternative to STRSST for monitoring AUINTERNALS security limits
This file contains hidden or 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... find an alternative to STRSST for monitoring security limits | |
| -- Author: Scott Forstie | |
| -- Date : June, 2022 | |
| -- Features Used : This Gist uses qsys2.user_storage | |
| -- | |
| -- Background: We need to see user profiles trending and approaching limits! | |
| -- Some important system limits information is only found within the bowels of the | |
| -- operating system and accessed via Start System Service Tools (STRSST). | |
| -- |
forstie
/ Find and read the SNTP activity log.sql
Created
June 5, 2022 17:53
The request... find and query the most recent SNTP activity log.
This file contains hidden or 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... find and query the most recent SNTP activity log | |
| -- Author: Scott Forstie | |
| -- Date : June, 2022 | |
| -- Features Used : This Gist uses MESSAGE_QUEUE_INFO, rtrim, hex, hextoraw, interpret, IFS_READ_UTF8, and CTEs | |
| -- | |
| -- Background: Many clients use an Simple Network Time Protocol (SNTP) client to keep the current time | |
| -- on their IBM i in relative sync with a time server. | |
| -- On the IBM i, the TCP9105 message appears in the System Operator message queue, | |
| -- pointing the admin to a log of SNTP activity. |
forstie
/ ddm server.sql
Created
June 4, 2022 15:23
The request... use SQL to determine if the DDM/DRDA server was active, and if not, start it.
This file contains hidden or 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... use SQL to determine if the DDM/DRDA server was active, and if not, start it. | |
| -- Author: Scott Forstie | |
| -- Date : June, 2022 | |
| -- Features Used : This Gist uses QSYS2.ACTIVE_JOB_INFO, BOOLEAN, QSYS2.QCMDEXC scalar function, CTE, case expression | |
| -- | |
| -- | |
| -- Is the DDM/DRDA listener active? (If at IBM i 7.4 or earlier) | |
| -- | |
| select count(*) as DDM_DRDA_Listener_Active |
forstie
/ mti_info.sql
Last active
February 26, 2025 13:18
The idea... discover MTIs and replace them with permanent indexes
This file contains hidden or 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 hidden or 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
September 25, 2024 21:28
The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
This file contains hidden or 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 hidden or 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 |