Tim Molter timothymolter
forstie
/ Searching the IFS for objects with "log4j" in the name.sql
Last active
August 15, 2022 13:12
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
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: Find objects that have the string "log4j" in their name | |
| -- Author: Scott Forstie | |
| -- Date : December 13, 2021 | |
| -- Features Used : This Gist uses ifs_object_statistics, job_info, RUNSQL, and SBMJOB | |
| -- | |
| -- | |
| stop; | |
| -- | |
| -- |
forstie
/ Use ACS on your IBM i to build spreadsheets.sql
Created
January 17, 2020 17:02
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS.
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
| -- | |
| -- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the | |
| -- acsbundle.jar onto the IBM i. Just apply the latest ACS PTFs and reference the jar | |
| -- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar | |
| -- | |
| -- Initial PTFs of ACS | |
| -- V7R4M0 SI71900 | |
| -- V7R3M0 SI71934 | |
| -- |
forstie
/ Generating spreadsheets with SQL.sql
Last active
June 17, 2024 16:09
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: |
timothymolter
/ gist:eda0907623524e5eb99b0e8b95e758a1
Last active
November 20, 2017 20:37
— forked from zarkosusnjar/gist:1675843
Aggregate concatenation - returning grouped rows as delimited list in DB2, AS400
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
| -- using db2 newer versions | |
| create table t1 (num int, color varchar(10)); | |
| insert into t1 values (1,'red'), (1,'black'), (2,'red'), (2,'yellow'), (2,'green'); | |
| select num, | |
| substr( xmlserialize( xmlagg( xmltext( concat( ', ', color ) ) ) as varchar( 1024 ) ccsid 37 ), 3 ) | |
| from t1 | |
| group by num; |