Steve Riedmueller sriedmue79
sriedmue79
/ CTE Example.sql
Last active
September 16, 2024 14:09
IBM i - Understanding CTEs and how to combine IBM i Services using LATERAL
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
| -- | |
| -- Description: The "Common Table Expression" (CTE) is a very powerful and useful tool which allows a temporary "table" to be | |
| -- created in memory while a query is running. The contents of that temporary table (or "result set") can be | |
| -- referenced elsewhere in the query. | |
| -- | |
| -- The table functions provided by IBM as "IBM i Services" are ALSO very powerful. These functions allow us to | |
| -- use SQL to retrieve many different details from the system. This includes details about user profiles, | |
| -- network connections, objects, streamfiles, security, and much more. | |
| -- | |
| -- We can leverage CTEs to combine multiple IBM i Services table functions (and views) and make them even more |
sriedmue79
/ MONMSGID.sql
Last active
June 6, 2025 15:43
Monitor for a particular message id being sent in the past 24 hours
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
| -- | |
| -- Description: Some software sends a warning message when its license key is nearing expiration, but the message | |
| -- is only sent to the user who is accessing the software at the time that they access it. | |
| -- If that user doesn't notify the system admin, the license key may expire "without warning"! | |
| -- Can I write a single SQL statement to check if a specific message id was sent to any user in | |
| -- the past 24 hours, and send me an email alert if so? | |
| -- | |
| -- | |
| -- Find all occurrences of message ID "XYZ1234" having been sent to any user: |
sriedmue79
/ EndIdleConnections.sql
Last active
September 5, 2025 12:54
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
| -- | |
| -- Description: Users are leaving their "Run SQL Scripts" sessions open for an extended period of time. | |
| -- Although there is a configurable option to "automatically close cursor to release locks", | |
| -- this does not disconnect the session. This can present a security risk if physical access | |
| -- to the connected workstation is compromised. | |
| -- | |
| -- How can we use SQL to identify ODBC/JDBC connections that have been idling for a long time | |
| -- and proactively disconnect them? Follow along below. | |
| -- | |
| -- DISCLAIMER: These examples are provided for informational and educational purposes only. There is no |
OlderNewer