Scott Forstie forstie
forstie
/ Decimal column checker upper.sql
Created
March 24, 2023 21:26
The request was this... I want to see how close some internal identification columns are to maxing out their maximum value. For example a counter that’s defined as DECIMAL(7,0) has a high value of 9,995,000 would indicate that we need to intercede ASAP.
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: Decimal column checker upper | |
| -- Author: Scott Forstie | |
| -- Date : March, 2023 | |
| -- Features Used : This Gist uses qsys2.syscolumns2, qsys2.object_statistics, RPAD, COALESCE, and SQL PL | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) This example can help find those columns which rely upon ever increasing values | |
| -- 2) The examples could be revised to study other numeric columns, or ever descending values |
forstie
/ Undocumented IBM i Services UDTFs.sql
Last active
April 10, 2023 19:05
This request has come in many times: Provide a UDTF alternative to a an SQL View for some of the IBM i (SQL) Services. My response is that the UDTFs already exist, are sometimes not documented, and all times are OK for users to query directly.
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: Undocumented IBM i Services UDTFs | |
| -- Author: Scott Forstie | |
| -- Date : March, 2023 | |
| -- Features Used : This Gist uses undocumented UDTFs for IBM i (SQL) Services | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) IBM i (SQL) Services frequently have SQL Views, which are documented here: | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=optimization-i-services |
forstie
/ Audit Journal Management.sql
Last active
December 27, 2023 19:27
I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit
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: Audit Journal Management | |
| -- Author: Scott Forstie | |
| -- Date : March, 2023 | |
| -- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.object_statistics, qsys2.qcmdexc, CTEs, sysibmadm.env_sys_info, and SYSTOOLS.split | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- 1) There are many configuration options to consider using when establishing the Audit Journal, this Gist uses SQL to examine some of the most important choices. | |
| -- 2) Its important to have a retention strategy for audit journal - journal receivers |
forstie
/ Bringing ORDER to a VIEW.sql
Last active
April 12, 2023 21:16
The request... change Db2 for i to allow ORDER BY on CREATE VIEW. Well, we aren't going to do that because its non-standard. This gist shows a path forward using the existing support.
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: Bringing ORDER to a VIEW | |
| -- Author: Scott Forstie | |
| -- Date : February, 2023 | |
| -- Features Used : This Gist uses UDTFs, SQL DDL, PIPE, SQL PL, SQL global variables | |
| -- | |
| -- Notes: | |
| -- =============================================== | |
| -- The SQL Standard dictates that the ORDER BY clause cannot be | |
| -- includes in the CREATE VIEW defintion. |
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 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
January 10, 2024 13:58
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 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
June 7, 2023 09:52
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 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 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 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 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 |