daveh42
daveh42
/ Generating spreadsheets with SQL.sql
Created
March 4, 2022 15:53
— forked from forstie/Generating spreadsheets with SQL.sql
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: |
daveh42
/ parse_statement udtf.sql
Created
March 4, 2022 15:52
— forked from forstie/parse_statement udtf.sql
The parse_statement() UDTF has gone largely unnoticed. I'm adding it to my Hidden Gems of Db2 for i presentation. This gist is another fully functional example, which shows how to establist an exit program for ZDA traffic (ODBC users). Parse_statement provides the realiable technique for getting the job done.
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 : March 1, 2020 | |
| -- Description: ODBC user activity comes in over QZDASxINIT jobs. | |
| -- You can easily utilize a ZDA exit program to | |
| -- identify specific SQL activity, when you | |
| -- code your exit program to use QSYS2.PARSE_STATEMENT(). | |
| -- | |
| -- Here's the thing: | |
| -- With the QSYS2.PARSE_STATEMENT() UDTF, you do not need to invent your own parser! |
daveh42
/ read only views.sql
Created
March 4, 2022 15:52
— forked from forstie/read only views.sql
Some views can be used to INSERT, UPDATE or DELETE data in an underlying physical file or SQL table. This example shows how the Database Engineer can construct the SQL view to be read only.
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
| -- Setup a sample database | |
| call qsys.create_sql_sample('HIS_STORE'); | |
| set schema his_store; | |
| -- Create a view over sales. The view qualifies as an updateable and deletable view. | |
| -- | |
| -- Background: | |
| -- Deletable views: A view is deletable if an INSTEAD OF trigger for the delete operation has been defined for the view, or if all of the following are true: | |
| -- ================ | |
| -- The outer fullselect identifies only one base table or deletable view that is not a catalog table or view. |
daveh42
/ Finding user profile names.sql
Created
March 4, 2022 15:51
— forked from forstie/Finding user profile names.sql
Finding user profile names... the fastest way possible....
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
| -- | |
| -- The fastest way to find every user profile | |
| -- | |
| select objname as user_profile | |
| from table ( | |
| qsys2.object_statistics('QSYS', '*USRPRF', '*ALLSIMPLE') | |
| ); | |
daveh42
/ Data validation of a string.sql
Created
March 4, 2022 15:50
— forked from forstie/Data validation of a string.sql
In a classic two-for-Tuesday move, another string based and built-in function solved question has come in.
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 : July 14, 2020 | |
| -- | |
| -- Question: How can I use SQL to validate that a string only contains an approved list of characters? | |
| -- Answer : The TRANSLATE() built-in function. | |
| -- | |
| -- TRANSLATE replaces the allowed characters with blanks. | |
| -- Then, RTRIM throws all the blanks away. | |
| -- Then, if the resulting LENGTH is zero, you can conclude that the string only contained allowed characters. |
daveh42
/ Spreadsheets and Emails with SQL & ACS.sql
Created
March 4, 2022 15:50
— forked from forstie/Spreadsheets and Emails with SQL & ACS.sql
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.* |
daveh42
/ generate pdf.sql
Created
March 4, 2022 15:47
— forked from forstie/generate pdf.sql
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, |
daveh42
/ Tracking ALLOBJ users through time
Created
March 4, 2022 15:47
— forked from forstie/Tracking ALLOBJ users through time
The idea of this Gist is to take a step beyond access to a live view of who has *ALLOBJ user special authority, to also being able to see how the topic is changing over time. With the addition of a time dimension, clients can more easily focus on the delta changes.
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: iSee how to track *ALLOBJ users | |
| -- =============================================== | |
| -- Date : November 5, 2020 | |
| -- Author: Scott Forstie | |
| -- Use : iSee video series with Tim Rowe | |
| -- | |
| -- ====================================================================================================== | |
| -- |
daveh42
/ Protect the IFS root for *PUBLIC
Created
March 4, 2022 15:46
— forked from forstie/Protect the IFS root for *PUBLIC
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; |
daveh42
/ Row level auditing.sql
Created
March 4, 2022 15:45
— forked from forstie/Row level auditing.sql
I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.
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
| -- | |
| -- I was asked how to incorporate row level auditing detail into tables. | |
| -- While Temporal tables with Generated columns is a powerful combination, | |
| -- the following example demonstrates a different approach. | |
| -- | |
| -- One fun aspect about the solution is the use of INCLUDE SQL on the triggers... | |
| -- | |
| -------------------------------------------------------------------------------------------------- | |
| -------------------------------------------------------------------------------------------------- |