Scott Forstie forstie
forstie
/ Data driven emails from IBM i.sql
Last active
July 26, 2024 23:45
The request... send emails using data driven email recipient specifications. The implementation is 100% 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: 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 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 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 15, 2024 16:58
Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic....
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: 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 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
October 9, 2024 19:19
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 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 : [email protected] | |
| -- | |
| -- This Gist is a two-fer: |
forstie
/ Reactive index strategy.sql
Last active
July 29, 2024 19:32
Database and SQL performance tuning is a persistent focus, which many times leads into the indexing strategy. In this gist, I show how some of the existing tools can be tied together to achieve an automated "DBE in a box".
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: DBE in a box | |
| -- Author: Scott Forstie | |
| -- Date : August, 2023 | |
| -- Features Used : This Gist uses SQL PL, qsys2.SYSTEM_STATUS_INFO_basic, systools.lprintf, qsys2.sysixadv, qsys2.sysindexes, QSYS2.SYSINDEXSTAT, SYSTOOLS.ACT_ON_INDEX_ADVICE, QSYS2.RESET_TABLE_INDEX_STATISTICS and SYSTOOLS.REMOVE_INDEXES | |
| -- | |
| -- Note: | |
| -- 1) Indexes are not free, and care should be taken when deciding whether an index is worth creating | |
| -- 2) Indexes are not free, and care should be taken to understand whether adequate system resources are available before creating additional indexes | |
| -- 3) Indexes are not free, and care should be taken to establish a well trained Database Engineer (DBE) responsible for your IBM i partitions | |
| -- |
forstie
/ Using LICOPT and initAuto.sql
Last active
July 2, 2023 05:48
The inspiration for this Gist came from a client. A piece of code had a long-standing problem where a variable was not initialized. Given the unpredictable nature of uninitialized made the topic hard to approach. This Gist shows how the IBM i Optimizing Translator can be used to find such problems within a dev or test environment.
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 initAuto to find uninitialized variable problems in ILE code | |
| -- Author: Scott Forstie | |
| -- Date : June, 2023 | |
| -- Features Used : This Gist uses SQL PL, INCLUDE, Change Program (CHGPGM) CL command, qsys2.joblog_info, qsys2.program_info, qsys2.qcmdexc, and QSYS2.BOUND_MODULE_INFO | |
| -- | |
| -- A) Programmers use declared variables and structures to facilitate program logic. | |
| -- B) Programmers should always initialize or assign declared variables and structures to contain known and expected values. | |
| -- C) Programmers sometime miss step B. | |
| -- | |
| -- This Gist shows how the IBM i Optimizing Translator can help. |
forstie
/ PTF Cover Letters.sql
Last active
August 25, 2023 15:36
The request... show how SQL can be used to narrow the field down to those PTFs that have special instructions, and only return the special instructions. The following example focuses on what an admin might do AFTER loading PTFs, but BEFORE applying them.
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: PTF Cover Letters | |
| -- Author: Scott Forstie | |
| -- Date : April, 2023 | |
| -- Features Used : This Gist uses qsys2.ptf_info, SQL Alias, ltrim, rtrim(), dynamic SQL, SQL PL, PIPE | |
| -- | |
| -- Note: | |
| -- 1) Whether PTF Cover Letters exist on your IBM i is up to whomever ordered the PTFs | |
| -- Use the Copy PTF Cover Letter (CPYPTFCVR) command to bring in PTF cover letters | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/cpyptfcvr.html | |
| -- 2) The file QGPL/QAPZCOVER *FILE contains one member for each PTF Cover Letter |
forstie
/ db2_to_json.sql
Last active
May 2, 2024 07:42
The request... Is it possible to extract data from IBM i into JSON format with a Db2 service?
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... return SQL services detail using JSON. | |
| -- Author: Scott Forstie | |
| -- Date : April, 2023 | |
| -- Features Used : This Gist uses qsys2.syscolumns2, listagg(), rtrim(), dynamic SQL, SQL PL, PIPE | |
| -- | |
| -- Note: | |
| -- When someone asks you to return Db2 for i data "as JSON", they probably want you | |
| -- to publish a JSON document, which contains good key names and of course, the data. | |
| -- |
NewerOlder