Scott Forstie forstie
forstie
/ Library list security.sql
Created
April 24, 2025 21:38
The challenge... How do we protect ourselves from an 'Uncontrolled Search Path Element' attack?
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
| -- | |
| -- Author: Scott Forstie | |
| -- Date: April, 2025 | |
| -- | |
| -- The request: | |
| -- How do we protect ourselves from an 'Uncontrolled Search Path Element' attack? | |
| -- | |
| -- Purpose: | |
| -- The IBM i has a wonderful feature known as the Library List (aka *LIBL). | |
| -- This feature allows applications, scripts, and end users to call program, interact with database files, and many other |
forstie
/ Generate SQL for Data Queues.sql
Last active
May 1, 2025 00:26
Provide a technique whereby if a data queue becomes damaged, the data queue details are available such that it can be deleted and recreated.
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
| -- | |
| -- Author: Scott Forstie | |
| -- Date: April, 2025 | |
| -- | |
| -- The request: | |
| -- When a data queue becomes damaged, that data queue cannot be evaluated or used to create a new data queue. | |
| -- Provide a technique whereby if a data queue becomes damaged, the data queue details are available such that it can be deleted and recreated. | |
| -- | |
| -- Purpose: | |
| -- This Gist shows how to establish a data mart of the existence and characteristics of all data queues. |
forstie
/ coolstuff-class_info.sql
Last active
February 11, 2025 14:41
The request... fill an IBM i (SQL) Services gap in Work Management via the creation of a CLASS_INFO service.
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: Finding IBM i class objects and return the class attributes | |
| -- Author: Scott Forstie | |
| -- Date : February, 2025 | |
| -- | |
| -- This service overcomes the lack of OUTFILE support on the Display Class (DSPCLS) CL command | |
| -- by copying the spooled file into a Db2 for i table in the SESSION schema. | |
| -- | |
| -- Features Used : This Gist uses object_statistics, output_queue_entries_basic, SQL PL, Pipe | |
| -- |
forstie
/ gist:013fba6463ce6dc4a422f6222e19a25a
Last active
April 30, 2025 18:22
Seeing programs that are configured to use adopted authority.sql
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: Seeing programs that are configured to use adopted authority | |
| -- Author: Scott Forstie | |
| -- Date : January, 2025 | |
| -- | |
| -- The request... | |
| -- I don’t find any examples for reporting on adopted authority. | |
| -- Specifically, we would like to know what pgms in non-IBM libraries use adopted authority. | |
| -- | |
| -- Features Used : This Gist uses object_statistics, object_privileges, and program_info |
forstie
/ Data driven emails from IBM i.sql
Last active
March 26, 2025 19:53
The request... send emails using data driven email recipient specifications. The implementation is 100% SQLcandoit.
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: Data driven emails from IBM i | |
| -- Author: Scott Forstie | |
| -- Date : May, 2024 | |
| -- Features Used : This Gist uses SQL PL, listagg, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL, and database know-how | |
| -- | |
| -- Now that it's simple to generate spreadsheets and send emails from the IBM i, the request was to | |
| -- send emails and NOT have the recipient(s) of the email hard-coded. | |
| -- | |
| -- One solution is found below. Store the email recipients within a Db2 for i table and |
forstie
/ Stay Current.sql
Created
April 9, 2024 14:53
The age old problem is this... IBM recommends that IBM i clients stay up to date on software updates... but how does a client get current and stay current? This confluence of technologies highlights one approach to consider.
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: Stay Current | |
| -- Author: Scott Forstie | |
| -- Date : April, 2024 | |
| -- Features Used : This Gist uses SQL PL, systools.group_ptf_currency, sysibmadm.env_sys_info, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL | |
| -- | |
| -- The age old problem is this... IBM recommends that IBM i clients stay up to date | |
| -- on software updates... but how does a client get current and stay current? | |
| -- | |
| -- This confluence of technologies highlights one approach to consider. |
forstie
/ frca_info.sql
Created
February 7, 2024 12:01
The path to achieving improved security on IBM i is sometimes a winding road. SQL can help to get you there on time. In this installment, FRCA is raised from obscurity to something understandable. #SQLcandoit,
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: HTTP Servers and FRCA | |
| -- Author: Scott Forstie | |
| -- Date : February, 2024 | |
| -- Features Used : This Gist uses SQL PL, qsys2.object_statistics, qsys2.syspartitionstat, | |
| -- SQL aliases, SQL built-in functions, UDTF, qsys2.ifs_read, and pipe | |
| -- | |
| -- HTTP Server configuration detail resides in the IFS. | |
| -- The HTTP Server instance file (QUSRSYS/QATMHINSTC *FILE) | |
| -- points to the IFS path to use for each specific server. |
forstie
/ coolstuff.read_joblog.sql
Last active
April 29, 2025 12:53
Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic....
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: Reading a joblog | |
| -- Author: Scott Forstie | |
| -- Date : January, 2024 | |
| -- Features Used : This Gist uses SQL PL, qsys2.joblog_info, sys2.history_log_info, QSYS2.SPOOLED_FILE_INFO, | |
| -- systools.SYSTOOLS.SPOOLED_FILE_DATA, systools.ended_job_info, listagg | |
| -- | |
| -- If you're like me, you've found it frustrating that a joblog is only a joblog while the job is active. | |
| -- What happens when the joblog wraps or the job completes? That's up to whomever constructed the job, | |
| -- but in many cases the answer is one or more spooled files exist with the joblog data. |
forstie
/ Reset environment variables in a job.sql
Created
January 18, 2024 19:33
This gist comes from client requests to have a mechanism to "reset" environment variables within a job.
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: Reset environment variables in a job | |
| -- Author: Scott Forstie | |
| -- Date : January, 2024 | |
| -- Features Used : This Gist uses SQL PL, qsys2.ENVIRONMENT_VARIABLE_INFO, and qsys2.qcmdexc | |
| -- | |
| -- | |
| -- When a job is started, it "inherits" all system level environment variables as job level environment variables. | |
| -- From that point on, the job can add, change, and remove job level environment variables. | |
| -- But... what should a job do if they want to reset or return to the initial state of environment variables? |
forstie
/ Collection Services made easy with SQL.sql
Last active
January 15, 2025 19:22
The Collection Services (CS) config and CS data hold a goldmine of operational insight about the IBM i. This Gist shows how a little bit of SQL can open the door to gaining insight and value from this data.
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: See Collection Services (CS) config and query CS data with SQL | |
| -- ======================================================================= | |
| -- | |
| -- Author: Scott Forstie | |
| -- Date : September, 2023 | |
| -- Email : forstie@us.ibm.com | |
| -- | |
| -- This Gist is a two-fer: |
NewerOlder