daveh42
daveh42
/ authority collection - split to the rescue.sql
Created
March 4, 2022 15:43
— forked from forstie/authority collection - split to the rescue.sql
This Gist shows how SQL can be used to simplify the task of analyzing Authority Collection runtime authority data.
daveh42
/ virtually done.sql
Created
March 4, 2022 15:44
— forked from forstie/virtually done.sql
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? | |
| -- |
daveh42
/ prtprvaut.sql
Created
March 4, 2022 15:44
— forked from forstie/prtprvaut.sql
Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit
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
| -- | |
| -- If you're not ready to use IFS_OBJECT_PRIVILEGES, this might help you... | |
| -- Note: A special thanks to Sue Romano for her help with this Gist | |
| -- | |
| -- https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-privileges-table-function | |
| cl:PRTPVTAUT OBJTYPE(*STMF) DIR('/') SCHSUBDIR(*YES); | |
| -- | |
| -- create an alias over the most recent execution of PRTPRVAUT |
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... | |
| -- | |
| -------------------------------------------------------------------------------------------------- | |
| -------------------------------------------------------------------------------------------------- |
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
/ 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
/ 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
/ 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
/ 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
/ 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') | |
| ); | |
OlderNewer