daveh42
forstie
/ virtually done.sql
Last active
December 27, 2023 19:27
Does your physical data model include a virtual layer? If no, this gist is for you...
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: Establishing a virtual layer in the data model using SQL Views | |
| -- Author: Scott Forstie | |
| -- Date : Rocktober, 2021 | |
| -- Features Used : This Gist uses QSYS2.SYSFILES, QSYS2.SYSCOLUMNS2, dynamic SQL, and SQL PL | |
| -- Prereq: IBM i 7.3 or higher, with the Db2 PTF Group level from September 9, 2021 or later | |
| -- | |
| -- Function - The request was, I don't have a DBE.. I don't have SQL Views... how do I get started with | |
| -- shifting users and applications away from directly consuming the physical files? | |
| -- |
forstie
/ Protect the IFS root for *PUBLIC
Created
February 8, 2021 19:48
When an IFS directory includes W (write), you are exposed to malware attacks. Use this to review and overcome this topic for the all important ROOT directory.
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
| -- | |
| -- When an IFS directory includes W (write), you are exposed to malware attacks | |
| -- Use this to review and overcome this topic for the all important ROOT directory | |
| -- | |
| -- For help on this or related security topics, contact Robert and team... | |
| -- http://ibm.biz/IBMiSecurity | |
| -- Robert Andrews - robert.andrews@us.ibm.com | |
| -- | |
| stop; |
forstie
/ generate pdf.sql
Created
November 4, 2020 18:28
IBM i Access Client Solutions (ACS) includes many nifty features, like being able to save a spooled file as a PDF. This Gist shows how SQL can be used to progammatically take one or more spooled files and generate PDFs for them into the Integrated File System (IFS).
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
| -- | |
| -- title: Generate PDF(s) from Spooled Files | |
| -- This scalar function allows the SQL programmer to identify Spooled File(s) and generate pdfs for them in the IFS. | |
| -- | |
| -- minvrm: V7R2M0 | |
| -- | |
| CREATE or replace FUNCTION SYSTOOLS.Generate_PDF( | |
| job_name varchar(28) for sbcs data, | |
| file_name varchar(10) for sbcs data, | |
| file_number integer, |
forstie
/ Largest MTIs in use today.sql
Created
October 27, 2020 17:59
Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into 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
| -- | |
| -- Gist: Largest MTIs in use today | |
| -- | |
| -- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more... | |
| -- | |
| -- | |
| -- | |
| -- Find the tables with the 10 largest MTIs | |
| -- |
forstie
/ Spreadsheets and Emails with SQL & ACS.sql
Last active
October 7, 2025 12:46
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 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
| -- | |
| -- 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.* |
NielsLiisberg
/ bash-pipe-stdout.sql
Last active
August 27, 2020 19:24
SQL runs bash script and returns the stdout as a table
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
| -- Run a bash command or script and returns the stdout as a table. | |
| -- It assumes bash is installed by YUM so | |
| -- it will be in the default location /QOpenSys/pkgs/bin/bash | |
| -- You can use this aproach to other shells like sh, qsh setc. | |
| -- You need to have the "ifs_write" procedure found on my gist | |
| -- you need library QSYSINC installed: | |
| -- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm | |
| -- I doubt this method is a good idea to build huge applications, | |
| -- however it is a cool example how far you can go with SQL: | |
| ---------------------------------------------------------------------------------------------- |
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 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
| -- | |
| -- 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
September 28, 2025 19:26
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 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 | |
| -- 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
/ Temporalize a library.sql
Created
November 8, 2019 13:59
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
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: find database files and deploy Temporal over them | |
| -- note: The history table will be named <existing-table-name>_HISTORY | |
| -- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i | |
| -- minvrm: V7R3M0 | |
| -- | |
| CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10)) | |
| BEGIN |