Scott Forstie forstie
forstie
/ AUINTERNALS made easy with SQL.sql
Created Jun 30, 2022
The request... find an alternative to STRSST for monitoring AUINTERNALS security limits
View AUINTERNALS made easy with SQL.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: 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 Jun 5, 2022
The request... find and query the most recent SNTP activity log.
View Find and read the SNTP activity log.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: 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 Jun 4, 2022
The request... use SQL to determine if the DDM/DRDA server was active, and if not, start it.
View ddm server.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: 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 Jun 3, 2022
The idea... discover MTIs and replace them with permanent indexes
View mti_info.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: 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 May 30, 2022
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.
View send_sms.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: 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
The request... show how SQL can tackle that pesky MESSAGE_KEY binary value and respond to an inquiry message.
View Responding to an inquiry message.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: 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
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
View alerting on high levels of jobs.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: 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
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; | |
| -- |
NewerOlder