Scott Forstie forstie
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
/ 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
/ Generating spreadsheets with SQL.sql
Last active
April 3, 2024 18:15
In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer. …
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
| -- ================================================================= | |
| -- Author: Scott Forstie | |
| -- Email : forstie@us.ibm.com | |
| -- Date : January 10, 2020 | |
| -- ================================================================= | |
| -- | |
| -- Setup: | |
| -- 1) create and populate the spreadsheet generator table | |
| -- 2) change the procedure source code: |
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
/ Spreadsheets and Emails with SQL & ACS.sql
Last active
March 8, 2024 13:29
In this Gist, I show how you can leverage ACS's jar that is shipped on your IBM i to do your bidding. In this case, we can automate the creation and downloading of a spreadsheet to an IFS file where the contents of the spreadsheet is controlled with an SQL query. After the spreadsheet is created in the IFS, we use SQL once more to email the spre…
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
| -- | |
| -- Note: | |
| -- Documentation for ACS's cldownload and other fantastic hidden gems can be found here: | |
| -- ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html | |
| -- | |
| -- | |
| -- Do I have the ACS jar on this IBM i? | |
| -- | |
| select create_timestamp, acs.* |
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
/ Retrieve details for active 5250 sessions.sql
Last active
January 25, 2024 22:34
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
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
| -- | |
| -- description: Find active Telnet or Interactive 5250 sessions | |
| -- | |
| -- resource: ibm.biz/WellDefinedIBMiPorts | |
| -- | |
| select remote_address, remote_port, authorization_name as user_name, job_name | |
| from qsys2.netstat_job_info n | |
| where local_port in (23,992) and job_type = 'INTERACTIVE'; | |
| -- |
forstie
/ Who am i.sql
Last active
January 25, 2024 22:17
I was asked to provide a query that pulls together some of the basic identity detail for the IBM i you're connect to... which got me to this....
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
| -- | |
| -- Who am i? | |
| -- | |
| select s.host_name, e.host_name, 'IBM i ' concat os_version concat '.' concat os_release as ibmi_level, | |
| serial_number, machine_type, machine_model, | |
| ipl_mode, ipl_type, attention_light, | |
| total_cpus, e.configured_cpus, | |
| configured_memory, total_memory, | |
| s.* | |
| from sysibmadm.env_sys_info e, qsys2.system_status_info s; |
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
/ QBATCH job study.sql
Created
May 30, 2021 17:10
I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.
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: QBATCH Job Analysis | |
| -- Author: Scott Forstie | |
| -- Date : May 30, 2021 | |
| -- Note : This Gist leverages different built-in functions and grouping support to explore job history | |
| -- | |
| -- ================================================================================================ | |
| -- | |
| -- description: 20 Jobs that ran (started and ended) the longest in QBATCH over the last 24 hours |
NewerOlder